Appearance
ADR-0020 · Platform schema, redesigned from first principles
Status: 🟡 Proposed — authored 2026-08-07, awaiting owner approval · Supersedes in part: ADR-0001 (tenancy model), ADR-0007 (entitlement storage), ADR-0009 (capability storage) · Amends: ADR-0014 (the projection problem is designed out, not policed) · Design detail: data-architecture.md
The one-line thesis
20260710134136_baseline_schema_from_prod.sql — 58 tables, 705 columns, lifted verbatim from a pre-vision production database — has been the implicit reference for every schema decision in this repo, and that is why the drift kept recurring. qr-setu-dev is the source of truth and is greenfield: archive the baseline and the series built on it, author a new one from first principles, and rebuild Dev. Production is disposable and is not a design input.
Context
Every schema decision in this repo to date has been taken against the baseline squash (20260710134136_baseline_schema_from_prod.sql), which was itself lifted verbatim from a production database built before the platform had an architectural direction. The standards programme has been retrofitting around it: ADR-0009 added a capability layer beside ADR-0007's untouched entitlement tables; ADR-0014 built an entire least-privilege apparatus to police one public projection; ADR-0016 replaced the reminders schema outright after finding it could not express a recurring reminder.
On 2026-08-07 the owner directed, for the third time, that this stop: qr-setu-dev is the source of truth and is to be treated as greenfield, with standing authority to redesign any backend artifact from scratch. Production is disposable — it will be replaced by replicating Dev when Dev is release-ready — so it carries no compatibility obligation and is not a design input.
This ADR exists because the same error was made three times in two days, always in the same direction, and always while feeling like prudence. (1) Drafting the feature-management foundation, platform_features was found to already exist and the migration was rewritten to ALTER it. (2) A proposal was then made to adopt Prod's 18-row feature registry as the product vocabulary. (3) The first draft of this very ADR opened with Prod row counts as justification and specified an 11-profile cutover — arguing from the artifact it was meant to discard.
"It already exists" is an argument about cost, and it is only decisive when the existing shape can express the requirement. ADR-0016 established exactly this for one table and it was not enough — which is why the rule now lives in
CLAUDE.mdas a standing top-level rule ("Second rule: Dev is the source of truth") rather than as a precedent in one ADR.Pointer added 2026-09-23 (QRS-1288): the rule's canonical text is now Operating rules § R-02;
CLAUDE.mdcarries its generated short form. This ADR is a log and is not rewritten.
What is actually being replaced
A file, not an environment. 20260710134136_baseline_schema_from_prod.sql — 58 tables, 705 columns, 45 of them on profiles alone — was a one-time squash captured in July 2026 to get the schema under version control. That was right for traceability and wrong for design: from that moment it was the implicit reference for everything built afterwards, and Dev carries it too.
So "greenfield Dev" has a concrete mechanism: archive the baseline and the series built on it, author a new baseline from the design, rebuild Dev. No cutover, no data migration, no archive of live rows — designing any of those would be inventing a constraint that does not exist.
The faults enumerated below are properties of that file. They are cited so the new model can be checked against the problems it must make impossible — not as an inventory to preserve.
Decision
Design and build a new platform data model per data-architecture.md, on eight principles. The four that overturn prior decisions:
D1 — The workspace is the primary tenant; a solo merchant is N=1
Rejected: ADR-0001's "organization as a billing wrapper over independent profiles." That shape is a retrofit forced by profiles being simultaneously person, business, public card, preferences and billing subject. Making workspaces primary — with a solo merchant as a one-member workspace — means enterprise costs nothing structurally, because it is the same rows.
workspaces.ownership_model (member_owned | org_owned) resolves the owner's paying-≠-owning requirement in one column: on seat revocation, org_owned suspends the workspace, member_owned drops it to the free plan and keeps its card. A dealership and an MLM upline then use identical seats rows.
D2 — The public surface is its own table, not a projection of a private one
anon receives SELECT on cards (+ card_links, card_hours) and on nothing else. This is a partial amendment to ADR-0014, not a repudiation of it — every grant rule that ADR stands up remains correct. What changes is that the hardest problem it addresses stops existing: with no private column in the public table, there is no allow-list to maintain, no hand-computed "81 residual" constant, and no possibility that a new public field leaks email, gstin or a future pan.
The duplication between cards.display_name and workspaces.display_name is deliberate and is the point. One is the name the merchant publishes; the other is their legal or internal name. Storing them in one column is what makes every public read a leak risk. They are two facts about two audiences.
D3 — One feature registry, three axes, seven scopes, one resolver
Rejected: extending either existing mechanism. ADR-0007's platform_features /domain_features/ user_feature_permissions (18/23/0 rows, zero call sites) and ADR-0009's capabilities/archetype_default_capabilities/profile_capabilities are two registries with two override chains and no resolver combining them — which is exactly why the owner's six-scope Access Control requirement has no home. Extending one leaves the other; keeping both is the duplicate- source-of-truth defect this repo has hit at QRS-249, QRS-284 and QRS-287.
The axes are not scopes and must not be collapsed: applicability (does this workflow apply to this business), entitlement (is it unlocked, how much), availability (is the code shipped). effective = applicable AND entitled AND available. One feature_grants table carries every grant on every axis at all seven scopes, so the Access Control matrix is a query.
Three consequences worth naming:
planshas nofeatures jsonb. A plan's features are grants atscope_kind='plan'. This retiressubscription_tiers.features/limitsas a second mechanism.applicability = universalreplaces aCORE_FEATURESconstant in code with a registry row — admin-visible and auditable, rather than a TypeScript array a screen could contradict.- Scope precedence is a table, not a
CASE. An eighth scope is a data change; and the precedence is readable by the admin panel that must render it.
Correction to the 2026-08-06 draft: polymorphic scope is modelled as exactly-one-non-null FK columns with a CHECK, not scope_id text plus a validation trigger. Postgres enforces referential integrity; a trigger would have to be tested to do worse.
D4 — The principal is a user; three user categories differ by workspace-membership count
Added 2026-08-07, and it corrects D1 as first written. D1 originally said "the workspace is the primary tenant" — which serves categories 1 and 2 and cannot serve category 3 at all, because an individual consumer has no business, no card, and no workspace. Any resolver, RPC or policy that requires a workspace is structurally unable to serve them.
| Category | Workspace memberships | Governs own archetype/plan |
|---|---|---|
| 1 · Solo business owner (primary R1 audience) | 1, member-owned | Yes |
| 2 · Enterprise employee | 1, org-owned | No — the org admin does |
| 3 · Individual consumer | 0 | N/A |
Consequences that change the model rather than annotate it:
resolve_features(p_user_id, p_workspace_id default null)— user required, workspace optional.users.primary_context (business|individual)selects the default experience; it is not an exclusion, so a consumer who later starts a business gains a membership, never a second account. ⚠ It is a PREFERENCE and never a security input — the authoritative "does this person have a business?" is derived from membership. A user-mutable column that grants access is a privilege-escalation primitive, and storing a derivable fact duplicates a source of truth. (Namedaccount_typein the 2026-08-07 first draft; renamed for exactly this reason.)- The transition is a pure
INSERT, and that is the acceptance test — oneworkspacesrow + oneworkspace_membersrow. AnyUPDATEto consumer-side data, row move, or migration means the model is wrong. pgTAP-checkable, and it fails loudly if the two identities are ever coupled. orders.buyer_user_id uuid NULLships now. The earlier "buyer accounts: deliberately not in this design" line is reversed — identity retrofitted onto a high-volume transactional table is expensive; only the consumer UI is deferred.- One new grant scope:
user, taking the total to eight. Anaccount_typescope was added in the first draft and then dropped: consumer features belong atplatformscope (merchants are consumers too), and business features are not applicable without a workspace — which the applicability axis already answers, so the scope would have resolved on a derived fact. - Archetype immutability becomes governor-scoped, not absolute — correcting the plan's S-D4, whose unqualified write-once trigger would have made enterprise employee management impossible.
D4a — ⚠ authenticated stops meaning "merchant", and that is a threat-model change
Category 3 will outnumber merchants by orders of magnitude and is equally authenticated. ADR-0014 reasons about anon vs authenticated on the assumption that the latter is a small, semi-trusted merchant population; that assumption expires the day consumers can register.
Therefore no policy may grant access by role alone. Every merchant-data policy scopes by relationship — workspace_id in (select workspace_id from workspace_members where user_id = auth.uid()) — and using (true) TO authenticated is banned. pgTAP must assert that an authenticated user with zero workspace memberships can read nothing merchant-owned. This is the single highest-consequence finding from the three-category clarification, because it is silent: every existing TO authenticated policy keeps passing its current tests while its blast radius grows by the size of the consumer population.
D5 — Stable text keys for platform reference data
industries.key = 'salon', not business_domain_id = 14. Integer ids for data promoted across environments produced QRS-249 — hardcoded ids disagreeing with the database — and forced the standing rule "ids 11-22, PINNED explicitly, never nextval." A text key is self-describing in every log line and every jsonb payload, and makes that hazard class unrepresentable rather than merely documented.
Options considered and rejected
| Option | Why rejected |
|---|---|
| Extend the baseline schema (the path taken until this ADR) | Cannot express workspaces, seats, group subscriptions, or a unified feature resolver without the retrofits enumerated above. Every one of the five structural faults survives. |
| Extend it now, redesign after launch | Greenfield authority on Dev is worth only what is used before the first shipped APK. After that, requires_min_app_build bounds every DROP and this becomes a multi-month expand-contract programme instead of a baseline rewrite. |
| Migrate the old schema's rows into the new model | There is nothing to migrate. Dev is greenfield by directive; Prod is replaced by replicating Dev, as a separate exercise the owner initiates. Building a migration path would be inventing a constraint nobody has. |
New app schema alongside public | Supabase's PostgREST exposure, RLS defaults and the whole tooling chain assume public. Re-baselining public achieves full isolation with no db.schemas reconfiguration. |
| Adopt the ADR-0007 registry rows as the feature taxonomy | Proposed on 2026-08-06 and the specific error this ADR corrects. Rows authored before the archetype work, with zero consumers, are an artifact — not a product vocabulary. |
Keep capabilities as the applicability axis and add the rest | Two mechanisms, and the S1 client wiring would have to be rewritten anyway once plan/group/member scopes arrive. Folding it in now costs one day and removes a permanent seam. |
Consequences
Accepted costs, stated rather than smoothed:
- ~13.5 work-days, and it precedes Sections C/D/E/F of the 26.0.1 plan. A first-principles redesign and a 15 Aug public launch are mutually exclusive. The recommendation is to move the launch; the alternative is shipping the first release on a schema already agreed to be wrong.
- Dev is wiped and rebuilt.
drop schema public cascadethen the new series.auth.*is untouched so sign-in survives, but every existing Dev app row is gone — which on a greenfield Dev is the intent, not a casualty. - S1's capability wiring (2026-08-06) is partially superseded.
get_capabilities_for_profile,_shared/capabilities.tsandapps/mobile/src/capabilities/were built one day before this ADR. The client contract survives intact —useFeature('store')is unchanged, which is exactly why asking for a feature rather than a capability was the right call — but the resolver and service beneath it are replaced. This is real rework and it is the cost of having wired one axis before designing all three. feature_flags(20260806160000) is retained, folded in as theavailabilityaxis atscope_kind='platform'. It remains the only revert lever until the admin panel exists.- Every ADR that references
profilesneeds an amendment note. ADR-0001, 0006, 0007, 0009, 0010 and 0014 all name it.
What it buys:
- Enterprise workspaces, seats and group subscriptions become the default shape rather than a future migration across a 58-table schema.
- The public-exposure defect class is designed out.
- One control plane the Access Control screen can render directly, with per-axis provenance so support can answer "why is this off for this merchant?" in one query.
compliance_profileonindustriesretires the permanentresolvePromofail-closed limitation (ADR-0004) and unblocks QRS-086.- The
outboxmakes cache invalidation transactional, closing G1/G2 with a mechanism rather than a best-effort HTTP call inside a request.
Open decisions this ADR needs
- The plan/tier vocabulary.
plans.keyis one platform-wide list and every entitlement grant references it; three vocabularies currently exist in the repo. Blocking the resolver. workspacesvsbusinessesas the table and vocabulary name — identical model, propagates into every table name, RPC and screen.