Vector data follows ordinary PostgreSQL write semantics. Use parameterized inserts, deterministic keys, and ON CONFLICT for idempotent retries; use scoped updates and deletions with lifecycle checks; and use COPY plus staging for large loads. A successful write is not complete until dimensions, model provenance, row counts, checksums, and deletion propagation are verified.
The unique key makes a retry converge. The checksum avoids rewriting identical data. Add an expected source-version predicate when concurrent content changes are possible.
For large migrations, load into an unlogged or regular staging table using client-side COPY, validate it, then merge in a transaction. Unlogged tables are not crash-safe or replicated like ordinary tables; use them only as rebuildable staging.
Validate nulls, duplicates, dimensions, model IDs, and foreign keys before merging. Build ANN indexes after a very large initial load when that reduces total build work, then analyze the table.
Prefer lifecycle operations with explicit tenant and source identity:
Never expose unscoped destructive SQL to an AI agent. A soft-delete timestamp can preserve audit history, but retrieval predicates, indexes, caches, and eventual hard deletion must all respect it.
Compare expected and actual row counts, distinct keys, checksum totals, failed rows, and sample vector norms. Run the same batch twice and prove the second run does not create duplicates. Delete one fixture source and prove chunks, embeddings, lexical indexes, caches, and citations no longer expose it.
Design an idempotent bulk embedding import for [row count, dimensions, update rate]. Include staging DDL, COPY approach, validation queries, conflict semantics, batch sizing, WAL/lock considerations, reject handling, reconciliation, and scoped rollback. Never generate an unqualified DELETE or UPDATE.
Verification contract: Run a duplicate batch, a wrong-dimension row, a stale checksum, and an interrupted batch. Each must produce a defined, measurable outcome.