The database boundary in an AI system

One entity does not need four sources of truth

An AI application may touch an operational database, a warehouse, an event store, and a vector index in a single request. The architecture becomes difficult to reason about when every copy is called the source of truth. I would assign authority at the level of a fact and an operation: who may change an account status, who owns an event history, and which derived view can safely lag.

This current design study follows a hypothetical service asset. Postgres owns its current administrative state. Redshift supports historical analysis. Cassandra is considered for a specific time-bucketed event access pattern. A vector index supports retrieval over associated documents. This is an illustrative architecture, not a prescription to deploy all four stores. Each additional system must justify the operational and consistency boundary it introduces.

Write an ownership contract before choosing an index

The asset needs a stable identity independent of its display name, storage location, or document wording. It also needs a version for mutable facts and a definition of when those facts take effect. I would distinguish the current asset record from observations about the asset. A sensor observation and an administrator changing the asset status are different kinds of events, even if both contain a timestamp and an asset identifier.

The proposed Postgres table below represents current state. It deliberately contains no embedding and no warehouse aggregate. An optimistic concurrency update could require the caller to present the version it read, incrementing the version only when that condition matches. That mechanism needs a surrounding transaction and explicit handling of zero updated rows; a version column alone does not prevent lost updates.

The contract should also define deletion and retention semantics. Does retiring an asset hide it from search, preserve it for analysis, or remove particular personal attributes? Downstream consumers need a machine-readable transition they can apply. A record disappearing from one table is insufficient notification to a separate index that already copied its contents.

1CREATE TABLE asset (
2    asset_id uuid PRIMARY KEY,
3    tenant_id uuid NOT NULL,
4    state text NOT NULL,
5    version bigint NOT NULL CHECK (version > 0),
6    effective_at timestamptz NOT NULL,
7    updated_at timestamptz NOT NULL
8);
9 
10-- Bind parameters; validate $5 as the transition effective time.
11-- Treat zero affected rows as a conflict.
12UPDATE asset
13SET state = $1, version = version + 1,
14    effective_at = $5, updated_at = now()
15WHERE asset_id = $2 AND tenant_id = $3 AND version = $4;

Use a warehouse for a declared analytical view

The warehouse answers a different question from the operational service. Instead of asking for the current status of one asset, an analyst may ask how status transitions varied across regions and months. I would model that history explicitly rather than repeatedly overwriting the only analytical copy with the latest value. The ingestion contract should preserve event time, arrival time, and source identity so late corrections can be distinguished from new activity.

AWS documents that Redshift differs from PostgreSQL in important architectural and SQL details. Familiar syntax does not make the two interchangeable execution environments. I would validate constraints, data types, and query behavior in the target engine, then expose a freshness boundary to consumers. A dashboard produced from an hourly projection should not quietly be used as an immediate authorization decision.

Historical correctness also needs a policy. If a source correction changes last month’s region assignment, should prior reports be restated, preserve the originally known classification, or offer both views? That decision belongs in the data contract. A model trained from the warehouse may need the information available at prediction time, whereas financial reconciliation may need the corrected truth. The same table cannot satisfy both interpretations accidentally.

Let the access pattern justify Cassandra

Cassandra’s documentation emphasizes designing tables around queries. In this example, the candidate access pattern is reading an asset’s recent events within a bounded time range. A partition key containing tenant, asset, and a time bucket could limit the amount of history read for that query. The bucket size would be chosen from measured event volume and the required range, not copied from an unrelated system.

I would test skew as part of that decision. A small number of very active assets can create very different partitions from the median asset. Retention, late events, duplicate delivery, and read consistency choices also affect the design. If the actual workload requires frequent joins, flexible ad hoc filters, and strict multi-record transactions, I would reconsider whether this additional store is useful rather than making every query fit the original technology choice.

The boundary remains explicit: Cassandra would own or serve the event access path described by its contract; it would not silently become the authority for administrative asset state. A status change might be recorded as an event and projected into current state, but the ordering and conflict rules must then be designed around that event authority. Mixing both ownership models without naming the distinction is a reliable way to create contradictory answers.

Treat embeddings as a versioned projection

An embedding represents text under a particular model and preprocessing procedure. It is not the document itself, and a nearby vector is not a verified fact about the asset. I would store document identity, source version, chunk location, embedding model version, authorization context, and projection status with each retrievable unit. The result should lead back to the evidence used to construct it.

Pgvector supports exact and approximate nearest-neighbor search in Postgres. That creates a useful option when operational metadata and vector retrieval benefit from living within one database boundary. It does not remove the need to measure retrieval quality or inspect filtering behavior. An approximate index trades some search behavior for speed and resource characteristics; its suitability depends on the actual corpus and query restrictions.

For a separate vector service, the projection consumer needs replay and deletion handling just like any other downstream store. A document update may invalidate old chunks; a revoked permission may invalidate a previously cached answer. I would verify source authorization at the appropriate request boundary and propagate changes promptly, rather than assuming a copied access label remains valid forever. Index freshness and permission freshness are related but distinct properties.

Give the semantic layer definitions it can enforce

An ontology or semantic model can connect these stores through explicit entity and relationship definitions. In this proposal, Asset, Observation, Document, and Organization would have distinct identities and stated relationship meanings. The semantic layer should specify whether a link means ownership, evidence, location, or an inferred association. A graph edge called related_to is easy to create and difficult to interpret responsibly.

I would maintain mappings from source fields to these definitions with versions and provenance. When two systems disagree about the same attribute, the resolution policy should name the authoritative source or preserve both observations with their contexts. A semantic layer becomes useful when it prevents an invalid join or makes ambiguity visible. It should not merely add a new vocabulary above unresolved inconsistencies.

This also gives an AI application a better retrieval contract. A question about current ownership can request an authoritative record; a question about historical behavior can request a warehouse view; a question about a manual can request source passages. Combining those results requires preserving their time and authority boundaries. A fluent answer should not erase the differences between a current fact, an analytical estimate, and a textual claim.

Rehearse disagreement between stores

The most informative integration test is not the happy path. I would delay a projection, replay an event, change a permission, delete a document, and submit a correction with an older event time. For each case, the expected user-visible behavior should be written before the test. Some queries may return a freshness warning; others should fall back to an authoritative read or refuse to answer.

The final architecture record would name the owner of each fact, the allowed lag of each projection, the replay mechanism, and the reconciliation process. If the team cannot operate those boundaries, fewer databases may be the better system. An AI application benefits from specialized storage only when it can still explain where its answer came from, how current it is, and which component is responsible when the copies disagree.