docs(specs): drive S22 reconciliation selection off namespace_statistics

What does this MR do?

Two things, both documentation, split out of the Step 1 implementation MR (!1422 (merged)) so the schema and query decisions review without the code. Both target main independently; !1422 (merged) carries only code.

1. The reconciliation index served neither query asked of it

Review of handbook!20738 pointed out that the (last_reconciled_at) index cannot be used by the candidate-selection query this spec specifies. Measured on PostgreSQL 18, 50,000 namespaces, 500 stale, five with no namespace_statistics row:

Query	Plan	Buffers	Time
Selection as specified (namespaces LEFT JOIN, IS NULL, ORDER BY n.id)	Merge left join over both primary keys, staleness as a post-join Filter	34,308	17.95 ms
Same, index dropped	identical plan, node for node	11,458	6.08 ms
Selection reading namespace_statistics alone	Index Scan	4	0.07 ms
reconciliation_backlog as specified (stale-or-missing, joined)	2× Seq Scan + Hash Join	1,456	15.04 ms
Same count over namespace_statistics alone	Index Only Scan, Heap Fetches: 0	3	0.09 ms

Two structural reasons, not a planner accident: the IS NULL disjunct the missing-row arm needs cannot become an index condition, and ORDER BY n.id cannot be supplied by an index led by a timestamp. The cost also runs backwards — the rarer stale rows are, the more of the table each page walks — so the query is most expensive exactly when reconciliation is healthy, which is the state the alert exists to confirm.

Both consumers now read namespace_statistics alone, and the selection pages keyset-wise over (last_reconciled_at, namespace_id).

The second index column is a correctness requirement, not tuning. Until a row's first pass it holds the 'epoch' default, so the timestamp alone is not a unique sort key and a cursor over it cannot advance through the tie. On that data the single-column index plans each page as a sequential scan plus a top-N sort of the whole table (423 buffers, 7.14 ms per page) against 99 buffers and 0.37 ms for the composite.

The missing-row case does not disappear — it moves to an orphan sweep on reconciliation_orphan_sweep_interval (default 6h), folded into Step 15 because it is the same walk-and-enqueue shape against the same task. It carries its own gauge, namespaces_missing_statistics, rather than folding into the backlog count: that would put an anti-join on the scrape path and hide a broken schema invariant inside a number expected to be non-zero. Without the sweep, a namespace whose trigger never fired would never be reconciled at all — in a billing input.

2. The schema corrections moved out of !1422 (merged)

Unchanged in substance, listed so they are not read as new:

• ON DELETE CASCADE on the namespace_id foreign key, which the AFTER INSERT trigger forces.
• Primary key on (namespace_id) rather than "unique index on (namespace_id)".
• Unpartitioned under ADR-007's structural exception rather than the row-count argument the Partitioning invariant rejects by name.
• last_reconciled_at defaults to 'epoch', not '-infinity': no time.Time holds an infinite timestamp, so the generated model cannot scan a row still carrying the default — every row until its first pass.
• The drain section's pgx claim corrected: pgx refuses the scan, not the encode; a zero time.Time encodes silently as year 1, which is the failure that survives the default change.
• docs/dev/database.md: the two NOT NULL cases separated, and the partitioning exception scoped to what ADR-007 defines including its no-inbound-foreign-keys half.

3. The trigger-and-seed rationale, corrected

Review of !1422 (merged) established that the spec and plan were wrong to say the namespace_statistics trigger and seed cannot be split across two migrations. The seed is INSERT … ON CONFLICT (namespace_id) DO NOTHING, so table-plus-trigger followed by a backfill loses no namespace, and a row missed anyway is healed by reconciliation's write-back and found by the orphan sweep above.

The Approach section now separates the two migrations that mix schema with data instead of granting both the same justification: forced for the shadow table, where nothing heals a blob row missed between the seed's snapshot and trigger creation because reconciliation treats the shadow as authoritative; a preference for namespace_statistics, where one file simply keeps any intermediate state unobservable. What is load-bearing in both is the order, trigger before seed.

Merge order

Read this MR before !1422 (merged) — it carries the decisions that MR implements — but neither blocks the other in git: both target main, and !1422 (merged)'s diff no longer touches any document.

handbook!20738 carries the ADR side. It must merge and sync before !1422 (merged) ships an unpartitioned namespace_statistics on main, since until then the synced docs/adr/007_database_schema.md still states an invariant that table contradicts.

Related to #265 (closed)