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

Local pgvector Task 2 report

Outcome

Implemented ordered, idempotent production migrations and the tht vector migrate interface, including tht vector migrate --status --json with pristine JSON output.

Implementation

• 001_extensions.sql installs pgvector.
• 002_schema_tables.sql creates vectors.schema_records, vectors.evidence, and vectors.memory with the VectorWriteRecord columns and vector(768) embeddings.
• 003_roles.sql creates passwordless NOLOGIN reader/writer roles. Deployments inject credentials (or grant these roles to separately-created login roles); no production secret is stored in the repository.
• Reader authority is schema usage plus table SELECT.
• Writer authority is schema usage, table INSERT/UPDATE, narrow hash-probe column SELECT, and sequence USAGE. It has no DELETE, broad row SELECT, DDL, or ownership authority.
• The migration runner discovers ordered SQL files, records SHA-256 checksums in public.tht_vector_migrations, serializes runners with a transaction-scoped advisory lock, and applies the full pending batch in one transaction.
• Status distinguishes applied, pending, and checksum-drifted migrations. Apply refuses drift. A failed migration rolls back both prior migrations in that batch and ledger writes.

TDD evidence

RED was observed with a real pgvector/pgvector:pg16 testcontainer: 6 failures for the missing module, missing command, and missing schema.

GREEN verification:

• Focused migration + direct adapter integration: 23 passed.
• Full harness from the documented harness/ cwd: 473 passed, 5 deselected.
• Targeted Ruff (tht plus the new L0 test): clean.
• git diff --check: clean.

The new L0 coverage exercises clean install, idempotent rerun, pristine JSON status, checksum drift, transaction rollback, exact tables/columns/dimensions, role isolation, sequence authority, and the real PgVectorStore.health() plus VectorWriteRecord upsert path.

Existing repository lint baseline

The requested full ruff check . was run. It reports 34 pre-existing violations in unrelated test files (unused imports and one-line semicolon statements). None are in Task 2 files; changing them would exceed this task's scope. The complete harness test gate is green.

Self-review

No unresolved Task 2 correctness concern found. One deliberate contract choice is worth noting: writer INSERT and UPDATE are table-level because the approved direct adapter health probe uses has_table_privilege for those authorities. Least privilege is retained by withholding broad SELECT, DELETE, DDL, ownership, and credentials.

Review fix wave

The post-implementation review found four production-boundary gaps. They are fixed as follows:

• Migration SQL now ships inside the tht wheel (tht/migrations/vector) via explicit setuptools package-data and is discovered through importlib.resources, rather than relying on a source-checkout-relative directory.
• Both status and apply reject ledger versions absent from the installed manifest, including nonnumeric future version labels. This treats a binary/database downgrade as drift instead of silently reporting a healthy state.
• Migration files are ordered by parsed integer version; spellings such as 2 and 02 are rejected as duplicate versions.
• Every migration transaction pins search_path locally to pg_catalog, pg_temp; catalog calls and the ledger are schema-qualified. pgvector is installed into the locked vectors schema, tables use vectors.vector, and PgVectorStore qualifies vector casts and the cosine operator. A hostile admin default path with a writable shadow schema cannot redirect migration objects.
• The core image build asserts CLI discovery. Image verification now starts an ephemeral pgvector database, runs the installed image's migration command, and compares pristine apply/status JSON.

Additional verification after the fix wave:

• Focused migration, adapter, hostile-path, and wheel suite: 27 passed.
• Full harness: 477 passed, 5 deselected.
• Production core image build: passed, including build-time CLI discovery.
• Core-image apply/status smoke against pgvector/pgvector:pg16: passed.
• Changed production and test files: Ruff clean; git diff --check clean.
• Full Ruff remains at the same 34 pre-existing unrelated test-file findings documented above.

No dependency changed, so the committed Python requirements lock did not require regeneration.