Production AI Database Design: Embedding Pipelines, Model Drift, Access Control, Cost, and Observability

Learning outcomes

ServiceHub launches semantic search successfully, then quietly degrades over six months. Source documents change without re-embedding; two model versions are mixed; a new model shifts nearest neighbors; ANN recall drops after index/data churn; external embedding cost spikes; and an AI-provider outage breaks search. A production AI database needs an embedding pipeline with lineage, staleness detection, model migration, index evaluation, tenant security, cost/latency budgets, observability and a deterministic fallback.

1. Production AI has at least three independently versioned layers

2. Create lineage-rich source and embedding tables

BEGIN EXECUTE IMMEDIATE 'DROP TABLE sh25_embeddings PURGE';EXCEPTION WHEN OTHERS THEN IF SQLCODE != -942 THEN RAISE; END IF; END;/BEGIN EXECUTE IMMEDIATE 'DROP TABLE sh25_source_docs PURGE';EXCEPTION WHEN OTHERS THEN IF SQLCODE != -942 THEN RAISE; END IF; END;/CREATE TABLE sh25_source_docs (  doc_id NUMBER PRIMARY KEY,  tenant_code VARCHAR2(30) NOT NULL,  status_code VARCHAR2(12) NOT NULL,  body_text CLOB NOT NULL,  content_version NUMBER DEFAULT 1 NOT NULL,  source_hash VARCHAR2(64) NOT NULL,  updated_at TIMESTAMP DEFAULT SYSTIMESTAMP NOT NULL);CREATE TABLE sh25_embeddings (  doc_id NUMBER NOT NULL    REFERENCES sh25_source_docs(doc_id),  model_name VARCHAR2(100) NOT NULL,  model_version VARCHAR2(50) NOT NULL,  dimension_count NUMBER NOT NULL,  normalization VARCHAR2(30) NOT NULL,  source_hash VARCHAR2(64) NOT NULL,  generated_at TIMESTAMP DEFAULT SYSTIMESTAMP NOT NULL,  embedding VECTOR(4,FLOAT32) NOT NULL,  state VARCHAR2(12) DEFAULT 'READY' NOT NULL    CHECK (state IN ('READY','STALE','FAILED','CANDIDATE')),  PRIMARY KEY(doc_id,model_name,model_version));

A real embedding table may use 384/768/1024/3072 dimensions or another model-defined size. Four dimensions keep the no-provider lab inspectable.

3. Detect stale vectors by source hash

INSERT INTO sh25_source_docs VALUES(  1,'TENANT_A','ACTIVE',  'Compressor restart after thermal shutdown',  1,'SRC-A1',SYSTIMESTAMP);INSERT INTO sh25_source_docs VALUES(  2,'TENANT_A','ACTIVE',  'Electrical cabinet breaker replacement',  1,'SRC-A2',SYSTIMESTAMP);INSERT INTO sh25_embeddings VALUES(  1,'LAB_MODEL','1.0',4,'UNIT_LIKE',  'SRC-A1',SYSTIMESTAMP,  VECTOR('[0.95,0.80,0.10,0.05]',4,FLOAT32),'READY');INSERT INTO sh25_embeddings VALUES(  2,'LAB_MODEL','1.0',4,'UNIT_LIKE',  'SRC-A2',SYSTIMESTAMP,  VECTOR('[0.08,0.05,0.90,0.30]',4,FLOAT32),'READY');COMMIT;

UPDATE sh25_source_docsSET body_text='Compressor restart after thermal shutdown with pressure inspection',    content_version=2,    source_hash='SRC-A1-V2',    updated_at=SYSTIMESTAMPWHERE doc_id=1;COMMIT;SELECT  s.doc_id,  s.source_hash AS current_source_hash,  e.source_hash AS embedded_source_hash,  e.model_version,  e.stateFROM sh25_source_docs sJOIN sh25_embeddings e  ON e.doc_id=s.doc_idWHERE s.source_hash <> e.source_hash;

The source hash mismatch is an objective staleness signal. Timestamp age alone cannot tell whether unchanged content needs re-embedding; a content/pipeline fingerprint can.

4. Mark stale rows explicitly

UPDATE sh25_embeddings eSET state='STALE'WHERE EXISTS (  SELECT 1  FROM sh25_source_docs s  WHERE s.doc_id=e.doc_id    AND s.source_hash<>e.source_hash);COMMIT;SELECT state,COUNT(*) AS embeddingsFROM sh25_embeddingsGROUP BY stateORDER BY state;

A retrieval query should normally require state='READY' for the chosen model version, or define a documented fallback if stale vectors are temporarily acceptable.

5. Model migration uses side-by-side candidate embeddings

Never overwrite model 1.0 vectors in place before model 2.0 is evaluated. Generate model 2.0 into separate rows/indexes, label them CANDIDATE, evaluate against a relevance test set, then atomically switch the active model configuration.

INSERT INTO sh25_embeddings VALUES(  1,'LAB_MODEL','2.0',4,'UNIT_LIKE',  'SRC-A1-V2',SYSTIMESTAMP,  VECTOR('[0.91,0.86,0.07,0.04]',4,FLOAT32),'CANDIDATE');INSERT INTO sh25_embeddings VALUES(  2,'LAB_MODEL','2.0',4,'UNIT_LIKE',  'SRC-A2',SYSTIMESTAMP,  VECTOR('[0.04,0.06,0.94,0.25]',4,FLOAT32),'CANDIDATE');COMMIT;

6. Deliberately wrong: query both model versions in one distance ranking

VARIABLE q VECTORBEGIN  :q := VECTOR('[0.93,0.83,0.08,0.05]',4,FLOAT32);END;/SELECT doc_id,model_version,       VECTOR_DISTANCE(embedding,:q,COSINE) AS distanceFROM sh25_embeddingsWHERE state IN ('READY','CANDIDATE')ORDER BY distance;

Both versions have four FLOAT32 dimensions, so Oracle can calculate distances. The result is still semantically invalid because the query vector belongs to one model space. The safe repair is to bind/query only the matching model/version and create a separate query embedding per candidate model during A/B evaluation.

7. Active model configuration is data, not hard-coded application lore

CREATE TABLE sh25_model_registry (  use_case VARCHAR2(40) PRIMARY KEY,  model_name VARCHAR2(100) NOT NULL,  model_version VARCHAR2(50) NOT NULL,  dimension_count NUMBER NOT NULL,  distance_metric VARCHAR2(20) NOT NULL,  normalization VARCHAR2(30) NOT NULL,  status VARCHAR2(12) NOT NULL    CHECK (status IN ('ACTIVE','CANDIDATE','RETIRED')),  activated_at TIMESTAMP);INSERT INTO sh25_model_registry VALUES(  'SERVICE_NOTE_SEARCH',  'LAB_MODEL','1.0',4,'COSINE','UNIT_LIKE',  'ACTIVE',SYSTIMESTAMP);COMMIT;

Promotion to 2.0 should update this registry only after evaluation and index readiness. The application reads/configures one active semantic space.

8. Evaluation set separates retrieval correctness from LLM answer quality

CREATE TABLE sh25_retrieval_eval (  query_id NUMBER PRIMARY KEY,  query_text VARCHAR2(500) NOT NULL,  tenant_code VARCHAR2(30) NOT NULL,  expected_doc_id NUMBER NOT NULL,  business_reason VARCHAR2(500) NOT NULL);INSERT INTO sh25_retrieval_eval VALUES(  1,  'how to restart an overheated compressor',  'TENANT_A',  1,  'Known approved compressor procedure should rank in top-k');COMMIT;

For each model/index version, generate the query embedding using that same model, measure exact/ANN top-k, recall and labeled relevance. Only after retrieval passes should an LLM answer layer be evaluated for citation faithfulness/hallucination. A correct top-k set can still produce a bad answer, and a fluent answer can conceal bad retrieval.

9. Index lifecycle follows the model lifecycle

• Build candidate HNSW/IVF index against candidate model rows or a version-specific table/partition.
• Measure build duration, Vector Pool/PGA/storage, DML lag/maintenance.
• Measure recall@k against exact results and p50/p95/p99 retrieval latency.
• Switch active model/index only after evaluation passes.
• Keep the prior model/index until rollback window closes.
• Re-evaluate after substantial corpus churn, parameter/index rebuild or RU change.

10. Generation options and platform boundaries

11. Cost and latency budgets

Track embedding generation queue delay, provider tokens/requests or GPU/CPU seconds, database write/index-maintenance cost, Vector Pool/PGA/storage, retrieval latency, reranker/LLM latency and application end-to-end latency. Do not optimize only vector-query milliseconds while provider inference dominates the user response time.

CREATE TABLE sh25_ai_run_metrics (  run_id VARCHAR2(64) PRIMARY KEY,  stage VARCHAR2(30) NOT NULL,  model_version VARCHAR2(50),  started_at TIMESTAMP NOT NULL,  ended_at TIMESTAMP,  rows_processed NUMBER,  failures NUMBER DEFAULT 0,  estimated_cost NUMBER,  detail_json JSON);

12. Tenant access is evaluated at retrieval time

Do not embed tenant identity into a vector and expect the model to enforce access. Keep tenant/security metadata in relational columns and VPD/privilege policy, exactly as Lesson 3. An embedding can reveal source semantics if exfiltrated and should be treated as derived sensitive data according to classification policy.

13. Failure and fallback design

If the embedding provider is unavailable, continue serving existing READY vectors and queue changed documents for later re-embedding. If semantic search/index is unavailable, fall back to tenant-safe keyword/structured retrieval. If the LLM is unavailable, return retrieval results with citations/snippets rather than fabricating an answer. A fallback that bypasses VPD or switches to global search is not a fallback—it is a security incident.

14. RU and HA/DR awareness

RMAN/Data Guard protect table/vector data according to their normal datafile/redo model, but vector-index-specific restrictions matter: vector indexes are not supported with transportable tablespaces in Data Pump, and distributed HNSW/RAC has its own memory/duplication/reload behavior. 23.26.3's distributed-HNSW scalar quantization feature should be exercised only on that RU or later with the required RAC topology.

15. Rollback a model migration

UPDATE sh25_model_registrySET status='CANDIDATE',    activated_at=NULLWHERE use_case='SERVICE_NOTE_SEARCH'  AND model_version='2.0';UPDATE sh25_model_registrySET status='ACTIVE',    activated_at=SYSTIMESTAMPWHERE use_case='SERVICE_NOTE_SEARCH'  AND model_version='1.0';COMMIT;

In a real registry design, enforce exactly one ACTIVE row per use case through schema/application controls and make the switch one transaction. Keep old embeddings/index until the rollback and retention windows expire.

16. Cleanup

DROP TABLE sh25_ai_run_metrics PURGE;DROP TABLE sh25_retrieval_eval PURGE;DROP TABLE sh25_model_registry PURGE;DROP TABLE sh25_embeddings PURGE;DROP TABLE sh25_source_docs PURGE;

17. Production judgment and chapter close

Production AI is data engineering plus retrieval engineering plus model operations. Store source/model lineage, prevent cross-model distance comparisons, evaluate exact and ANN retrieval, keep authorization relational/database-enforced, observe the full inference/retrieval path, and maintain deterministic non-AI fallbacks. Model drift is not only “model became worse”; source distribution, chunking, index accuracy and business definitions can drift too.

Current baseline is Oracle AI Database 26ai RU 23.26.3, SQL Developer 26.2 and SQLcl 26.2.1.222.1617. VECTOR requires COMPATIBLE >=23.4.0; no lab raises it. External model/provider/private-container costs and credentials are separate from Oracle Free. Free's 2 CPU/2 GB/12 GB envelope is suitable for learning, not an enterprise vector-capacity result. No management pack is required by the mandatory labs. The next chapter can apply the same lineage/rollback discipline to cross-platform migration, upgrade and replication operations.

Authoritative references

• Oracle AI Vector Search User's Guide — embedding/index/retrieval lifecycle
• Vector Generation Examples — in/out-of-database embeddings/chunking
• Vector Search Restrictions — platform/driver/Data Pump/redaction restrictions
• Private AI Services Container — private embedding/LLM infrastructure
• Select AI Guide — AI profile/provider/RAG boundary