Soft Delete Conventions

Decided on 2026-05-19. Supersedes the older is_deleted + deleted_at pattern in .ai/instructions.md.

The convention in one sentence

For any table that needs a soft-delete primitive, use a status column with the value :archived to mark a row as soft-deleted. Do not use is_deleted or deleted_at.

Two rules that frame this convention

1. No hard delete. Rows are never removed from the database. PII redaction follows a different process (see the PDPA notes) and is not the same as deletion. This rule is project-wide and absolute.
2. Restore depends on what :archived means for the table.
   • Pure soft-delete tables — where :archived is only a "deleted" marker — do not restore. If the business action is "bring back this thing", create a new row; do not flip the flag back.
   • Lifecycle / master-data tables that define an explicit reactivation use-case (e.g. Org::JobRole, Gig::Job) may flip archived → active. Here :archived is a reversible operational state, not a deletion. These tables are FK-anchored — other rows point at their id — so "create a new row" would orphan those references; flip-back is the integrity-preserving operation. A reactivation is logged as an :updated history row (a status transition); it does not need a :restored action.

Rule 1 is project-wide and absolute. For rule 2, decide which kind of table you have before you build a restore path. If a pure soft-delete table later needs restore, raise it as a design discussion first.

How to apply the convention

Tables that have no other lifecycle (e.g. Org::JobRole, Gig::PayRate)

Add a status column with two values: :active and :archived.

# migration
t.string :status,
  null:    false,
  default: 'active',
  comment: 'rails_enum(:active, :archived)'

# model
enum :status, {
  active:   'active',
  archived: 'archived'
}, prefix: :status, default: 'active'

The active scope is filtered by status, not by a boolean flag:

has_many(
  :active_pay_rates,
  -> { where(status: :active) },
  class_name:  'Gig::PayRate',
  foreign_key: 'org_company_id'
)

Partial unique indexes use the status predicate:

add_index(
  :gig_pay_rates,
  [ :org_company_id, :org_outlet_id, :org_job_role_id, :day_type, :starts_at, :ends_at ],
  unique: true,
  where:  "status = 'active'",
  name:   'index_gig_pay_rates_on_rate_condition_uniqueness'
)

Tables with a richer lifecycle (e.g. Gig::Shift, Gig::Application, Gig::Assignment, Gig::Payment)

Do not add :archived to the existing enum. These tables already have terminal states that serve the same role:

Table	Existing terminal state(s)	Why no :archived
Gig::Shift	:cancelled, :expired, :completed	These already mean "not active". Adding :archived would create ambiguity.
Gig::Application	:rejected, :withdrawn, :declined, :cancelled, :expired	Same.
Gig::Assignment	:cancelled, :no_show, :verified	Same.
Gig::Payment	:paid	Once paid, the row is operationally closed.

If you need to filter "active" rows on these tables, query against the lifecycle directly:

Gig::Shift.where.not(status: [ :cancelled, :expired, :completed ])

Tables that never need soft-delete

Do not add a status column. These are:

• Audit / history / append-only logs. Examples: gig_pay_rate_histories, gig_shift_histories, gig_assignment_adjustments, gig_shift_qr_codes. By design, rows are written once and never modified.
• Settings rows whose lifecycle is dictated by the parent. Examples: gig_company_settings, gig_outlet_settings. The parent (Org::Company, Org::Outlet) is what gets archived; the settings row is irrelevant once the parent is gone.
• Pure reference / lookup tables with is_active semantics (e.g. geo_public_holidays.is_active). These pre-date the convention; is_active is a feature flag, not a soft-delete marker. Do not add status to them.

How a soft-delete actually happens

The Destroy Manager flips the status column and writes a history row. There is no callback; the Manager owns both writes inside one transaction.

module Gig
  module PayRates
    class DestroyManager

      def self.execute(request:)
        pay_rate = ::Gig::PayRate.find(request.id)

        ::ActiveRecord::Base.transaction do
          pay_rate.update!(
            status:          ::Gig::PayRate.statuses[:archived],
            updated_by_type: request.actor.class.name,
            updated_by_id:   request.actor.id
          )

          ::Gig::PayRateHistory.create!(
            gig_pay_rate_id: pay_rate.id,
            action:          ::Gig::PayRateHistory.actions[:archived],
            diff:            { status: { was: 'active', now: 'archived' } },
            changed_by_type: request.actor.class.name,
            changed_by_id:   request.actor.id
          )
        end

        pay_rate
      end

    end
  end
end

The history table's action enum is :created, :updated, :archived. There is no :deleted and no :restored — those values were tied to the old convention.

"When was this row archived?"

Two sources, depending on what the consumer needs:

Need	Where to look
Fast read on the hot path (resolver, listings, etc.)	status column on the parent row. The row is either :active or :archived.
"Who archived it and when?"	History table for that model. The action = :archived row carries changed_by_* and created_at.
Per-table report that needs the timestamp without a join	Add an archived_at timestamptz column on that table only as an exception. Do not add it everywhere by default.

updated_at on the parent row is not a reliable proxy for "archived at". The row may receive other updates after archiving.

Why this convention (and not is_deleted + deleted_at)

Concern	is_deleted + deleted_at	status :active | :archived
Two-column drift risk	Possible — set one and forget the other	Impossible — one column
Honest naming	"deleted" is a lie when nothing is hard-deleted	"archived" matches what we actually do
Restore flow	Flip flag and clear timestamp (two writes, easy to do incorrectly)	One write to status; allowed only for lifecycle tables with an explicit reactivation use-case (see rule 2)
Read path	WHERE is_deleted = false	WHERE status = 'active'
Consistency with lifecycle-rich tables	Different primitive on top of status	Same primitive — status everywhere
Column count on a 2-state table	2 columns	1 column

The strongest argument: archiving is a state transition, not a deletion marker. For pure soft-delete tables the column flips once and stays; for lifecycle tables it may flip back via an explicit reactivation. Either way it is a status change — exactly what a status column models. status is the right word.

What this convention does NOT change

• PII redaction. Hashing or scrubbing PII on a user-deletion request is a separate process and is allowed to write NULLs into specific columns. That is not a soft-delete.
• Hard deletes for test data and fixtures. Test environments may truncate freely. The "no hard delete" rule applies to production application logic.
• Tables that have been built on the old pattern. Existing tables with is_deleted + deleted_at keep working. They are off-convention and should be migrated forward when the team has bandwidth, but this is not a blocker for new work.

Migrating an existing table off is_deleted + deleted_at

If a table currently uses the old pattern and you want to convert it:

1. Add the new status column with default: 'active'.
2. Backfill: rows with is_deleted = true become status = 'archived'. The backfill runs in a separate migration.
3. Update all readers to use status instead of is_deleted.
4. Drop is_deleted and deleted_at in a final migration.

Do not run all four steps in one PR. The intermediate state (both columns present, both being written) is what protects you from a bad deploy.

Naming caveats

• The Ruby method is pay_rate.status_archived? (because the enum uses prefix: :status). Not pay_rate.archived? — that would collide with other models that use the same prefix convention.
• The model exposes has_many :active_things (filtered) and has_many :things (all). Most callers use the filtered association.
• The column is always called status, never state. Match the existing repo convention.

Open questions

• Bulk archive of many rows under one action (e.g. archiving all PayRates for an outlet when the outlet closes). The current pattern writes one history row per archived row. For bulk operations, the history may inflate. Address per case when it lands — not in advance.
• Retention / pruning of archived rows. No policy today. When a retention policy is needed, the design discussion goes here.

Related

• .ai/instructions.md — this convention overrides the older "Soft Delete Pattern" section there
• Org::JobRole (docs/30-49-domains/33-org/models/org-job-role.md) and Gig::Job (docs/30-49-domains/35-gig/models/gig-job.md) — both use the status: :archived primitive, and both are the reversible-lifecycle case under rule 2 (each defines a reactivation use-case)
• Gig::PayRate — the illustrated spec (docs/30-49-domains/35-gig/models/gig-pay-rate-illustrated.mdx) uses this convention and matches the gig_pay_rates.status enum in production. The older gig-pay-rate.md still carries pre-convention wording ("no status lifecycle", "active/inactive") and should be reconciled separately.