Do not mix every kind of data in the same tables
An AI application handles several very different classes of data: transactional business data, documents, embeddings, conversations, prompts, model outputs, technical traces and audit data.
Mixing them without clear separation quickly creates performance, governance and security problems. Good architecture starts by identifying the responsibility of each storage area.
PostgreSQL can absolutely remain the core of the system, but schemas and tables should be organized by purpose rather than development convenience.
The transactional core remains relational
Critical business entities should remain modeled with relations, constraints, foreign keys, indexes and transactions. AI does not replace relational consistency.
Customers, users, contracts, interventions, orders and access rights should keep stable identifiers and strong integrity rules.
This layer is the source of truth. AI processes enrich it, but should not become the only representation of business state.
Separate documents, chunks and embeddings
In a RAG architecture, it is useful to separate the source document, document versions, generated chunks and associated embeddings.
Each chunk should retain a reference to its document, version number, position, business metadata and optionally a hash. This preserves answer provenance.
With pgvector, embeddings can live in PostgreSQL, but vector indexes, filtering strategy and consistent deletion on document updates must be planned.
Log AI calls without storing everything
To diagnose an AI system, teams need to recover the model used, latency, status, approximate cost, prompt version and errors.
However, systematically storing full prompts and every transmitted value can create a permanent copy of sensitive information.
A better approach separates technical metadata from content, masks sensitive fields and defines an appropriate retention period.
Design multi-tenancy and security into the data model
In a multi-company AI SaaS, every business record, document, chunk, conversation and trace should be explicitly tied to an organization or tenant.
Tenant filters should not exist only in the UI. They must be enforced in the backend and, when appropriate, strengthened with PostgreSQL Row Level Security.
This discipline becomes even more important with AI agents that can chain multiple tools and data sources.
A reference structure
A simple structure can separate logical_business, ai_knowledge, ai_runtime and audit schemas. The names matter less than the separation of responsibilities.
The business schema contains transactional entities. The knowledge schema contains documents, chunks and embeddings. Runtime contains conversations, agent executions and outputs. Audit stores traceability events.
This organization makes migrations, retention policies, permissions and analytics much easier to control as the application grows.
