04 — PRIMITIVE P2: dot-staging-sandbox-drop

Status: STAGED (authored, not deployed/registered/executed). DOT code (staging-lite): DOT-STG-SANDBOX-DROP Purpose: Retire/drop a sandbox by id, governed — the rollback/retire facet of the lane. No blind drops: it refuses any DB not registered as a sandbox.

8-facet lifecycle

1. reuse-first — verify the sandbox exists in its own sbx_meta.sandbox_registry; refuse an unknown id (no blind DROP DATABASE). Refuse if registry status is retired already (idempotent no-op).
2. birth / khai sinh — records a retirement event linked to the original birth (tombstone).
3. governance / admission — auto staging admission; requires the sandbox be a registered, non-pinned c1_staging_% DB; fail-closed otherwise.
4. registration / staging-registry — sets sandbox_registry.status='retired', retired_at=now() (captured to KB/JSONL tombstone before the DB is dropped, since the in-DB registry dies with it).
5. DOT manage ledger — appends op=sandbox_drop to the durable JSONL/KB ledger.
6. rollback / retire / drop — terminates connections then DROP DATABASE … WITH (FORCE).
7. readback — confirms the DB is gone (not in pg_database).
8. orphan check — scans for (a) any c1_staging_% DB with no registry/birth (orphan DB) and (b) any durable-ledger sandbox marked active whose DB is gone (orphan record); reports both.

Runner (bash)

#!/usr/bin/env bash
# DOT-STG-SANDBOX-DROP (staging-lite, DOT-100 governed)
set -euo pipefail
SBX=""; while [ $# -gt 0 ]; do case "$1" in --sandbox-id) SBX="$2"; shift 2;; *) echo "unknown $1">&2; exit 2;; esac; done
[ -n "$SBX" ] || { echo "ADMISSION_DENIED: --sandbox-id required"; exit 3; }
case "$SBX" in c1_staging_*) :;; *) echo "REFUSE: '${SBX}' is not a c1_staging_* sandbox"; exit 4;; esac
PSQL(){ docker exec postgres sh -lc 'psql -U "${POSTGRES_USER:-postgres}" '"$*"; }

# facet 1+3: must be a registered, live sandbox
REG="$(PSQL -tAc "\"select 1 from pg_database where datname='${SBX}'\"" </dev/null | tr -d '[:space:]')"
[ "$REG" = "1" ] || { echo "NO_OP: ${SBX} not present (already retired?)"; exit 0; }
HASREG="$(PSQL -d "$SBX" -tAc "\"select 1 from sbx_meta.sandbox_registry where sandbox_id='${SBX}' and status='active'\"" </dev/null | tr -d '[:space:]' || true)"
[ "$HASREG" = "1" ] || { echo "REFUSE: ${SBX} has no active sbx_meta registry row (orphan? inspect, do not blind-drop)"; exit 5; }

# facet 2+4+5: capture tombstone BEFORE drop (durable JSONL/KB)
mkdir -p /opt/incomex/staging/c1
PSQL -d "$SBX" -tAc "\"select row_to_json(b) from sbx_meta.birth_certificate b where sandbox_id='${SBX}'\"" </dev/null \
  | sed 's/^/TOMBSTONE_BIRTH: /' | tee -a /opt/incomex/staging/c1/_tombstones.jsonl
echo "{\"op\":\"sandbox_drop\",\"sandbox_id\":\"${SBX}\",\"at\":\"$(date -u +%FT%TZ)\"}" >> /opt/incomex/staging/c1/_ledger.jsonl

# facet 6: terminate conns then drop
PSQL -d postgres -c "\"SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE datname='${SBX}' AND pid<>pg_backend_pid()\"" </dev/null
PSQL -v ON_ERROR_STOP=1 -d postgres -c "\"DROP DATABASE IF EXISTS ${SBX} WITH (FORCE)\"" </dev/null

# facet 7: readback (must be gone)
GONE="$(PSQL -tAc "\"select count(*) from pg_database where datname='${SBX}'\"" </dev/null | tr -d '[:space:]')"
[ "$GONE" = "0" ] && echo "RETIRED_OK: ${SBX}" || { echo "RETIRE_FAILED: ${SBX} still present"; exit 6; }

# facet 8: orphan sweep
echo "--- orphan c1_staging_% DBs still present ---"
PSQL -tAc "\"select datname from pg_database where datname like 'c1_staging_%'\"" </dev/null

Cleanup / rollback path for the whole lane (exact)

• Retire one sandbox: dot-staging-sandbox-drop --sandbox-id c1_staging_<ts>
• Equivalent raw mechanism it wraps (for audit): DROP DATABASE c1_staging_<ts> WITH (FORCE);
• TTL: 24h from birth created_at; owner = nmhuyen@gmail.com. After TTL, P2 is the retire path.