feat(db): hierarchy record class schema + witnesses (contract 1, M4-1a) #1459

Merged
fred merged 10 commits from feat/m4-1a-hierarchy-schema into next 2026-08-28 00:42:36 +00:00
Collaborator

Implements the schema layer of contract 1 (docs/requirements/hierarchy-schema.md) — increment M4-1a of the M4 domain-layer plan. The Gateway command family, audit events, and route inventory follow in M4-1b.

What lands

  1. Five class tables in packages/db/src/schema.ts: companies, estates, platform_projects, workspaces, hierarchy_grants.
    • §2.7 exhaustive column sets. Child node tables carry no timestamps — the contract declares created_at/updated_at only for companies (and created_at for grants); renames are audited via events, so no column is added beyond the declared set.
    • No owner_id anywhere (§4.4, Ruling 2): ownership is computed from grants.
  2. Grant constraints (§3): num_nonnulls(user_id, team_id) = 1 and num_nonnulls(company_id, estate_id, platform_project_id) = 1 CHECKs; six-column UNIQUE NULLS NOT DISTINCT; target FKs CASCADE, principal FKs RESTRICT (§3.3); six btree indexes. Role vocabulary CHECK is contract 2 (M4-2).
  3. Migration 0018_clean_cobalt_man.sql — drizzle-kit generated; SQL text verified against the contract; applies cleanly on PGlite.
  4. hierarchy-schema.witness.test.ts — dual-leg witness suite: PGlite always, real PostgreSQL under DATABASE_URL (the §6.8 binding leg; CI's ci-postgres service runs it). Witnesses: parent-FK integrity + catalog assertion (each child exactly one parent FK, only hierarchy_grants multi-references node tables, nothing references hierarchy_grants), slug scoping (§6.2 half), column allowlist via information_schema (§6.2), all six valid grant forms, subject/target CHECK refusals (zero and two), NULLS NOT DISTINCT duplicate refusal for all six forms with different-role control, NOT NULL refusals, deletion semantics (§6.6): parent delete fail-closed, leaf delete cascades exactly its grants, user/team RESTRICT as subject and as granted_by referent.
  5. hierarchy-writer-coverage.test.ts — §6.3(b) three-prong static assertion: (i) alias-aware schema-symbol writes, (ii) class-table names in SQL strings/templates, (iii) raw-execution primitives (sql.raw, .unsafe, non-template .execute, direct driver imports) outside a closed infrastructure register; eval/new Function and non-literal dynamic imports fail anywhere. Writer allowlist is empty (no production module may write class tables until M4-1b). Closed importer enumerations for the migration runner (four importers, matching contract revision 9's measured set) and migrate-tier (CLI + package barrel). Every prong proven able to fire via a planted-violation control during development.

Verification

  • pnpm exec tsc --noEmit clean; eslint src clean.
  • db package suite: 48 passed, 22 skipped (the real-PG legs, which run in CI where DATABASE_URL is set).
  • Migration 0018 applied on PGlite; all five tables confirmed via information_schema.

Ruling 4b note: this PR adds no visibility attribute — that lands in the 4b amendment after #1436 merges, before company-create semantics freeze (M4-1b).

Implements the schema layer of contract 1 (`docs/requirements/hierarchy-schema.md`) — increment M4-1a of the M4 domain-layer plan. The Gateway command family, audit events, and route inventory follow in M4-1b. ## What lands 1. **Five class tables** in `packages/db/src/schema.ts`: `companies`, `estates`, `platform_projects`, `workspaces`, `hierarchy_grants`. - §2.7 exhaustive column sets. Child node tables carry **no timestamps** — the contract declares `created_at`/`updated_at` only for companies (and `created_at` for grants); renames are audited via events, so no column is added beyond the declared set. - No `owner_id` anywhere (§4.4, Ruling 2): ownership is computed from grants. 2. **Grant constraints** (§3): `num_nonnulls(user_id, team_id) = 1` and `num_nonnulls(company_id, estate_id, platform_project_id) = 1` CHECKs; six-column `UNIQUE NULLS NOT DISTINCT`; target FKs CASCADE, principal FKs RESTRICT (§3.3); six btree indexes. Role vocabulary CHECK is contract 2 (M4-2). 3. **Migration `0018_clean_cobalt_man.sql`** — drizzle-kit generated; SQL text verified against the contract; applies cleanly on PGlite. 4. **`hierarchy-schema.witness.test.ts`** — dual-leg witness suite: PGlite always, real PostgreSQL under `DATABASE_URL` (the §6.8 binding leg; CI's `ci-postgres` service runs it). Witnesses: parent-FK integrity + catalog assertion (each child exactly one parent FK, only `hierarchy_grants` multi-references node tables, nothing references `hierarchy_grants`), slug scoping (§6.2 half), column allowlist via `information_schema` (§6.2), all six valid grant forms, subject/target CHECK refusals (zero and two), NULLS NOT DISTINCT duplicate refusal for all six forms with different-role control, NOT NULL refusals, deletion semantics (§6.6): parent delete fail-closed, leaf delete cascades exactly its grants, user/team RESTRICT as subject and as `granted_by` referent. 5. **`hierarchy-writer-coverage.test.ts`** — §6.3(b) three-prong static assertion: (i) alias-aware schema-symbol writes, (ii) class-table names in SQL strings/templates, (iii) raw-execution primitives (`sql.raw`, `.unsafe`, non-template `.execute`, direct driver imports) outside a closed infrastructure register; `eval`/`new Function` and non-literal dynamic imports fail anywhere. Writer allowlist is **empty** (no production module may write class tables until M4-1b). Closed importer enumerations for the migration runner (four importers, matching contract revision 9's measured set) and migrate-tier (CLI + package barrel). Every prong proven able to fire via a planted-violation control during development. ## Verification - `pnpm exec tsc --noEmit` clean; `eslint src` clean. - db package suite: 48 passed, 22 skipped (the real-PG legs, which run in CI where `DATABASE_URL` is set). - Migration 0018 applied on PGlite; all five tables confirmed via `information_schema`. Ruling 4b note: this PR adds no `visibility` attribute — that lands in the 4b amendment after #1436 merges, before company-create semantics freeze (M4-1b).
fred added 1 commit 2026-08-27 20:37:28 +00:00
Implements docs/requirements/hierarchy-schema.md sections 2-4 and the
schema-layer half of section 6:

- Five class tables (companies, estates, platform_projects, workspaces,
  hierarchy_grants) with the section 2.7 exhaustive column sets: child
  node tables carry no timestamps (renames are audited via events), no
  owner_id anywhere (section 4.4 - ownership is computed from grants).
- Grant constraints per section 3: exactly-one-subject and
  exactly-one-target num_nonnulls CHECKs, six-column UNIQUE NULLS NOT
  DISTINCT, target FKs CASCADE / principal FKs RESTRICT, six btree
  indexes.
- Migration 0018 generated by drizzle-kit; SQL verified against the
  contract text and applied on PGlite.
- hierarchy-schema.witness.test.ts: dual-leg witness suite (PGlite
  always; real PostgreSQL under DATABASE_URL, the section 6.8 binding
  leg in CI). Covers parent-FK integrity + catalog assertion, slug
  scoping, column allowlist (6.2), all six grant subject/target forms,
  CHECK refusals, NULLS NOT DISTINCT duplicates, NOT NULL refusals, and
  deletion semantics (6.6): fail-closed parent delete, leaf cascade of
  exactly its grants, principal RESTRICT.
- hierarchy-writer-coverage.test.ts: section 6.3(b) three-prong static
  assertion (alias-aware symbol writes, class-table names in SQL
  literals, raw-execution primitives) with empty writer allowlist,
  closed infrastructure register, and closed importer enumerations for
  the migration runner and migrate-tier. All prongs proven able to fire
  via a planted-violation control.

Command family, audit events, and route inventory land in M4-1b.
fred added 1 commit 2026-08-27 21:16:45 +00:00
fix(db): harden writer-coverage assertion per M4-1a review
ci/woodpecker/pr/ci Pipeline was successful
8305d129a2
Review findings F1-F8 (REQUEST_CHANGES) addressed:
- F5: replace regex comment stripping with a string-aware lexer producing
  comment-free code plus accurate string/template spans (handles nesting,
  regex literals, escapes)
- F3: prong (i) now tracks namespace imports and re-export conduits via a
  fixpoint over the scanned import graph
- F1/F2: tagged-template exemption dropped; backlog.ts joins the infra
  register; capability-gated prong (iii) — driver import flags any-receiver
  execute/query/unsafe, factory import flags any-receiver execute/unsafe,
  db-shaped receiver backstop covers DI'd handles; sql.raw tracked through
  aliases and namespaces
- F4: closed importer enumeration added for createDb/createPgliteDb;
  composition property of remaining register modules documented
- F6: plugins/ added to scan roots
- F7: per-parent slug scoping witnessed at platform_projects and workspaces
- F8: dynamic-import disposition register added; stricter-than-contract
  stances documented in the header

The review's 8 evasion samples are embedded as permanent controls run
through the production analyzer, plus clean controls guarding against
false positives.
fred added 1 commit 2026-08-27 21:40:02 +00:00
Per the second M4-1a detached review (REQUEST_CHANGES, R1-R8):

- R1: prong (i) now tracks namespace destructuring, nested namespace
  re-exports (ns.schema.companies), and literal dynamic-import bindings
  (destructured and namespace) per contract rev 9.
- R2: capability-conduit fixpoint (computeCapabilityConduits) closes
  driver/factory laundering through export-from modules.
- R3: dynamic-import check is per call site with a full-literal tail
  match, so concatenated specifiers no longer pass.
- R4: computed-member calls with literal verb names (obj['insert'],
  obj['query']) and spaced member access are flagged.
- R5: prong (ii) adjacency tolerates schema qualification, interposed
  block comments, COPY, and escaped quotes.
- R6/R8: header documents KNOWN RESIDUALS (DI receiver rename in
  capability-free files, computed non-literal member access, scan
  perimeter) with the measured counterfactuals.
- createRequire fails outside a 5-module measured register (R1 route).
- 15 new permanent evasion controls (E9-E23) with helper-file conduits
  run through the production analyzer.

Calibration: tree-wide prong test green with zero new exclusions;
tsc, eslint, and the full package suite pass.
fred added 1 commit 2026-08-27 22:05:39 +00:00
Round-3 review remediation (G1-G6):
- export-map schema conduits: renames propagate through export-from,
  export-of-local, and export-const-from-namespace routes (G1)
- fail-closed namespace destructure over-approximation + depth-2 alias
  derivation (const t = ns.companies, const { schema } = ns) (G1)
- factory capability: consumer-side name gate dropped for conduit imports;
  export-of-local factory bindings make a module a factory conduit (G2)
- createRequire tracked through import aliases and dynamic destructure (G3)
- literal dynamic import of a schema/factory/driver source outside the
  tracked const-await binding shape is a violation (.then/deferred/
  Promise.all) (G4)
- prong (ii): quoted schema qualifier and SQL line-comment gaps (G5)
- computed eval, spaced execution verbs, bracket-form and parenthesized
  write arguments, spaced sql.raw (G6)
- value-flow residual documented in KNOWN RESIDUALS with counterfactuals
- 16 new permanent controls (E24-E39)
fred added 1 commit 2026-08-27 22:29:57 +00:00
Default-export slot treated as an export name at both ends (helper-side
export-default passes, consumer-side bare-default-import resolution);
namespace-member write targets match the source's exported names via a
per-file memberSyms union; factory conduits carry a name map computed to
a fixpoint so renames propagate hop by hop (export-from, export-of-local,
export default); tracked dynamic imports of capability conduits confer
capability; factory destructure-rename from a schema namespace; static
module-namespace createRequire destructure; spread-argument writes; DCL
keywords (GRANT/REVOKE ON, LOCK TABLE); bracketed conventional DI
receiver. Permanent controls E40-E53.
fred added 1 commit 2026-08-27 22:51:42 +00:00
Unify member-access matching on shared DOT/BRACKET_OPEN/MEMBER_SEG shapes
(dot, bracket, ?. and ! forms) across both conduit computations, the write
targets, the factory-import clause, and the DI backstop. Treat export-default
as an expression at both ends: strip parens/assertions, classify bare-word vs
namespace-member chain, on the schema AND capability sides. Add the
capability-side export-default ns-member pass (round-5 finding 2). Admit bare
SQL qualifier words (TABLE, ONLY, IF EXISTS) between keyword and table name in
prong (ii). Controls E54-E68.
fred added 1 commit 2026-08-27 23:15:35 +00:00
stripExprDressing also strips trailing non-null assertions and satisfies
clauses, so export-default dressing (companies!, createDb satisfies T,
(x as unknown)!) resolves at both ends. The three ns-member right-hand
sides tolerate parenthesization. Every verb matcher admits the optional-
call form (?.() via a shared CALL_OPEN shape. A computed member call
whose key is a text-only template literal fails closed (its text never
reaches lexed code); interpolated keys stay under the non-literal
residual. Document the DB_FACTORY_IMPORTERS ns-destructure enumeration
blind spot as a residual. Controls E69-E81.
fred added 1 commit 2026-08-27 23:38:45 +00:00
The text-only template-key rule fires on any computed member — access or
call — not only the call form, so template-keyed write targets, export
expressions, and receivers fail closed at their origin. Declaration
exports and derivations tolerate a type annotation and prior declarators
via shared TYPE_ANN/DECL_LIST shapes at all five declarator sites.
Invoking a write/exec verb through .apply/.call/.bind fails closed
anywhere (both member names statically visible, unlike the
method-extraction residual). The evasion test counts violations across
the whole synthetic chain, since some routes fail closed at the helper.
Controls E82-E92; clean control pinning the interpolated-key
discriminator.
fred added 1 commit 2026-08-28 00:03:41 +00:00
- Dressed quoted computed keys (as/satisfies/!) fail closed anywhere: the
  static key survives the dressing but breaks every ['name'] matcher, and
  matcher tolerance cannot span types containing ']' (as Foo['x']), so the
  shape itself is the trigger (with an !(?!=) guard for ordinary
  comparisons).
- Escape-built quoted keys (\u/\x/octal) fail closed: statically
  resolvable, so outside the non-literal computed-member residual.
- Reflect verb indirection fails closed: any Reflect.* call naming a
  write/exec verb in its argument text (Reflect.apply(db.insert, ...),
  Reflect.get(db, 'insert')).
- CODE_SHAPE_REGISTER: enumerated disposition path for the fail-closed
  code-shape rules (template key, dressed/escaped key, apply/call/bind,
  Reflect) — a reviewed legitimate hit is registered, never resolved by
  weakening the shape. Empty today; eval/new Function stays unconditional.
- KNOWN RESIDUALS: test-file/out-of-src modules named as import-graph
  conduit blind spots; TYPE_ANN/DECL_LIST single-line limit documented.
- Controls E93-E102 (dressed/escaped keys at write target, conduit export,
  default export, single-file factory extraction; Reflect.apply/get) plus a
  clean control pinning the !== guard.
fred added 1 commit 2026-08-28 00:25:48 +00:00
rev-code-01 approved these changes 2026-08-28 00:40:07 +00:00
rev-code-01 left a comment
Member

APPROVED — rev-code-01, pinned to head 14457a83227c58f5a065f8b4bf6d664260411658 (re-verified unmoved immediately before posting).

Scope: 6 files, +8017/−1 (bulk = drizzle meta snapshot 5034 + the two suites 2808) — the contract-1 schema layer. I reviewed contract 1 at rev 9 (PR #1435, my review id 328); this implementation is verified against that contract independently of the ten GLM rounds:

  1. Schema exact vs contract §2/§3 (every clause measured): companies carries exactly id/name/slug-unique/created_at/updated_at; the three child tables carry exactly id/name/slug/NOT-NULL-parent-FK with per-parent composite slug uniques and NO timestamps (the §2.7 exhaustive-set reading — the PR body's no-timestamps note is the contract-faithful one, since renames are audited events); grants carry exactly the §3.1 column set with num_nonnulls exactly-one-of CHECKs (subject, target), the six-column UNIQUE NULLS NOT DISTINCT, target FKs CASCADE / principal FKs + granted_by RESTRICT (the §3.3 split), role vocab deliberately unpinned (contract 2 §2 owns it — correctly deferred), no owner_id anywhere, no metadata column. No extra columns, no missing ones.
  2. Migration 0018 SQL text verified against the schema declaration line-by-line (both directions: constraint names, NULLS NOT DISTINCT, all nine FK actions, six indexes); applied cleanly on PGlite by the witness suite's own boot.
  3. Witness suite (§6.1/6.2/6.6, PGlite leg) run by me: 16/16 green — full valid chain, siblings control, null/dangling parent refusals, catalog assertion (exactly one parent-FK per child, no edge table, only grants multi-reference), slug scoping both directions, information_schema column allowlist, six valid grant forms, zero/two-subject and zero/two-target refusals, all-six-forms duplicate refusal, NOT NULL refusals, deletion semantics (fail-closed parent, cascading leaf grants, RESTRICT as subject and granted_by). The real-PostgreSQL leg (§6.8 binding) is correctly DATABASE_URL-gated and runs on ci-postgres in CI — pipelines 2911-2919 all green, 2920 running on this head.
  4. Writer-coverage assertion (§6.3b) — my own red control, which initially did NOT fire and led to a real finding about my own control: my first planted db.insert(companies) outside src/ was invisible — the scanner walks <root>/<pkg>/src only. Planted INSIDE apps/gateway/src/, the tree-calibration test fails exactly as designed: [prong i-symbol] apps/gateway/src/dbtest-writer-probe.ts: .insert(companies) outside the writer allowlist, and removal restores green. The suite is 9/9 including the E1-E112 evasion battery and the 554-file production-tree calibration (0 violations). The src/-only perimeter is a DOCUMENTED accepted residual (non-src TS — framework/**, configs, e2e helpers — unscanned, verified free of db/driver/execute references; test files also invisible as import conduits, flagged as review-visible anomalous) — consistent with the contract's rev-6 honesty rule that the witness claims only what it statically sees. The empty writer allowlist is the correct M4-1a state (no production writer until M4-1b); the infrastructure register, the four-importer migration-runner enumeration (matching my own id-328 measurement exactly), migrate-tier/db-factory enumerations, dynamic-import and createRequire registers, and the empty code-shape register are each closed with justifications I spot-read.
  5. Gates run by me at this head (fresh worktree, build-first): db build rc=0; db suite 52 pass/23 skip (skips = the PG legs, CI-bound); writer-coverage 9/9; witness 16/16; repo typecheck 45/45; lint 25/25; format rc=0.

Non-blocking:

  • [S1] The src/-perimeter residual is accepted and documented; when M4-1b lands, consider a one-line companion assertion that no NEW non-src .ts appears under the three roots carrying db-package imports (a cheap widening of the documented verification from at-review-time to always) — optional, not required by the contract.

CI note: 2920 running at review time (nine prior pipelines green). Merge is fred's on this approval + 2920 terminal green.

**APPROVED — rev-code-01, pinned to head `14457a83227c58f5a065f8b4bf6d664260411658`** (re-verified unmoved immediately before posting). Scope: 6 files, +8017/−1 (bulk = drizzle meta snapshot 5034 + the two suites 2808) — the contract-1 schema layer. I reviewed contract 1 at rev 9 (PR #1435, my review id 328); this implementation is verified against that contract independently of the ten GLM rounds: 1. **Schema exact vs contract §2/§3 (every clause measured):** companies carries exactly id/name/slug-unique/created_at/updated_at; the three child tables carry exactly id/name/slug/NOT-NULL-parent-FK with per-parent composite slug uniques and NO timestamps (the §2.7 exhaustive-set reading — the PR body's no-timestamps note is the contract-faithful one, since renames are audited events); grants carry exactly the §3.1 column set with `num_nonnulls` exactly-one-of CHECKs (subject, target), the six-column `UNIQUE NULLS NOT DISTINCT`, target FKs CASCADE / principal FKs + granted_by RESTRICT (the §3.3 split), role vocab deliberately unpinned (contract 2 §2 owns it — correctly deferred), no owner_id anywhere, no metadata column. No extra columns, no missing ones. 2. **Migration 0018 SQL text verified against the schema declaration** line-by-line (both directions: constraint names, NULLS NOT DISTINCT, all nine FK actions, six indexes); applied cleanly on PGlite by the witness suite's own boot. 3. **Witness suite (§6.1/6.2/6.6, PGlite leg) run by me: 16/16 green** — full valid chain, siblings control, null/dangling parent refusals, catalog assertion (exactly one parent-FK per child, no edge table, only grants multi-reference), slug scoping both directions, information_schema column allowlist, six valid grant forms, zero/two-subject and zero/two-target refusals, all-six-forms duplicate refusal, NOT NULL refusals, deletion semantics (fail-closed parent, cascading leaf grants, RESTRICT as subject and granted_by). The real-PostgreSQL leg (§6.8 binding) is correctly DATABASE_URL-gated and runs on ci-postgres in CI — pipelines 2911-2919 all green, 2920 running on this head. 4. **Writer-coverage assertion (§6.3b) — my own red control, which initially did NOT fire and led to a real finding about my own control:** my first planted `db.insert(companies)` outside `src/` was invisible — the scanner walks `<root>/<pkg>/src` only. Planted INSIDE `apps/gateway/src/`, the tree-calibration test fails exactly as designed: `[prong i-symbol] apps/gateway/src/dbtest-writer-probe.ts: .insert(companies) outside the writer allowlist`, and removal restores green. The suite is 9/9 including the E1-E112 evasion battery and the 554-file production-tree calibration (0 violations). The src/-only perimeter is a DOCUMENTED accepted residual (non-src TS — framework/**, configs, e2e helpers — unscanned, verified free of db/driver/execute references; test files also invisible as import conduits, flagged as review-visible anomalous) — consistent with the contract's rev-6 honesty rule that the witness claims only what it statically sees. The empty writer allowlist is the correct M4-1a state (no production writer until M4-1b); the infrastructure register, the four-importer migration-runner enumeration (matching my own id-328 measurement exactly), migrate-tier/db-factory enumerations, dynamic-import and createRequire registers, and the empty code-shape register are each closed with justifications I spot-read. 5. **Gates run by me at this head (fresh worktree, build-first):** db build rc=0; db suite 52 pass/23 skip (skips = the PG legs, CI-bound); writer-coverage 9/9; witness 16/16; repo typecheck 45/45; lint 25/25; format rc=0. Non-blocking: - **[S1]** The src/-perimeter residual is accepted and documented; when M4-1b lands, consider a one-line companion assertion that no NEW non-src .ts appears under the three roots carrying db-package imports (a cheap widening of the documented verification from at-review-time to always) — optional, not required by the contract. CI note: 2920 running at review time (nine prior pipelines green). Merge is fred's on this approval + 2920 terminal green.
fred merged commit 5964dab891 into next 2026-08-28 00:42:36 +00:00
Sign in to join this conversation.