Appearance
User lifecycle: deactivation, attribution and erasure
Platform-wide, deliberately. 📘 Owner constraint 2026-08-23: "This needs to work consistently across all QR Setu customer types, not just dealerships. We should avoid introducing a dealership-specific mechanism." So this page sits in
architecture/, not inverticals/car_sales/. The dealership page data continuity is now the vertical application of the rules decided here.
⚠ Read the three measurements before the recommendation, because two of them invert the question
📘 The owner's initial thought was "soft deletion / deactivation would be safer", with the explicit instruction not to implement it blindly.
🧮 Measured against all 69 live migrations and against packages/data, by script:
- ✅ The identity-level soft delete ALREADY EXISTS.
public.users.statusischeck (status in ('active', 'suspended', 'deleted')). The decision the owner is proposing was taken in20260808100000_v2_identity_and_tenancy.sql. - ⚠⚠ AND IT IS ENFORCED BY NOTHING. Two functions read
users.status:get_my_contextprojects it, andsoft_delete_accountchecks it for idempotency (20260904150210_v2_soft_delete_cascade.sql:70). Zero policies, zero helpers and zero client code enforce it. A soft delete is made to stick by an Auth ban instead (QRS-909), but a user row set to'suspended'still signs in, still passes every RLS check and still sees everything. This is QRS-013's green-no-op applied to identity: the state exists, the enforcement was never written. - ✅ Membership revocation, by contrast, genuinely works — and it is single-sourced.
my_workspace_ids()filtersm.status = 'active', the other two helpers derive from it, so one column governs the whole revocation surface. The migration's own comment says: "so removed and suspended memberships never widen it. Drop this and the platform…"
🔎 So the answer to "should we soft delete?" is: we already decided to, at the right layer, and then enforced it at only one of the two layers. The gap is not a design gap. It is an unwired one.
1 · The verdict, up front
| Question the owner asked | Answer |
|---|---|
| Should we avoid hard deletes? | ✅ Yes, and the schema already says so — but ⚠ see the cascade defect in §5.2 |
| Is soft deletion the right approach? | 🔎 At the identity and membership layers, yes. As a general deleted_at on every table, NO — see §2.3 |
| Do we need to separate person from role/seat? | ✅ Yes, into FOUR layers, not two — and ⚠ not into a "seat", see §3 |
| Does the architecture already handle it? | 🧮 Partly. Two layers are right, one is unwired, and one does not exist |
| Is it consistent across all four customer types? | ⚠ Almost — one cascade breaks it, see §9 |
| Can we lock the dealership implementation? | 🔴 Not yet. Three decisions in §10 come first |
2 · What the industry actually does
🌐 Researched rather than assumed, because the owner asked for the industry-recommended approach rather than my preference.
2.1 · The standard is active: false, and it is a standard
🌐 SCIM 2.0 is the interoperability standard for exactly this problem, and its answer is unambiguous: deprovisioning almost never means DELETE. Okta and Microsoft Entra ID both deactivate a user by sending a PATCH that sets active to false; the record is retained. Hard delete is reserved for three specific cases — an erasure request, a whole tenant offboarding, and a scheduled purge after a retention window closes — and the recommended shape is deactivate immediately, then hard-delete on a schedule if a policy demands it.
🔎 This is worth more than a citation: it means the owner's instinct is the interoperability standard, not just a reasonable choice. If QR Setu ever supports enterprise SCIM provisioning — and an enterprise dealership group with an HR system is exactly the customer who asks — then users.status and workspace_members.status are the active attribute. Building anything else would have to be undone.
2.2 · ⚠ The trap the industry names, and it is the one we are closest to hitting
🌐 "Setting active to false only blocks new logins. It does nothing to a session that already exists, so deprovisioning is incomplete until you actively revoke live sessions and issued tokens."
🧮 QR Setu is in an unusually good position here, and it is worth knowing why. Authorization is not carried in the JWT — there are no roles or workspace ids in the token. Every protected read re-derives authority from the database through my_workspace_ids(), which re-runs per query. So flipping a membership to removed takes effect on the very next query, not at the next token refresh. Worst case is one already-rendered screen showing stale content until it refetches.
⚠ But the session itself is a different object, and killing it is a separate action nobody has written. A revoked member still holds a valid auth.uid(), so they can still call an Edge Function, and anything that trusts auth.uid() without asking workspace_members remains reachable. Revocation is therefore two writes, not one: the membership status, and an admin sign-out plus a ban on the auth user. 🔎 The second one has no code today.
2.3 · ⚠ And the part of the owner's instinct to NOT generalise: a universal deleted_at is an anti-pattern
Soft-deleting the identity is the standard. Soft-deleting every row is a different proposal wearing the same words, and it is the one to refuse:
Why a universal deleted_at goes wrong | Consequence here specifically |
|---|---|
| Every query must remember the filter | ⚠ Every RLS policy would need it too, and a forgotten predicate is a silent data leak rather than a visible bug |
| Unique constraints stop meaning what they say | 🧮 setu_cards.slug is citext not null unique — a soft-deleted card still occupies the slug, so the next holder cannot have it |
| It creates a second definition of "exists" | This repo's most expensive recurring defect class (QRS-249) is two sources of truth for one fact |
| Deleted rows reappear | A join that forgets the filter resurrects them into a report |
🧮 And the schema already carries THREE different soft-delete idioms, which is the argument in miniature:status text on identity and content, is_active boolean on reference data (cities, states, reserved_slugs, payout_accounts), and archived_at timestamptz on chat (conversation_states, quick_replies). 🔎 Adding a fourth would be worse than any of the three.
The rule that comes out of this: soft-delete the LIFECYCLE OBJECTS — identity, membership, assignment, card. Never soft-delete the BUSINESS RECORDS. A lead does not get deleted when its owner leaves; it gets reassigned. That is not a deletion problem at all, which is why generalising soft delete to solve it produces a worse schema than solving it directly.
3 · The separation the owner asked for: four layers, and none of them is a seat
📘 The question was "how should we separate the identity of a person from their role/seat and business records?" 🔎 The honest answer is that two concepts is not enough and a seat is the wrong third one.
| Layer | State | What it answers |
|---|---|---|
| 1 · Identity | 🟡 exists, unenforced | Who is this person, ever? One row, never deleted, never duplicated |
| 2 · Membership | 🟡 exists, no tenure end | Do they work here right now? This is the access decision, and the only one |
| 3 · Assignment | 🔴 does not exist | What authority did they hold, over what, and between when and when? |
| 4 · Record | 🔴 columns not written | Who did this? (immutable) vs whose job is it now? (mutable) |
3.1 · ⚠ Why not a "seat", restated because the owner raised it twice
Two reasons, and the thing the seat was for is delivered anyway.
(1) The word is taken, and taking it twice is this repo's most expensive habit.QRS-397 fixed seat as a licence unit: 8 showrooms + 30 agents = 39 cards but 30 seats. A second meaning would join cards, plans, primitives (three simultaneous meanings) and templates (254 code and 358 portal occurrences before anyone said the word aloud). Every one of those cost a cross-cutting sweep, and every one was found by a human reading a filename, never by a gate.
(2) A seat entity loses the person the question is about. 📘 The owner's own framing is exactly right and already names the answer: historical attribution → the original employee; operational responsibility → the current employee. Those are two columns on the record, not a third entity between them. A lead owned by Seat 3 puts the person a join away at best and unrecoverable at worst.
✅ The one thing a position abstraction genuinely earns is TEAM CONTINUITY — a team must report to the position, not to a deactivated person, and last quarter's report must use the role held then. 🔎 That is layer 3, and layer 3 is RBAC's role_assignments scoped to a subtree, which QRS-863 already specifies. So the position model is a date range on an assignment row, not a new table. That is a further argument for taking the RBAC model decision now (QRS-868) even while the RBAC screen stays deferred.
4 · What is measured true today
| Capability | State | Evidence |
|---|---|---|
| Identity is never duplicated per tenant | ✅ | users.id = auth.users.id; handle_new_user provisions one row per principal |
| Identity has a lifecycle vocabulary | 🟡 | status in ('active','suspended','deleted') — ⚠ enforced by nothing (read only by get_my_context and soft_delete_account) |
| Membership has a lifecycle vocabulary | ✅ | status in ('invited','active','suspended','removed') |
| Revoking membership revokes data access | ✅ | my_workspace_ids() filters status = 'active'; the other two helpers derive from it |
| Oversight inherits the revocation | ✅ | my_oversight_workspace_ids() calls my_workspace_ids(), so it is correct by construction |
| Authorization is never cached in a token | ✅ | No role or workspace claim in the JWT. 🔎 This is why revocation is near-immediate |
primary_context is not an authorization input | ✅ | The migration says so explicitly: "PREFERENCE ONLY … Authorization asks workspace_members" |
| A tenant-scoped audit trail exists | ✅ | public.audit_log, actor_user_id SET NULL + actor_role denormalised |
| Tenure has an end date | 🔴 | joined_at exists; left_at = 0 occurrences |
| Time-bounded authority | 🔴 | valid_from / valid_until = 0 occurrences anywhere in 69 migrations |
| A client can revoke a membership | 🔴 | One policy on workspace_members, and it is SELECT. No write path exists yet |
| Creator vs assignee on business records | 🔴 | assigned_to = 0 occurrences; no assignment-history table |
| An employee Setu Card | 🔴 | setu_cards.workspace_id is not null UNIQUE, no user column (QRS-869) |
5 · ⚠ The three live defects
5.1 · users.status is a tombstone nothing checks
🧮 One occurrence in the repository: the CHECK constraint. A 'deleted' user signs in normally. The fix is small and belongs with the auth work, not with the dealership: a predicate in the auth-context RPC, and a pgTAP case asserting a 'deleted' user reads nothing.
⚠ Note what makes this dangerous rather than merely incomplete: it reads as done. Anyone auditing the schema for "do we soft-delete users" finds the column, finds the right three states, and stops.
5.2 · ⚠⚠ The soft delete is bypassed by the easiest action an operator can take
🧮 public.users.id uuid primary key references auth.users (id) on delete cascade.
So deleting the auth user hard-deletes the profile row and cascades onward through 11 further FKs. And deleting an auth user is one click in the Supabase dashboard, labelled Delete user. 🔎 The soft-delete state exists precisely so that click is never needed, and the click is easier to perform than the soft delete — which has no UI at all.
Recommended: treat auth.users deletion as a privileged, audited, last-resort erasure step with a written runbook, never an operator convenience; and reverse the operational FKs to SET NULL (rule L9 below) so that even if it happens, business records survive with an anonymised actor rather than vanishing.
5.3 · ⚠ Promotion overwrites its own history; transfer loses its tenure
📘 Added by the owner mid-review: "employees will get promoted / transferred from one outlet to another." 🧮 Measured, and the two cases behave differently, which is the finding:
| Case | Representable? | What breaks |
|---|---|---|
| Transfer, outlet A → outlet B | ✅ yes — the PK is (workspace_id, user_id), so both rows coexist and A's can go removed | ⚠ No left_at, so A's tenure has no end; and nothing marks it as a transfer rather than a second concurrent posting |
| Promotion within one outlet | 🔴 no | The PK gives one row per person per workspace, so a promotion is an UPDATE of role_key in place. The previous role is destroyed. "Who was the manager last quarter" is unanswerable |
⚠ And transfer carries a reporting defect that is the sibling of L10, one level up. Outlet A's manager must still see the leads that person created while at A. If a rollup derives the outlet from the person's current membership, then the day someone transfers, their entire history moves to the new outlet — outlet A's last quarter silently shrinks and outlet B's silently grows. 🔎 Every business record must carry its own workspace_id and every rollup must fold over the record's workspace, never the actor's current one.
6 · Setu Card lifecycle
🧮 setu_cards.slug is citext not null unique and write-once by trigger (12 references). So a card URL is immortal and unreassignable, and it may be printed on a standee and sitting in a hundred WhatsApp threads.
⚠ Yesterday I recommended redirecting a departed rep's card to the outlet card. That was the right answer to the wrong question, and the platform framing corrects it. The card is workspace-owned and member-held — setu_cards has no user column at all, so a person never owned it. That resolves the question by splitting it:
| The QR is… | Encodes | On departure |
|---|---|---|
| Printed / physical (standee, banner, card wall) | a position or touchpoint, never a person | ✅ Reassign to the next holder. The URL is a business asset and nothing is reprinted |
| A person's shareable link (WhatsApp signature, personal QR) | a person | ⚠ Retire with a redirect, because reassigning it would show a customer a different human under a name they remember |
🔴 This is a decision, not an observation, and it must be taken before the first standee is printed. A standee carrying a person-shaped slug makes the wrong choice permanent and physical. It is also the commercial argument for per-standee touchpoints (matrix item 16): the ₹225 standee should point at a position, and only a touchpoint table makes that possible.
7 · Privacy: the collision the owner asked us to avoid creating
📘 "…preserves business data without retaining unnecessary active access or creating privacy / security problems." 🌐 This is where the research changed my recommendation, so it is worth stating what I was going to say and why it was wrong.
What I was going to recommend: keep a pseudonymous identity key forever so joins and attribution hold, and vault the PII separately so it can be erased. Attribution survives as "Employee #4f2a, Sales Consultant, Jun 2025 to Mar 2026".
⚠ Why that is not sufficient as stated. 🌐 Under the DPDP framing, pseudonymised data is still personal data: if re-identification is technically feasible, the erasure right still applies. A user_id that can still be joined back to a name is re-identifiable, so the vault is not a compliance answer on its own.
✅ The precision that makes it work: destroy the MAPPING, not merely the PII row. Once the vault entry is irrecoverably destroyed, created_by_user_id is a bare opaque key with no path back to a person from our side — that is anonymisation rather than pseudonymisation, and it is what distinguishes crypto-shredding from tokenisation. 🔎 The distinction is the whole compliance argument, and it is easy to lose.
7.1 · And the harder finding: employee retention has a ceiling
🌐 An employer may not retain an employee's personal data beyond one year after an erasure request or after the purpose ends, unless another statute mandates retention. That collides head-on with "historical business data must remain available."
✅ The collision resolves cleanly once you separate the record from the attribution, which is the same split as §3:
| Basis | Survives erasure? | |
|---|---|---|
| The business record — a lead, a test drive, a sale, an invoice | The dealership's own business and statutory basis (contract, tax) | ✅ Yes, indefinitely |
| The attribution to a NAMED person | The employment relationship, which has ended | ⚠ No. The name yields; the key remains |
🔎 So the honest guarantee is: the record is permanent, and the actor may become anonymous. That is a stronger position than promising both, and the two-column design in §3 already supports it without change — which is a real argument for it over a seat entity.
7.2 · ⚠ And QR Setu is the PROCESSOR here, which is a product requirement
🌐 For employee data the dealership is the Data Fiduciary and QR Setu is the Processor — and the Fiduciary is liable for its processor's retention, with failure to enforce downstream deletion meaning the data was never legally erased.
Two consequences that are product work, not legal boilerplate:
- The org admin must be able to execute an erasure instruction from inside the product, and it must produce an
audit_logrow. 🌐 "Organisations that struggle are the ones that can't see what they are supposed to erase and can't demonstrate what they did." - A Data Processing Agreement is a sales artifact for enterprise deals, and its content is decided by the mechanism above. ⚠ It does not exist and should be tracked with the commercial model rather than discovered during a first enterprise procurement cycle.
8 · The rules
These supersede R1-R8 in data continuity and are platform-wide. 🔎 Every one of them is cheap now and expensive later, because most of the tables they govern do not exist yet — which makes this the correct moment and not an early one.
| Rule | ⚠ Why | |
|---|---|---|
| L1 | A public.users row is never hard-deleted for a departure. status moves to suspended; 'deleted' is reserved for a completed erasure | The soft-delete state already exists. Use it |
| L2 | users.status must be ENFORCED — auth context, RLS, pgTAP | Today it is a CHECK and nothing else |
| L3 | Access is decided by MEMBERSHIP, never by identity. workspace_members.status = 'active' stays the single gate | Already true and single-sourced. Do not add a second gate |
| L4 | Revocation is TWO writes: membership status, and session termination plus an auth ban | 🌐 active:false does not kill a live session |
| L5 | left_at on membership. Set on every exit, transfer included | Tenure with no end cannot answer what did they do while here |
| L6 | A promotion or a role change is a NEW assignment row with dates, never an UPDATE of role_key | Otherwise last quarter's report changes retroactively |
| L7 | Every business record carries created_by_user_id (immutable, trigger-protected) and assigned_user_id (mutable, nullable) | The owner's own two-concept framing, as two columns |
| L8 | Reassignment is an APPEND-ONLY row — from, to, by, at, reason. Never an in-place overwrite | The history is the audit for the operational half |
| L9 | Every operational FK to users is ON DELETE SET NULL, never CASCADE | 🧮 11 are CASCADE today. A record must outlive its actor |
| L10 | ⚠ Reporting reads the ASSIGNEE for a workload question and the CREATOR for a performance question. Every management screen names which column it folds over | This is the one that will bite. A dashboard folding over assigned_user_id credits Employee B with Employee A's conversions the day after a hand-over. The query runs, the number looks plausible, nobody notices |
| L11 | ⚠ Every rollup folds over the RECORD's workspace_id, never the actor's current membership | Otherwise a transfer moves a person's whole history to the new outlet, and outlet A's last quarter silently shrinks |
| L12 | A returning employee re-activates the SAME users row | A second identity splits their history silently, and nobody notices until a report is short |
| L13 | PII is separable from the identity key, and erasure destroys the MAPPING | 🌐 Pseudonymisation alone is still personal data |
| L14 | Soft-delete lifecycle objects only — identity, membership, assignment, card. Business records are reassigned, never soft-deleted | 🧮 The schema already has three soft-delete idioms; a universal fourth would be worse than all of them |
9 · Consistency across the four customer types
📘 The owner's explicit test. 🔎 The four-layer model passes it by construction, because membership count is what already distinguishes the categories (CLAUDE.md's three-category principle) — so the same rules mean different things without branching:
| Customer type | Memberships | What the lifecycle means |
|---|---|---|
| Consumer | 0 | No membership layer at all. Only identity + erasure. L1, L2, L13 apply; L3-L12 are inert |
| Solo / SMB merchant | 1, member_owned | ⚠ They cannot be deactivated by anyone — they are the tenant. Their exit is an account closure, a different workflow |
| Car dealership employee | 1..n, org_owned | The full set. Multiple memberships is exactly the transfer case |
| Enterprise employee | 1..n, org_owned | Identical to the dealership. 🔎 No dealership-specific mechanism is needed, which was the requirement |
⚠ One measured inconsistency, and it is a real cross-category defect:conversations.consumer_user_id … on delete cascade. A consumer exercising erasure deletes the dealership's own conversation record, including the merchant's replies — a processor action destroying a fiduciary's business record. Under L9 and L13 that FK should be SET NULL with the thread surviving as "customer (erased)". 🔎 The same applies to feature_grants.member_user_id / user_id, where a cascade silently drops a negotiated per-person override.
10 · What must be decided before the dealership implementation locks
| Decision | Blocks | Owner call needed? | |
|---|---|---|---|
| D1 | Where does an employee Setu Card live? A nullable member_user_id on setu_cards (dropping unique-on-workspace), or a separate member-card table. ⚠ 26 workspaces per outlet is not the answer — it breaks workspace = a business with its own P&L, which the tree, oversight and sharing semantics all rest on | My Setu Card, item 6 of the twelve-screen first cut | 🔴 Yes |
| D2 | Does a printed QR encode a POSITION or a PERSON? (§6) | Standee production, the touchpoint table | 🔴 Yes, before printing |
| D3 | Take the RBAC MODEL decision now, keeping the screen deferred. Layer 3 is the position abstraction, and L6 is unimplementable without it | Promotion history, targets, oversight, every rollup | 🟡 Recommended |
✅ Everything else on this page is engineering inside decisions already taken — which is the good news, and it is the same shape as the architecture validation finding: the four-layer platform model absorbs this whole requirement as columns and rules inside an existing boundary, with no tenancy redesign.
11 · Not verified
❓ The FK census counts declarations in migration text, not the live catalogue on Dev, so a later alter could have changed a behaviour read from a create table.
❓ The legal reading in §7 is from secondary sources and is not legal advice — the retention ceiling and the processor-liability position should be confirmed with counsel before a DPA is signed.
❓ Supabase's exact session-revocation semantics (banned_until versus admin sign-out versus token expiry) are stated from documentation, not probed on Dev. 🔎 It is a cheap probe and worth doing before L4 is implemented.