Skip to content

AI and agents · By Rivtor · Updated

Store AI chat history in PostgreSQL with clear ownership

Store conversations and messages as separate records with explicit ownership, stable ordering and bounded retention. Keep message creation idempotent and handle incomplete model responses deliberately. PostgreSQL persistence does not automatically provide retrieval embeddings or a complete authorization layer.

See the Rivtor deployment workflow →

Model the conversation boundary

A conversation belongs to an account or authorized team. Each message belongs to that conversation. Every request that lists or appends messages must verify the caller can access the parent conversation; knowing a message or conversation identifier is not authorization.

The following illustrative schema omits the application’s user table, foreign keys to that table and authorization implementation. Review it for your application before applying any SQL:

CREATE TABLE conversations (
  id uuid PRIMARY KEY,
  owner_id uuid NOT NULL,
  created_at timestamptz NOT NULL DEFAULT now()
);
CREATE TABLE messages (
  id uuid PRIMARY KEY,
  conversation_id uuid NOT NULL REFERENCES conversations(id),
  sequence_no bigint NOT NULL,
  role text NOT NULL CHECK (role IN ('user', 'assistant')),
  content text NOT NULL,
  UNIQUE (conversation_id, sequence_no)
);

Use parameters for values in application queries. Stable ordering matters because timestamps alone can tie. Define how sequence allocation behaves under simultaneous requests.

Handle incomplete replies deliberately

Do not hold a database transaction open while waiting for an external model response. Record a request identifier and explicit processing state, then commit the completed message in a short transaction. If a client disconnects or a model call times out, define whether a partial reply is kept, retried or marked failed.

A uniqueness constraint can prevent repeated final inserts for one logical request. It cannot undo a repeated external model charge. Track that boundary separately.

Control storage and exposure

Choose retention, deletion and export behaviour before accumulating sensitive conversation content. Paginate message history and load only the context the model actually needs. Do not log full prompts, credentials or private conversation bodies as routine request diagnostics.

Rivtor’s PostgreSQL documentation covers its data-service boundary. Do not assume vector extensions, database policies or specialist retrieval features are installed; verify every required capability separately.

Verify using two test accounts

Create synthetic conversations, append messages concurrently and check ordering and duplicate handling. Confirm another account cannot list or modify them. Restart the API and verify the records remain. Rehearse a restore on disposable data if conversation recovery is a business requirement; a database connection check does not demonstrate this journey.

References