Skip to content

measure(adr-0131 D1): a database 18.0.0 created is certified by its birth marker yet keeps every tenant column nullable, and the ceremony certifies tables it left nullable — D1 is not yet a fact the certificate proves #24919

Open

Description

@objectstack-fleet

Filing gate ③: a finding carried from the 18.x line, step 7 (#24583). It comes from the CONTRACT_REVIEW_TIER review of PR #24893 (7b-1+7b-2), record 6293077099, under "Observation (not this part's)".

Not an 18.0 gate, and not a defect today. No reader on HEAD treats a NULL organization as meaningful: 7a-1 retired the driver arm, 7a-2/7a-3 the authz readers, and 7z made check-tenant-chokepoint refuse the arm. What stays open is the shape ADR-0131 D1 promises ("no nullable tenant column anywhere").

What is measured

  1. 18.0.0-born stores. The first boot that creates a datastore writes the birth marker (details.birth.shapeGeneration = 1, runtime organization-ownership-boot-guard.ts writeBirthMarker). 18.0.0 created every tenant column nullable; 7b-0 makes fresh tables NOT NULL at createTable only from the next release on. Such a store reads as certified (organizationOwnershipShapeGenerationOf reads birth ?? checkpoint, at ORGANIZATION_OWNERSHIP_SHAPE_GENERATION = 1), so no step ever tightens its columns. The fixture in test(dogfood): fixture-18.0.0 — the upgrade harness boots a database the published 18.0.0 created #24911 (fixture-18.0.0/) is such a store.
  2. Ceremonied stores. The ceremony's not-null step sets NOT NULL only where a table will receive it and its NULL count is 0 (organization-ownership-apply.ts runNotNull). The other tables (not-declared, unsupported on the Turso remote face, and sys_email_template per the 7b-12 measurement) stay nullable, and the store is still certified at generation 1.
  3. The consequence that is live once feat(driver-sql,driver-turso,driver-memory,objectql,cli,metadata-protocol): plain organization key part in new unique indexes; the differ reads COALESCE(tenant) as tenant on a certified database (18.x step 7b-1 + 7b-2) #24893 lands. A new organization-scoped unique declared on an existing table of such a store gets the plain index, which exempts NULL-organization rows. feat(driver-sql,driver-turso,driver-memory,objectql,cli,metadata-protocol): plain organization key part in new unique indexes; the differ reads COALESCE(tenant) as tenant on a certified database (18.x step 7b-1 + 7b-2) #24893's changeset is being corrected to say so (B1).

The question to measure, then decide

How does D1 become true for stores that are already certified, without making an 18.0.0 install refuse its own next boot? Bumping ORGANIZATION_OWNERSHIP_SHAPE_GENERATION would refuse every 18.0.0-born store until it runs a ceremony; the 18.x line has kept the generation at 1 for that reason.

  • Measure:
    • how many tables per posture stay nullable on an 18.0.0-born store and on a ceremonied store;
    • whether any of them can hold a NULL organization_id row today (7b-3 measured such rows on an 18.0.0-born store, record 6284717698);
    • what an attributing NOT NULL step would need for an already-certified store: a boot-time tighten (a narrowing DDL, never applied unattended today), an operator command, or a generation bump with its boot refusal.
  • Then: a decision card with those options, a recommendation, and the ADR-0131 D1 text each one makes true.

Blocks on: step 7 (#24583) landing. 7b-0 and 7b-1+7b-2 change the baseline this measures.

Activity

  1. objectstack-fleet commented on Oct 20, 2026

    @objectstack-fleet
    ContributorAuthor

    Hold · epic PM session_01Y5583bs2RDd53MH6kM1ca3 (os-steve) · 2026-10-20T14:51Z. The epic seat (epic:#15194) holds this card. It is not for another seat to claim.
    Blocked-by: #24583

    The seat sorted the 18.x line's follow-ups. The cards that decide whether ADR-0131 is actually complete stay with the epic: #24897, #24919, #24883 and #24902. #24899 (security) is dispatched now. #24898 and #24900 stay in the shared queue. The maintainer agreed, verbatim: 「同意」.

    When step 7 (#24583) closes, the seat dispatches:

  2. objectstack-fleet commented on Oct 20, 2026

    @objectstack-fleet
    ContributorAuthor

    Measurement for this card · epic PM session_01Y5583bs2RDd53MH6kM1ca3 (os-steve) · 2026-10-20T17:34Z. The 7b-0 dev (#24940) took it on a copy of fixture-18.0.0, the database the published 18.0.0 created (#24911), with HEAD of 7b-0's branch merged with main 71696121a.

    • The ceremony runs on an 18.0.0-born database. HEAD's os migrate organization-ownership plan and apply, with auth composed, answered complete: true, blocking: 0.
    • It tightens 62 of 65 tenant tables to NOT NULL. The 3 left nullable:
      • a rotated shard (the ceremony reports that it cannot tighten one);
      • sys_email_template (the mirror-deletion fate);
      • sys_sso_provider (tenancy disabled).
    • Without OS_AUTH_SECRET, the apply blocks at the rewrite step: the security catalog does not resolve admin_full_access. That is the existing gate.

    What this changes here. There is already an operator path that brings an 18.0.0-born database to D1 for almost every table: run the ceremony, which the birth marker does not require. So the decision this card owes may be smaller than "a generation bump or a new command". It could be enough to document the voluntary ceremony and settle the 3 residual tables. The measurement this card asks for still stands, in particular whether any of those tables can hold a NULL row today.

  3. objectstack-fleet commented on Oct 21, 2026

    @objectstack-fleet
    ContributorAuthor

    Claim: epic seat round · 2026-10-21T18:22Z · releasing the epic's hold (6293749011) now that step 7 (#24583) is closed (6323222024). This is a MEASURE round, then a decision card.
    Session: session_01Y5583bs2RDd53MH6kM1ca3
    Account: os-steve (the card's assignee)
    Branch: none this round
    Worktree: the cloud session's own checkout (mode:cloud)
    Domain: domain:engine
    Seat: epic:#15194
    File surface:

    • Measurement only. No commit to main in this round.
    • The deliverable is an os-dev measurement report on this card. It completes the card's "Measure" list on main at or after d5d8146012, which carries 7b-0 and 7b-1+7b-2, and builds on the measurement 6297109158.
    • The seat then files the decision card the body names.
    • ⛔ Not docs/adr/** (docs(adr): ADR-0131 dated notes for the 18.x line — release scope, the D9 lean-embedding exception, D1 at createTable, D2 deployment-wide declared rules (Tier H) #24897).
    • ⛔ No ORGANIZATION_OWNERSHIP_SHAPE_GENERATION change. It stays 1, and any option that bumps it is a decision for the maintainer.
      Container & model: M, mode:cloud, model: TIER_DEFAULT
      Clause-②: no — a measurement round with no diff
      Responsibility: a store 18.0.0 created, and a ceremonied store, are certified while some tenant columns stay nullable | none | ADR-0131 D1's promise. No reader treats a NULL organization as meaningful on HEAD.
      Thread-read: 6297109158
      Serial constraints cleared: step 7 is closed, and 7b-0 and 7b-1+7b-2 are on main.
  4. objectstack-fleet commented on Oct 21, 2026

    @objectstack-fleet
    ContributorAuthor

    os-dev-report · measure round for claim 6323364911 · session_01F4TVnTQLXTmLgduMbWUUV1 · 2026-10-21T18:33Z

    Tree: origin/main d5d8146 (carries 7b-0 #24940 and 7b-1+7b-2 #24893). Nothing pushed, no branch, no PR. ORGANIZATION_OWNERSHIP_SHAPE_GENERATION was not touched (packages/spec/src/system/migration.zod.ts:215, still 1).

    Method:

    • Every store is a SQLite file. Each was booted with the upgrade harness's own composition (bootFixture: showcase, the audit plugin and the boot guard). Each ceremony is the real os migrate organization-ownership binary: the plan, then --apply --plan-file … --backup-taken, through the harness's runOwnershipCeremony.
    • Every table was counted with pragma_table_info (notnull on organization_id) and COUNT(*) WHERE organization_id IS NULL, after each boot and each ceremony.
    • The probes were throwaway scripts in the session's scratchpad. Nothing was written in the repository.

    Two things about posture you need to read the tables right:

    • In open code, a served boot with OS_TENANCY_POSTURE=group|isolated and no org-scoping runtime resolves to effective single (its log says "posture 'single' — Layer 0 is inert"). So the walled rows below come from a second run with @objectstack/verify's multiTenant: 'posture-only' stand-in. That run's tenancy service answered group / isolated at every boot. The ceremony reads the posture from the environment and reported posture: group|isolated itself.
    • (c) is fixture-18.0 (an 18.0 pre-release store with no ownership marker: the v17 refusal class, refused at first boot naming sys_flow_credential, sys_secret). Under group and isolated its ceremony first blocked at declared-webhooks: one customized copy of the declared showcase_task_changed webhook is an operator decision under a wall. I renamed the copy, as the blocker prescribes, and re-ran. Both runs then answered complete: true, blocking: 0.

    1. Nullable tenant tables per store and posture

    The counts are the same under all three postures, in every store. That is ADR-0131 D11, now measured.

    store posture tables with organization_id nullable NULL rows
    raw fixture-18.0.0 (before any HEAD boot) — 65 65 0
    (a) 18.0.0-born, booted on HEAD, no ceremony single 66 65 0
    group 66 65 0
    isolated 66 65 0
    (b) = (a), then HEAD's voluntary ceremony single 66 4 0
    group 66 4 0
    isolated 66 4 0
    (c) v17-class (fixture-18.0), then the ceremony single 66 4 0
    group 66 4 0
    isolated 66 4 0
    (d) fresh HEAD store single 64 1 0
    group 64 1 0
    isolated 64 1 0
    • (a): the 66th table is sys_activity__r20261021, the shard HEAD's boot rotated in. 7b-0's ensureShardTable created it NOT NULL. All 65 tables 18.0.0 created stay nullable. Reason: certified at birth. The guard boots a verified marker at generation 1 (organization-ownership-boot-guard.ts:545), and no step ever tightens such a store.
    • (b) and (c): the ceremony's not-null step reported due 63, applied 61, unsupported 2, notDeclared 0 in all six runs. The 4 tables left nullable:
    table reason (card's vocabulary) where it is decided
    sys_activity__r20261020 in (b), sys_activity__r20261019 in (c) rotated shard, created before 7b-0 organization-ownership-not-null.ts:41-48
    sys_email_template mirror-deletion fate organization-ownership-plan.ts:1098-1101; inventory :428
    sys_sso_provider tenancy-disabled (notNullExempt: better-auth's provider relation, not the tenancy anchor) inventory :297-305; object sys-sso-provider.object.ts:59
    sys_notification_subscription not-declared: the object was retired, and its table is left in place by ruling (notNullExempt) inventory :479
    • (d): only sys_sso_provider, which is an opt-out. That meets the expectation of 0 apart from opt-outs.

    Unsupported on the Turso remote face. Not measured at runtime: no hosted remote was reached. By code, driftApplyLimit('tighten_not_null') answers the table-rebuild limit on the remote face (driver-turso/src/turso-driver.ts:4669-4672). So on a Turso-remote store, the ceremony leaves every pre-existing tenant table nullable, reported unsupported.

    What changed since 6297109158. That measurement left 3 tables nullable; HEAD leaves 4. The fourth is sys_notification_subscription. #25024 (63085c3) retired it after the tree that measurement used (7169612), so the plan now exempts it (git merge-base --is-ancestor says 63085c3 is not in 7169612).

    The ceremony mis-reports the new shard. It lists the already-NOT-NULL sys_activity__r20261021 as unsupported … each new shard is created with sys_activity's nullable column. That text predates 7b-0 and is wrong since 7b-0 (organization-ownership-not-null.ts:31 and :46). It is listed below as a finding.

    2. NULL rows today

    • What the fixtures hold:
      • fixture-18.0.0: 0 NULL rows on all 65 tables, before and after HEAD's boot and after the ceremony.
      • fixture-18.0: 0 on all 67 tables, raw.
      • Every store in the table above: 0.
    • Where 7b-3's rows came from (6284717698). They came from a fresh install served by production os serve without OS_AUTH_SECRET. The published 18.0.0 already refuses that door: serve.ts at 06cab48 (the 18.0.0 tag's commit) carries the no-secret refusal, and HEAD has it at serve.ts:4688. A store 18.0.0 served normally therefore holds none from that path.
    • One writer is still LIVE on HEAD, measured: production os serve --no-server with no OS_AUTH_SECRET.
      • Why it gets through:
        • Both of serve's refusals are gated on flags.server (serve.ts:4666, :4688).
        • AuthPlugin is not composed, so no sys_organization is registered.
        • The engine takes no-organization-object and passes the row on with no organization (objectql/src/engine.ts:7785; decision at core/src/security/system-write-organization.ts:115-125).
      • On a copy of store (a), with one seeded showcase_category row deleted, the run re-seeded it as Engineering with organization_id NULL (1 NULL row afterwards).
      • Two controls:
        • Store (a) without the deletion: 0 rows changed, because seed replay finds its rows.
        • Fresh store (d): 240 [SeedLoader] ERROR lines and 0 rows. The NOT NULL column refuses each insert.
      • Where it can land: an 18.0.0-born store; any table of a ceremonied store the ceremony left nullable; any store on MongoDB, which has no NOT NULL.
    • Every other writer is guarded. A read-only census of the write paths on HEAD found nothing else:
      • shard rotation creates NOT NULL (driver-sql/src/sql-driver.ts:12204-12230);
      • the lifecycle archiver copies the hot row's value (objectql/src/lifecycle/lifecycle-service.ts:1502);
      • seed replay with the organization object registered stamps the sole organization or refuses (metadata-protocol/src/seed-loader.ts:2096, :2109);
      • the outbox, email log, better-auth adapter, sharing, audit and automation all write through the engine;
      • every os migrate * boot runs with skipSeedData: true (cli/src/utils/schema-migrate.ts:1312).
    • The residual tables have no NULL writer:
      • sys_email_template: the organization door is closed (plugin-email/src/email-template-door.ts), and the plugin no longer writes the table.
      • sys_notification_subscription: retired, no reader or writer.
      • old shards: writes go to the current shard only.
      • sys_sso_provider: a NULL there is a legitimate better-auth row by design.
    • What the ceremony does with a NULL row, posture by posture (measured, plan only). I used the store with the --no-server NULL row.
      • Under single: attribution {default-organization: 1}, willReceive: true.
      • Under isolated: 1 row(s) whose owner cannot be derived stay NULL and are reported, willReceive: false. That table would stay nullable.

    3. Options for an already-certified store

    option what it covers what it leaves cost risk ADR-0131 D1 text it makes true
    (i) boot-time tighten each table with 0 NULL rows, at every boot of a certified store shards and the Turso remote face (no apply door); every table with a NULL row contradicts D10 "never an automatic boot step" (ADR-0131 line 621). tighten_not_null is destructive, and the boot reconcile applies safe only (sql-driver.ts:14425, :14479). On SQLite it rebuilds each table (61 on this fixture). On PostgreSQL it is SET NOT NULL (sql-driver.ts:14705): a full scan under an exclusive lock. A NULL row blocks it. It has two choices: skip the table (residue nobody is told about) or refuse the boot (a generation bump under another name, hitting exactly the store that holds the --no-server row) high: unattended destructive DDL and lock time at boot none new. D1 still does not hold "anywhere"
    (ii) the voluntary ceremony already shipped, plus a way to learn about it measured: 61 of the 63 due tables per store, in every posture. Unattributable rows get reported, not guessed old shards (expire in at most 14 days on SQLite: plugin-audit/src/objects/sys-activity.object.ts:29-34); sys_email_template; retired tables; opt-outs; every table on the Turso remote face; tables holding unattributable NULL rows under a wall today an operator has no way to learn they should run it. The boot prints nothing for a birth-certified store (the :545-552 line fires only for retired columns), and os migrate plan stays silent by design (ceremonyOwnedNullability, driver-sql/src/schema-drift.ts:949, :974). The cost is one advisory: a boot info line, or an os migrate plan note listing the nullable tenant columns, plus a release-note sentence. Running it rewrites the birth marker as a checkpoint at generation 1 (measured) low: no refusal; the operator chooses when "NOT NULL on every table the driver creates and on every table the ceremony has cleared; an 18.0.0-born store is certified nullable until its operator runs the ceremony"
    (iii) generation bump to 2 (not built) forces (ii) on every store the same 4 residual tables (the ceremony's not-null step does not change), unless the ceremony changes too refuses every existing 18.x store at its next boot: every 18.0.0-born store (birth generation 1) and every store ceremonied at generation 1, which means (b) and (c) above too. The refusal is organization-ownership-boot-guard.ts:284-291: "This database was certified by an organization-ownership ceremony of shape generation 1, and this release requires generation 2: the ceremony steps added since have not run on it. The server refuses to start rather than serve it in a shape it was never migrated to. Run 'os migrate organization-ownership --out PLAN' …" The 18.x line kept the generation at 1 to avoid exactly this high: downtime on every install; a maintainer decision "every certified store has run the NOT NULL step", still minus the 4 residuals
    (iv) document the residue as accepted — (a): all 65 tables; (b)/(c): the 4 tables in section 1; Turso remote: every pre-existing table a D1 amendment (Tier H, #24897) low, as long as the --no-server writer is closed the wording below

    The 4 residual tables: does any need more than documentation?

    • Old sys_activity__r* shards: documentation only. HEAD creates new shards NOT NULL, writes go only to the current shard, and the pre-7b-0 shards expire within the declared 14 days on SQLite. Rotation is SQLite-only; on other dialects sys_activity is the base table, and the ceremony tightens it.
    • sys_email_template: documentation only. Its writer is closed, its mirrors are deleted by the ceremony (0 rows in these fixtures), and D13 retires the table.
    • sys_notification_subscription: documentation only. The object is retired, and its table is left in place untouched by ruling. No reader, no writer.
    • sys_sso_provider: documentation only, but D1's wording needs care. It is not a tenant column. It is better-auth's provider relation on a tenancy.enabled: false object, nullable even on a fresh store. Today D1 says an object with no organization "has no column". This object keeps a column named organization_id. D1 should say the rule binds the tenant column, not a column of that name.

    Recommendation: (ii) + (iv), and close the --no-server writer first.

    • (iii) refuses every 18.x install and still leaves the same residue. (i) is an unattended destructive boot step that either hides residue or turns into (iii).
    • (ii) already does the work on an 18.0.0-born store (61 of 63 due, in every posture). What it lacks is a way for the operator to learn about it: one advisory line.
    • What is left after the ceremony is self-expiring, retiring, or not a tenant column.
    • The one live NULL writer (os serve --no-server, no secret) is what would make the documented residue untrue on an 18.0.0-born store. It is ADR-0131 C8's open question, and 7b-3's option A (6284717698) covers it if extended to --no-server boots that seed.

    D1 wording option (iv) would make true, given (ii):

    Every object whose tenant column is organization_id carries it NOT NULL at the engine from the start (D9), and at the DDL on every table the driver creates and on every table the D10 ceremony has cleared. A database the published 18.0.0 created is certified at birth with its tenant columns nullable; os migrate organization-ownership, which it does not require, tightens them. The ceremony leaves nullable only: an activity shard rotated before the driver created shards NOT NULL (it expires with its retention), a table whose object is retired and left in place by ruling, sys_email_template until its D13 retirement, a table still holding a reported NULL row, and every table on a datasource whose transport cannot rebuild a table (the Turso remote face). A column named organization_id on an object with tenancy disabled (sys_sso_provider) is not a tenant column.

    {
      "issue": 24919,
      "status": "done",
      "part": "measure round (claim 6323364911)",
      "branch": null,
      "head": "d5d8146012 (origin/main), unmodified",
      "pr": null,
      "session": "session_01F4TVnTQLXTmLgduMbWUUV1",
      "premise_still_valid": true,
      "summary": "Tenant columns, all three postures alike: store (a) 18.0.0-born, booted on HEAD, has 65 of 66 nullable; (b) after the voluntary ceremony and (c) a v17-class store after the ceremony each have 4 of 66; (d) a fresh HEAD store has 1 of 64 (sys_sso_provider, an opt-out). Every store holds 0 NULL rows. One live NULL writer exists: production `os serve --no-server` without OS_AUTH_SECRET re-seeded a missing row with organization_id NULL on an 18.0.0-born store. Recommendation: (ii)+(iv), and close that writer.",
      "measurements": {
        "a_18.0.0_born_booted_on_HEAD": "66 tables carry organization_id, 65 nullable, 0 NULL rows; sys_activity__r20261021 (rotated in by HEAD) is NOT NULL; same in single/group/isolated",
        "b_a_plus_voluntary_ceremony": "complete true, blocking 0; not-null due 63 / applied 61 / unsupported 2; 4 nullable (old shard, sys_email_template, sys_sso_provider, sys_notification_subscription); 0 NULL rows; same in all postures",
        "c_v17_class_fixture_18.0_ceremonied": "the first boot is refused (absent marker); the ceremony completes in single; in group/isolated it blocks at declared-webhooks until the customized webhook copy is renamed, then completes; 4 nullable, 0 NULL rows",
        "d_fresh_HEAD": "64 tables, 1 nullable (sys_sso_provider), 0 NULL rows, all postures",
        "turso_remote_face": "not measured at runtime (no hosted remote); by code, every pre-existing tenant table is unsupported (turso-driver.ts:4669-4672)",
        "null_writer_os_serve_no_server_no_secret": "live: a seeded row deleted on a copy of (a) is re-seeded with organization_id NULL; on fresh (d), 240 seed inserts are refused at NOT NULL and 0 rows are written",
        "ceremony_on_a_null_row": "single: attributed to the Default Organization, then tightened; isolated: reported, the table stays nullable"
      },
      "tests": "No code change, so no suite run. Build: pnpm install --frozen-lockfile, then turbo build of the @objectstack/dogfood and @objectstack/cli closures (66/66). The probes are scratchpad tsx scripts calling the harness's own bootFixture / runOwnershipCeremony and the real CLI binary. 3 production `os serve --no-server` runs, each stopped by its recorded PID.",
      "gates": "None. There is no diff.",
      "mcp_calls": "reads only",
      "api_writes": "1: this comment, through post-stamped",
      "open_questions": [
        {
          "question": "How does ADR-0131 D1 become true for stores that are already certified?",
          "options": ["(i) boot-time tighten", "(ii) voluntary ceremony + an advisory telling the operator", "(iii) generation bump to 2", "(iv) document the residue (D1 wording above)"],
          "recommendation": "(ii)+(iv), after the `--no-server` NULL writer is closed (ADR-0131 C8; 7b-3 option A extended to --no-server boots that seed)"
        }
      ],
      "out_of_scope_findings": [
        "class: b · the `os serve --no-server` production boot with no OS_AUTH_SECRET writes organization_id NULL on nullable tenant columns (18.0.0-born stores, ceremony residue, MongoDB) · serve.ts:4666/:4688 are gated on flags.server; engine.ts:7785 · dedupe words: no-server, no-organization-object, AuthPlugin skipped, seed NULL organization",
        "class: c · the ceremony's not-null note says each new shard is created nullable, which is untrue since 7b-0, and it reports an already NOT NULL shard as unsupported · organization-ownership-not-null.ts:31, :46 · dedupe words: rotation shard nullable note stale"
      ]
    }

    Generated by Claude Code

  5. objectstack-fleet commented on Oct 21, 2026

    @objectstack-fleet
    ContributorAuthor

    Seat note · epic PM session_01Y5583bs2RDd53MH6kM1ca3 (os-steve) · 2026-10-21T18:39Z. The measurement round is done (6323585273), and it ends in two decisions for the maintainer:

    #25134's option A holds only once #25133 is fixed. The ceremony's stale shard note (organization-ownership-not-null.ts:31, :46) rides #25134's execution. This card stays open until both are ruled and executed.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment

Metadata

Metadata

Assignees

Type

No type

Projects

No projects

    Milestone

    No milestone

    Relationships

    None yet

    Development

    No branches or pull requests

    Issue actions