Incomex AI Portal
KB-65A0

GPT Review — Legacy Vector + IU-0 PG CRUD Performance Directive

8 min read Revision 1
gpt-reviewvectorpg-crudperformanceiu-0legacy-vectordebounceoutbox

GPT Review — Legacy Vector + IU-0 Outline-D PG CRUD Performance Directive

Date: 2026-05-02 Reviewed: Opus Legacy Vector Stabilization OUTLINE v2 + IU-0 OUTLINE-D User focus: PG CRUD speed/stability for AI/Agent editing information units; vector must not block PG writes; at least 120s idle delay before vector projection.

Evidence sweep performed

KB search/read focused on prior vector/PG CRUD/performance design. Relevant evidence:

  1. knowledge/current-state/reports/td131-vector-sync-investigation.md
    • API CRUD currently syncs/deletes vector directly.
    • Direct SQL bypass caused Qdrant drift.
    • Lesson: never bypass API without vector sync; need vector audit and realistic health thresholds.
  2. knowledge/current-state/reports/pg-qdrant-sync-implementation
    • Prior architecture: PG trigger -> NOTIFY -> listener -> vector_store.upsert_document() / delete -> Qdrant.
    • This was immediate projection, not delayed/debounced.
  3. knowledge/dev/laws/dieu38-trien-khai/reports/vector-reality-check-agent-data-qdrant-2026-05-02.md
    • Legacy vector is document-based; update semantics delete old chunks then upsert new; no IU fields in payload; current health critical; trigger missing; audit failing.
  4. knowledge/dev/laws/dieu43-migrations/07-dieu43-v1-2-fdw-optimize.sql
    • Important hot-path principle: hot path must use local/cheap operations only; cold path can do heavier work. Reported pattern: local cache reads around ~100us on kb_documents INSERT; remote FDW work only on rare cold path.
  5. knowledge/current-state/reports/kb-protection-phase1-report
    • Existing protection design aimed for zero impact on write flow; transparent triggers.
  6. knowledge/current-state/reports/gpt-connection-final-closure-2026-04-04.md and stability reports
    • Sync blocking caused service instability historically; multiple workers/timeouts were needed.

Conclusion: vector delay is necessary but not sufficient. We need an explicit PG CRUD Hot Path Contract for all IU/KB write paths.

Verdict on Opus deliverables

PASS directionally, but NOT ready for Agent execution. Required revision before approval:

  1. Add a comprehensive PG CRUD Performance Contract, not only vector delay.
  2. Replace V1-C as the recommended option. The proposed “trigger UPDATE kb_documents SET vector_status='pending'” inside a trigger on kb_documents risks recursion/extra write amplification unless implemented as a guarded BEFORE trigger setting NEW only. As written, it is unsafe as default.
  3. Default architecture should be a durable transactional outbox/queue (V1-B refined), with lightweight trigger/API enqueue and delayed worker.
  4. API write path must be delayed too; not only PG trigger path. Current API inline embedding is also a CRUD performance risk.
  5. IU-0 must define vector freshness states and search behavior for pending/stale/current.

Required design decision

Final default:

  • Quiet window: 120 seconds after the last content-affecting update.
  • Worker poll interval: configurable, e.g. 30 seconds.
  • Debounce key:
    • legacy KB: document_id
    • IU: unit_version_id
  • Default write behavior: enqueue only, return PG CRUD response fast.
  • Immediate reindex: manual/admin operation only, with approval and rate limit.
  • Delete: may use shorter delay target (30-60s) but still cannot block CRUD.

PG CRUD Hot Path Contract — must be inserted

The following must be added to both Legacy Vector Stabilization v3 and IU-0 OUTLINE-E:

  1. PG CRUD hot path must not call OpenAI, Qdrant, Langroid, HTTP APIs, FDW, or any remote service.
  2. Hot path allowed operations only: local PG write, lightweight validation, local trigger, NOTIFY/outbox insert, content_hash calculation if cheap, audit row if already accepted.
  3. Trigger must be lightweight and idempotent.
  4. All vector projection must be asynchronous, delayed, debounced, retryable, and observable.
  5. Multiple edits in a 120s quiet window must collapse into one vector job.
  6. Metadata-only updates must not enqueue an embedding job.
  7. Worker must embed the latest committed content only.
  8. Worker must be idempotent: same key can be processed repeatedly safely.
  9. Worker must have retry/backoff/dead-letter behavior.
  10. Worker must expose queue lag, pending count, failed count, last success, and oldest pending age.
  11. Search must surface vector freshness: current / pending / stale / error.
  12. Fresh PG reads must remain the authority immediately after edit; vector search is eventual consistency.
  13. Direct PG writes by Agent must be supported safely via the same outbox/trigger path.
  14. Bulk edit/import must not enqueue per-row embeddings synchronously; it must batch/debounce.
  15. No cleanup/reindex/bulk embedding before a fresh Qdrant snapshot.

Preferred option for Legacy V1

Use refined V1-B: durable outbox queue.

Required outline:

  • Table/logical queue: vector_sync_outbox or existing equivalent if found.
  • Columns logical: source_kind, source_id/document_id/unit_version_id, operation, content_hash, first_seen_at, last_seen_at, earliest_run_at, status, attempts, locked_at, last_error.
  • Trigger/API path: UPSERT into outbox with earliest_run_at = now() + interval '120 seconds'; repeated updates only bump last_seen_at/content_hash and postpone earliest_run_at.
  • Worker: selects due jobs with FOR UPDATE SKIP LOCKED, processes latest committed content, updates status.
  • No immediate embedding in request path.

V1-C may be kept only as emergency fallback if implemented as a guarded BEFORE trigger setting NEW/vector_status and a separate reconciler. It must not be the default because it lacks audit/retry/backoff and delete semantics.

Required revision to IU-0

IU-0 OUTLINE-E must add:

  • IU-PG-CRUD-1..15 performance contract.
  • IU-VP-6 retained and expanded into async projection contract.
  • IU vector outbox uses unit_version_id as debounce key.
  • vector_sync_status: pending / stale / current / error / retired (logical labels; map to actual legacy statuses later).
  • Search adapter behavior:
    • if IU vector current and IU lifecycle enacted: prefer IU;
    • if IU vector pending/stale/error: show freshness warning or fallback to KB draft/PG direct read;
    • never hide PG-current content merely because vector is stale.

Directive to Opus

Next output must be revision only:

  1. Legacy Vector Stabilization OUTLINE v3

    • Add PG CRUD Hot Path Contract.
    • Change recommended option from V1-C to refined V1-B durable outbox.
    • Keep V1-C only as fallback with recursion warning.
    • Make API path and PG trigger path both enqueue-only.
    • Default quiet window = 120s; configurable to 180s.
    • Delete delay = 30-60s; still async.
    • Include queue observability and rollback.
    • No execution.

  2. IU-0 OUTLINE-E

    • Add IU-PG CRUD Performance Contract.
    • Expand IU-VP-6.
    • Add vector freshness and duplicate behavior.
    • Keep two-vector architecture and collection separation.
    • No full draft yet unless Outline-E passes.

Non-negotiable guardrails

  • No Agent fix yet.
  • No trigger creation yet.
  • No immediate embedding in CRUD path.
  • No Qdrant mutation, no cleanup, no reindex, no crontab change.
  • No P44-6.

Final review conclusion

Opus is close, but still under-specifies the performance/stability layer. The next revision must turn “vector delayed 120s” into a concrete Hot Path / Cold Path architecture. Default design should be durable outbox with 120s quiet-window debounce.

Back to Knowledge Hub knowledge/dev/laws/dieu44-trien-khai/reviews/gpt-review-legacy-vector-iu0-outline-d-pg-crud-performance-2026-05-02.md