Postgres as an AI agent's memory: tables to create first

By KYN AI Advisory Team — AI implementation specialists, Singapore

Use Postgres as the agent's memory and start with six tables: conversations, messages, facts, tasks, events and approvals. Postgres is already the system of record for most business software, so the agent reads and writes the same data your team sees. You get transactions, row-level security and plain SQL for audit. A chat-history buffer alone is not memory. Memory is structured state the agent can look up, correct and be held to.

What does an AI agent need to remember?

Three kinds of things, and they behave differently.

• What was said. The raw thread: who wrote what, when, on which channel.
• What is known. Facts about a customer, supplier or case that stay true across threads: preferred language, billing contact, agreed terms.
• What is pending. Tasks the agent or a person still owes, and approvals still waiting.

Workflow tools usually ship a memory node that stores the last N messages per session. n8n's AI agent documentation describes that pattern, and a Postgres-backed version is a common option. It works for a chatbot. It breaks for business work, because a message buffer cannot answer "what did we agree with this supplier in March?" without rereading everything.

Split the three kinds into separate tables. Each gets its own rules for retention, correction and access.

Which tables should you create first?

Six tables cover most SME agents. Names are suggestions.

| Table | Stores | Key columns | |---|---|---| | conversations | One row per thread | id, channel, contact_id, opened_at, status | | messages | Every inbound and outbound message | id, conversation_id, direction, body, sent_at, actor | | facts | Durable statements about a contact or case | id, subject_id, key, value, source_message_id, confirmed_by | | tasks | Work owed, by whom, by when | id, subject_id, owner, due_at, state | | events | Append-only log of every agent action | id, actor, action, target, at, payload | | approvals | Requests waiting for a named person | id, action, requested_by, approver, decided_at, decision |

Build events and approvals on day one. Teams add them after the first incident, and then they have no history to read.

How should facts be stored?

Store a fact as a key, a value and a source. Never store a bare claim.

create table facts (
  id uuid primary key default gen_random_uuid(),
  subject_id uuid not null,
  key text not null,
  value text not null,
  source_message_id uuid references messages(id),
  confirmed_by text,
  valid_from timestamptz not null default now(),
  valid_to timestamptz
);

Three design choices matter here.

1. Source. Every fact points to the message it came from. A person can open the source and check it. If the agent wrote "payment terms are 30 days", you can see who said so.
2. Confirmation. The agent can propose a fact. A fact that changes money, terms or identity stays unconfirmed until a person marks it. The agent treats unconfirmed facts as hints, not truth.
3. Validity window. Do not overwrite. Set valid_to on the old row and insert a new one. You keep the history and can answer what was true on a given date.

Should the agent use vector search?

Often not at first. Structured lookups answer most business questions: this customer, this quote, this month. A query on facts by subject and key is exact, cheap and explainable.

Add embeddings when you have a real retrieval need, such as searching years of free-text notes or policy documents. The pgvector extension keeps embeddings in the same database, so you do not add a second service. Treat retrieved text as untrusted input. A stored message can contain instructions aimed at the agent, which we cover in prompt injection in business AI agents.

How do you keep agent memory safe?

Memory is a new place to leak or corrupt data. Four controls cover the common failures.

• Row-level security. Postgres can restrict which rows each database role sees, per the PostgreSQL row security documentation. Give the agent its own restricted role. It reads only the rows for the contact in front of it.
• No delete by the agent. The agent inserts and proposes. A person deletes. Deletions wait for a named person.
• Append-only events. Revoke update and delete on events from every application role. The log stays trustworthy.
• Retention. Decide how long messages live. Personal data you no longer need should go on a schedule, not stay forever by default.

How big does this get?

For an SME, small. A team handling a few thousand conversations a month adds rows in the tens of thousands, which Postgres handles on a small instance. The cost is in the design, not the storage. Index messages by conversation and facts by subject and key, and lookups stay fast.

What does a worked example of the first tables look like?

Take an agent that sorts incoming customer enquiries (illustrative). Four tables are enough for a first release.

| Table | Key columns | Purpose | |---|---|---| | contacts | id, name, channel, consent flag | Who the agent may talk to | | cases | id, contact id, status, owner, opened at | One row per matter | | events | id, case id, actor, action, payload, time | Every step, append only | | facts | id, case id, key, value, source, valid from | What the agent knows, with where it came from |

Rules that save pain later: events are never edited, only added; every fact has a source and a date; and the agent reads a case by its id, not by searching the whole database.

When a customer writes again after two weeks, the agent loads that case, its facts and the last ten events. It does not need the full history of every customer. This keeps cost low and answers consistent, and it keeps one customer's data out of another's context.

What should you back up and retire?

Memory grows, so plan its end. Back up the database on a schedule and test a restore once a quarter, because an untested backup is a hope. Decide how long each table is kept: events for the period your audit needs, facts for as long as they stay valid. Mark old facts as superseded instead of deleting them, so the history stays readable. When a customer asks for their data to be removed, delete by contact id across all four tables and log who did it. Review the size of each table monthly. A table that grows faster than your case volume points to a loop or a duplicate.

Where KYN fits

KYN builds agent memory as part of a larger system that runs in your own cloud account. In a manufacturer's system we moved 10,210 vendor quotes and a ledger of 3,309 reconciled rows with zero duplicates into Postgres on one AWS account. Agents have their own restricted accounts, the database uses row-level security, and every action is logged with the actor. Outbound messages, money, filings and deletions wait for a named person.

The first 14 days are a review of your stack and interviews with your team. A full build ships in under 90 days. See infrastructure and security.

Frequently asked questions

Is a chat history buffer enough for an agent's memory?

For a casual chatbot, yes. For business work, no. A buffer forgets older threads and cannot be queried for a specific agreement. Structured facts with sources let the agent and your team agree on what is known.

Do I need a separate vector database?

Usually not. Postgres with pgvector keeps embeddings next to your other data, with the same access rules and backups. Move to a dedicated store only if you measure a retrieval problem the extension cannot meet.

Who can change what the agent remembers?

A named person, through the app. The agent proposes facts and the person confirms the ones that matter. Every change is written to the event log with the actor, so you can trace any fact back to its source.

Checked 30 October 2026.

Curious whether Marketing agents fits your business? Talk to KYN on WhatsApp — no forms, just a conversation.

Start Building →