ThothII/.superpowers/sdd/pgvector-task-4-report.md

Local pgvector Task 4 report

Outcome

Implemented adapter parity gates and an operator-safe custom-format backup/restore workflow.

• Direct and HTTP stores now share validation, configured-dimension rejection, and deterministic similarity ordering with record ID as the tie-break.
• The parity fixture exercises identical records through real pgvector and the HTTP RPC contract: kind filtering, ordering, hashes, replacement upserts, invalid collection/kind errors, and query plus write dimensions.
• Backup explicitly allowlists the three vector tables and migration ledger, refuses overwrite, writes through a partial file, and uses a custom compressed archive.
• Restore requires explicit active-source and target coordinates. It compares PostgreSQL system identifier plus database OID (robust across DNS aliases), refuses the active database, checks for an empty target unless force is explicit, and restores with exit-on-error.
• Passwords are accepted only through validated secret files, converted to private temporary PGPASSFILEs, and never placed in command arguments or success/error logs.
• Role passwords/login identities are deliberately not dumped. The target must have the approved passwordless group roles and pgvector extension reconciled before restore; archived ACLs restore the reader/writer grants.

TDD and semantic alignment

The first parity run exposed the intended HTTP differences: it accepted unknown collections and wrong dimensions. Direct pgvector also had no stable order for equal cosine distance. The adapters were aligned, and the final focused real-pgvector gate passed: 25 passed.

The first recovery run caught an incorrect probe username before restore. The second caught an intersection between pg_dump --schema and the explicit public ledger table. The third confirmed the archive contents but caught missing target group roles. Each defect was corrected and the complete drill was rerun from a fresh generated project.

Live recovery smoke

./scripts/local-vector-smoke.sh --backup-restore: PASS.

• generated/owned source Compose project and source vector_data
• distinct restore container and distinct named restore volume
• migration and role health, secret rotation, restart persistence
• real custom backup, then deliberate mutation of the active source record
• same-database identity guard evaluated before restore
• restore into the separate target only
• restored hash equals the pre-mutation backup, proving retrieval parity
• migration ledger has all three applied versions
• all three restored embedding columns report vectors.vector(768)
• ownership-checked cleanup; the active operator project/volume is never addressed

Verification

• parity + direct adapter: 25 passed
• full harness: 485 passed, 5 deselected
• changed Python files: Ruff clean
• shell syntax: clean
• git diff --check: clean
• full Ruff: unchanged repository baseline of 34 unrelated pre-existing test-file violations

Self-review and operational constraints

The restore account must be able to read pg_control_system() for the robust cluster-identity comparison and create/restore the selected objects. This is intentionally an administrative recovery operation, not a runtime reader/writer action. --force-nonempty is explicit but still uses pg_restore --clean --if-exists; operators should prefer a new database/volume and validate migration status, health, and known retrieval before endpoint cutover.

Post-review hardening

All five final review findings were addressed in a follow-up commit:

• Restore now requires a physically separate PostgreSQL cluster and refuses any equal system_identifier, independent of database OID or hostname.
• pg_restore combines --single-transaction with --exit-on-error. The live drill creates an existing vector sentinel, deliberately fails late during a forced restore, and proves the original sentinel row/hash remains unchanged before performing the successful restore.
• Backup uses a mode-0600 mktemp in the output directory, atomically renames it, and cleans only that owned path. A fake-command test pins symlink-clobber resistance and preserves an adversarial legacy .partial symlink and its target.
• HTTP parity now traverses the real VectorRestClient transport boundary. It asserts RPC URL/key and kinds payloads, legacy 404 fallback, response conversion, malformed metadata tolerance, and canonical VectorRestError to VectorStoreError mapping.
• The restored target runs role/secret reconciliation and a real PgVectorStore with separate reader/writer logins. Health, known-record search, writer upsert, hash probe, schema/table/column/ sequence authority, and 768-dimensional compatibility are therefore verified through the production adapter. Reconciliation now restores group-role schema USAGE, which table-selected archives cannot carry.

Atomic no-replace backup publication

The final publication review is also closed. The private same-directory archive is published with an atomic hard-link create rather than rename-overwrite semantics. If any process creates the final file or symlink after preflight but before publication, ln fails with EEXIST, the backup exits nonzero, the concurrent destination remains byte-for-byte intact, and the trap removes only the randomly named temporary archive owned by this invocation. The fake pg_dump safety test creates that destination immediately before returning and pins the failure and cleanup behavior.