Reconcile TK-11364 partition state and document remaining approval gate

36033811ac0bf35c6ee62eca4b9837d138313e2d · 2026-09-10 17:59:07 -0700 · Steve Abrams
Files touched

A ops/TK-11364-validationevent-approval.md
A verification/e2e-proof.json
Diff

commit 36033811ac0bf35c6ee62eca4b9837d138313e2d
Author: Steve Abrams <steve@designerwallcoverings.com>
Date:   Thu Sep 10 17:59:07 2026 -0700

    Reconcile TK-11364 partition state and document remaining approval gate
---
 ops/TK-11364-validationevent-approval.md | 90 ++++++++++++++++++++++++++++++++
 verification/e2e-proof.json              | 31 +++++++++++
 2 files changed, 121 insertions(+)

diff --git a/ops/TK-11364-validationevent-approval.md b/ops/TK-11364-validationevent-approval.md
new file mode 100644
index 00000000..1f66582d
--- /dev/null
+++ b/ops/TK-11364-validationevent-approval.md
@@ -0,0 +1,90 @@
+# TK-11364 — remaining ValidationEvent conversion
+
+Status: **HOLD — production DDL gated; existing script requires revision.**
+Owner: `codex-run-11364`. Verified 2026-09-11 00:56:36 UTC
+(2026-09-10 5:56 PM America/Los_Angeles).
+
+## Current state, independently verified
+
+- SourceEvidence is already a native RANGE(createdAt) partitioned table:
+  legacy before 2026-10-01, default, and 15 monthly partitions through
+  December 2027. All 17 children have effective SELECT and INSERT privileges
+  for the app role. Do not rerun its conversion.
+- ValidationEvent is still an ordinary table, 14 GB, with zero partitions.
+  Its runAt column is timestamp(3) without time zone, NOT NULL.
+- ValidationEvent has PRIMARY KEY(id) and a validated stagedRecordId FK
+  with ON UPDATE CASCADE ON DELETE RESTRICT. No incoming FK appeared in
+  the catalog query.
+- pg_partman is absent. The newer TK-11363 native partition approach has
+  superseded the original extension/rewrite proposal; installing pg_partman
+  is not part of this proposed action.
+- Root filesystem has 121 GiB available, 82% used. The earlier 44 GiB / 75 GiB
+  disk blocker no longer describes current state.
+- Default table privileges for postgres include homesonspec=arwdDxt.
+  No database sessions were waiting on locks at the measurement time.
+- A real published home page returned HTTP 200:
+  https://homesonspec.com/homes/cms52dly81wntdzqsqppje24n
+
+These are current-state checks, not a fresh proof of the historical conversion's
+row preservation, write routing, or archive behavior. This session made no
+production mutations. TK-11363 records the prior conversion and its verification.
+
+## Proposed gated action
+
+In an explicitly approved maintenance window, convert only ValidationEvent to
+native monthly RANGE(runAt) partitions using the no-rewrite legacy ATTACH
+approach. Preserve every existing row and its heap, the stagedRecordId index,
+the existing FK actions, and app permissions. Provide a default partition and
+forward months; never install automatic DROP retention. Archival is a separately
+approved DETACH operation with retained history and a retrieval plan.
+
+The existing candidate is `ops/o3b-apply-validationevent.sh` (introduced in
+commit 3e4d499f). TK-11363 reports a copy at `/root/o3b.sh`, but this session
+did not verify that remote copy. **Do not execute that staged copy as-is.**
+
+## Required before an execution request
+
+1. Correct and locally rehearse the candidate's error handling. It currently
+   suppresses all forward-partition FK creation errors with `|| true`.
+   The negative FK probe runs an expected-failing psql command under
+   `set -euo pipefail`, so the script can exit before its app-role query
+   and final partition verification. Catch and assert SQLSTATE 23503 in
+   a rolled-back test; reject unexpected outcomes without leaving a probe row.
+2. Preserve FK semantics explicitly. Forward/default FKs currently omit
+   ON UPDATE CASCADE ON DELETE RESTRICT, unlike the live legacy FK.
+   Verify validated FK definitions and effective privileges on every child.
+3. Bound DDL work, not just lock acquisition. The swap currently sets only
+   lock_timeout; a statement_timeout is also needed to bound unexpected work
+   after acquiring locks. Rehearse transactional failure and retry behavior.
+4. Assert rather than merely print the expected partition count, default
+   contents, successful app-role queries, and HTTP results. A SQL query with
+   the admin page's shape does not prove the authenticated admin page works.
+5. Validate a current backup's contents, including the full ValidationEvent
+   data block, and rehearse a rollback that retains rows written to forward
+   and default partitions after cutover. Age and filename alone are insufficient.
+6. Recheck catalog topology, oldest/newest runAt values, grants, backup, traffic,
+   and resource headroom immediately before the window. The fixed October 2026
+   cutoff must be reconsidered if execution is deferred beyond that boundary.
+
+## Acceptance and stop conditions
+
+Before/after evidence must prove row preservation under a defined writer window,
+unchanged legacy heap identity, monthly insert routing, FK rejection and original
+FK actions, app-role read/write access, partition pruning, real authenticated
+admin validation flow, public home HTTP success, and recovery after an interrupted
+attempt. Rehearse DETACH/re-ATTACH in scratch with all history retained.
+
+The prior SourceEvidence conversion caused approximately seven minutes of blocked
+queries and a public 500, per TK-11363. Its revised design must be checked at the
+application boundary as well as the database boundary before O3b is approved.
+
+Production conversion is not authorized by the current request: Steve explicitly
+instructed this session to draft gated actions to pending-approval and stop.
+TK-11363 also records three prior automatic-approval denials of the O3b run;
+their exact classifier rationale is not present in that ticket. This session
+has not retried that production mutation or attempted another command shape.
+
+TK-11364 remains blocked and must not be marked done until ValidationEvent is
+converted and the full acceptance journey passes. Disk space is not the current
+blocker. The next safe work is local candidate revision/rehearsal; production
+execution additionally requires explicit maintenance-window authorization.
diff --git a/verification/e2e-proof.json b/verification/e2e-proof.json
new file mode 100644
index 00000000..0e9a7812
--- /dev/null
+++ b/verification/e2e-proof.json
@@ -0,0 +1,31 @@
+{
+  "ticket": "TK-11364-homesonspec-o3-pg-partman-time-partition",
+  "agent": "codex-run-11364",
+  "intent": "Reconcile remaining O3 scope against live state and prepare the required production approval handoff",
+  "risk_tier": "R0 documentation with read-only production verification; proposed conversion is R4 and unexecuted",
+  "environment": "Local homesonspec checkout; read-only PostgreSQL catalog on kamatera; public home GET",
+  "baseline_commit": "0200179cc46d997fbb241c4cd1d79a7c1e37857d",
+  "timestamp": "2026-09-11T00:56:36Z",
+  "baseline": "Working tree clean. Ticket opening predates the TK-11363 SourceEvidence conversion. ValidationEvent conversion previously denied and unexecuted.",
+  "commands_executed": [
+    "TK_AGENT=codex-run-11364 tk inbox; tk show TK-11364-homesonspec-o3-pg-partman-time-partition; tk take TK-11364-homesonspec-o3-pg-partman-time-partition",
+    "tk show TK-11363; read ops/o3b-apply-validationevent.sh and O3 runbooks; inspect ValidationEvent schema and application call sites",
+    "ssh kamatera: date -u; df -h /; runuser -u postgres -- psql -X -v ON_ERROR_STOP=1 -d homesonspec; BEGIN READ ONLY; SET LOCAL statement_timeout='15s'; catalog SELECTs; COMMIT",
+    "curl -fsS --max-time 25 -o /tmp/TK-11364-home.html -w '%{http_code}\\n' https://homesonspec.com/homes/cms52dly81wntdzqsqppje24n",
+    "bash -n ops/o3b-apply-validationevent.sh"
+  ],
+  "checks": [
+    {"boundary":"database catalog", "verdict":"PASS", "observed":"SourceEvidence relkind=p, RANGE(createdAt), 17 children: legacy/default/15 monthly through December 2027"},
+    {"boundary":"database authorization", "verdict":"PASS", "observed":"All 17 SourceEvidence children have effective app SELECT and INSERT; postgres default table ACL grants homesonspec=arwdDxt"},
+    {"boundary":"remaining conversion", "verdict":"FAIL", "observed":"ValidationEvent relkind=r, 14 GB, zero partitions. Ticket outcome is incomplete"},
+    {"boundary":"disk and locks", "verdict":"PASS", "observed":"121 GiB free, 82% root usage; zero homesonspec sessions waiting on locks at measurement"},
+    {"boundary":"public HTTP", "verdict":"PASS", "observed":"Published /homes/cms52dly81wntdzqsqppje24n returned 200"},
+    {"boundary":"script syntax", "verdict":"PASS", "observed":"bash -n exit 0; this does not establish behavioral correctness"},
+    {"boundary":"candidate safety review", "verdict":"FAIL", "observed":"FK creation errors suppressed; expected FK-error probe can terminate script early; new FKs omit legacy CASCADE/RESTRICT actions; default count is printed rather than asserted"},
+    {"boundary":"production migration and persisted data", "verdict":"SKIP", "reason":"Explicit production gate; no DDL or probe writes performed"},
+    {"boundary":"negative FK, retry, rollback, authenticated admin flow", "verdict":"SKIP", "reason":"No new execution or scratch rehearsal in this session; required before migration success can be claimed"}
+  ],
+  "artifacts": ["ops/TK-11364-validationevent-approval.md"],
+  "cleanup": "No production rows, schemas, services, schedules or permissions changed. Local report and approval draft intentionally retained. Public page response retained in /tmp/TK-11364-home.html.",
+  "verdict": "BLOCKED — approval handoff prepared; production conversion and critical acceptance checks remain incomplete"
+}
← 0200179c TK-11303: retire stale v1 apple-review launchd plist (supers · back to Homesonspec · Rehearse and harden ValidationEvent partition conversion 90b58b81 →