dot-iu-cutter v0.5 — Index-Only DDL Design for Runtime Paths

Date: 2026-05-17 · Status: DESIGN / PROPOSAL ONLY — NO CREATE INDEX executed, NO DDL authorized. Index DDL remains forbidden without its own dry-run + command-review + sovereign prompt. Parent: v0.5 pre-scale foundation.

1. Design constraints (mandated)

• Additive indexes only. No table rewrite, no column add, no semantic migration, no data rewrite, no constraint change.
• Online build: every proposed index uses CREATE INDEX CONCURRENTLY (no ACCESS EXCLUSIVE table-rewrite; brief locks only). IF NOT EXISTS for idempotency.
• BTREE default; partial index only where structurally justified; no GiN by default (see §3).
• Names explicit/auditable; schema-qualified cutter_governance.*. No data touched.

2. Proposed additive index set (PROPOSAL — illustrative, NOT for execution)

#	Hot path	Proposed index (CONCURRENTLY, additive)	Type / justification
2	SWEEP	idx_dbe_status_emitted_keyset ON decision_backlog_entry (status, emitted_at, entry_id)	BTREE composite — serves the WHERE status=… filter and the deterministic (emitted_at, entry_id) keyset order in one index (index-only ordered scan; removes seq-scan+sort)
3	Lineage (envelope)	idx_me_source_doc_ref ON manifest_envelope (source_doc_ref)	BTREE equality
4	Lineage (review live-tail)	idx_rd_manifest_live ON review_decision (manifest_id) WHERE superseded_by_review_decision_id IS NULL	Partial BTREE — justified: phases selects only the non-superseded tail; partial keeps the index small and matches the exact predicate
5	Cut-once guard	idx_ccs_dbe_id ON cut_change_set (decision_backlog_entry_id) WHERE decision_backlog_entry_id IS NOT NULL	Partial BTREE — justified: column is nullable and G-CUT-ONCE only ever probes non-null values
6	Verify lookup	idx_vr_change_set_id ON verify_result (change_set_id)	BTREE equality
7	DOT signature xref	idx_dps_xref_cs ON dot_pair_signature (cross_reference_change_set_id) WHERE cross_reference_change_set_id IS NOT NULL and idx_dps_xref_vr ON dot_pair_signature (cross_reference_verify_result_id) WHERE cross_reference_verify_result_id IS NOT NULL	Partial BTREE — justified by the deployed XOR (exactly one xref non-null per row); two small partial indexes beat one mixed index
9	Dependency guard	idx_dbd_from_entry ON decision_backlog_dependency (from_entry_id)	BTREE equality (additive; 0 rows today, future-proof)

Optional (audit-scale, lower priority, OD): idx_dbh_entry ON decision_backlog_history (entry_id) (history is append-only; only needed if audit queries by entry_id at scale).

3. Explicitly NOT proposed

• No GiN/JSONB index. Runtime never filters on a JSONB key on a hot path; the design mandates promoting a hot key to a scalar column rather than scanning JSONB at scale (see …-extensible-information-unit-metadata-strategy-…). Adding GiN now would index data we deliberately do not query at scale → unjustified maintenance cost.
• No covering/INCLUDE indexes (premature; revisit only if EXPLAIN on dry-run-at-volume shows heap-fetch dominance).
• No unique indexes beyond those already deployed (would be a constraint/semantic change, not additive-neutral).
• No reindex/cluster/vacuum-full (table rewrite).

4. Verification model (for the future, separately-authorized index cycle)

The index-DDL execution (its own cycle) would: structural pre/post catalog check (pg_indexes/pg_index), EXPLAIN (ANALYZE off) on the runtime predicates before/after in an isolated dry-run env at volume to prove seq-scan→index-scan, additive-only assertion (object count +K, zero column/constraint/data delta), rollback = DROP INDEX CONCURRENTLY by exact name (additive ⇒ safe inverse). Structural comparison, never pg_get_*def string-equality (prior false-negative lesson).

5. Open decisions for GPT

• OD-I1 Approve the §2 set / adjust (e.g. partial vs full on #4/#5/#7).
• OD-I2 Include the optional decision_backlog_history(entry_id) now or defer.
• OD-I3 Sequencing: index DDL before the volume dry-run (so the dry-run measures the indexed path) vs measure-unindexed-then-indexed for evidence.
• OD-I4 Confirm CREATE INDEX CONCURRENTLY acceptable on the production directus DB (no long lock; cannot run in a txn — handled by its own cycle).

Boundaries / Git

Proposal/design only — no DDL, no write, no code/commit. Git main · e93424b5ff7fa5e4b8406131977ce4339cd0856a · clean (0 lines). No hardcoding; additive-only; SQL=SSOT; no vector/NoSQL. Next = GPT review; index DDL stays forbidden until a separate authorized cycle.