Designing Memory for AI Agents: Working Context, PostgreSQL, RAG, and Long-Term State

“Just give the model more context” is the most expensive sentence in AI product development. Context is not a pile of text. Context is a product decision about what the system is allowed to know, believe, and act on in this moment.

An early prototype can paste a whole conversation into a prompt. As documents, users, permissions, and time horizons grow, that approach becomes hard to inspect and reconcile. The fix is not automatically a larger window; it is to decide which layer owns each fact and how a run is allowed to use it.

The four layers I design for

1. Working memory (ephemeral)

What this run needs right now: goal, plan, last tool results, user instruction. Lives in the agent state or request scope. Discarded or checkpointed when the run ends.

2. Canonical product state (source of truth)

Postgres (or equivalent) rows the business trusts: tasks, decisions, people, projects, preferences. Updated carefully. Never overwritten by a free-form model hallucination without validation.

3. Evidence store (immutable-ish)

Raw and near-raw artifacts: transcripts, emails, documents, screenshots, API payloads. Append-heavy. The place you go when you need proof.

4. Retrieval index (approximate)

Embeddings, keyword search, hybrid retrieval. A hint system for what might be relevant — not a truth system.

Why chat history is a terrible primary memory

Chat UIs train users and engineers to think “the conversation is the product.” For assistants that may be fine. For products that manage work, it fails because:

• Conversations are not queryable business objects
• Contradictions accumulate without resolution
• Permissions and retention are unclear
• You cannot build reliable automations on vibes

The product move: chat as interface, state as system. The model reads and writes structured objects. The thread is commentary, not the ledger.

Designing context for a feature

When a customer asks for something like “remind me what we decided with Acme last week,” I do not dump every Acme-related token into the model. I design a context assembly pipeline:

1. Resolve entities — which Acme? which people? which project IDs?
2. Fetch canonical state — open decisions, commitments, owners
3. Retrieve evidence — top-k transcript spans / emails with scores
4. Pack a prompt pack — system rules + structured state + evidence excerpts + question
5. Require citations — answers must point to evidence IDs or admit uncertainty

That pipeline is the feature. The LLM call is the last mile.

Provenance is the trust feature

Users forgive incomplete answers. They do not forgive confident fiction.

Practices that work:

• Every derived claim links to evidence IDs
• UI distinguishes fact / derived / suggestion
• “I don’t know” is an allowed structured outcome
• Human approval for claims that become tasks or CRM updates

In professional intelligence work, this is non-negotiable: a commitment without a transcript span is a liability.

Context windows as packing problems

Think like a logistics engineer, not a novelist.

Priority order for packing:

1. Hard constraints and safety rules
2. Schema / output contract
3. Canonical state relevant to the entities
4. Highest-scoring evidence spans
5. Optional background (only if budget remains)

Compression strategies:

• Structured summaries with pointers back to raw evidence
• Deduplicate repeated boilerplate
• Prefer tables for entity attributes
• Drop conversational filler aggressively

When the pack does not fit, drop background first, never drop the output contract.

RAG without the cargo cult

RAG is useful. RAG is also how teams accidentally build a second, worse database.

Rules of thumb:

• Retrieve for questions, not for identity. “Who is the owner of Project X?” should hit canonical state.
• Hybrid search beats pure vector for proper nouns and IDs.
• Always return metadata: source, timestamp, ACL tags.
• Re-rank with a cheap model or heuristics before the expensive generation call.
• Evaluate retrieval separately from generation (did we fetch the right span?).

Multi-agent context is a coordination tax

If you split work across agents, you must design the shared blackboard:

• What each agent may read
• What each agent may write
• How conflicts resolve
• How much history each sub-agent gets

Unbounded “share the whole transcript with every agent” recreates the paste-everything failure mode with more invoices. Prefer a shared canonical state plus small working memories.

Session continuity without prompt obesity

For products that feel personal across days:

• Store preferences and durable facts in canonical state
• Reconstruct working context from state + recent events at session start
• Avoid “append forever” chat as the continuity mechanism
• Expire or summarize old working memory deliberately

This is how you get continuity that ops can debug: the system knew X because row Y said so, not because message #482 implied it.

Failure modes to instrument

A reference shape I keep returning to

User question
  → Entity resolution
  → Load canonical state (Postgres)
  → Retrieve evidence (hybrid)
  → Pack context under token budget
  → Generate structured answer + citations
  → Validate schema
  → Optional HITL
  → Write accepted results back to canonical state

Everything trendy — agents, tools, multi-model routing — plugs into this spine. Without the spine, trends become demos.

Product takeaway

Context management is how you scale trust. It is also how you scale cost. The teams that win treat context as architecture: layers, provenance, packing, and write-back.

If you are early, start smaller than you think:

1. One canonical table for the objects that matter
2. One evidence store with IDs
3. One retrieval path with citations
4. One prompt packer with a hard budget

That is enough to ship a 0→1 AI feature you can own after deploy — and improve when real users show you which context you forgot.

A small relational starting point

For a system that needs evidence-backed memory, keep durable facts and the source material that supports them separately. A deliberately small schema might start like this:

CREATE TABLE evidence (
  id uuid PRIMARY KEY,
  tenant_id uuid NOT NULL,
  source_uri text NOT NULL,
  observed_at timestamptz NOT NULL,
  content_hash text NOT NULL,
  excerpt text NOT NULL,
  search_vector tsvector GENERATED ALWAYS AS (to_tsvector('english', excerpt)) STORED
);

CREATE INDEX evidence_search_idx ON evidence USING GIN (search_vector);

CREATE TABLE memory_fact (
  id uuid PRIMARY KEY,
  tenant_id uuid NOT NULL,
  subject_id uuid NOT NULL,
  predicate text NOT NULL,
  value jsonb NOT NULL,
  status text NOT NULL CHECK (status IN ('proposed', 'accepted', 'rejected', 'superseded')),
  evidence_id uuid REFERENCES evidence(id),
  valid_from timestamptz,
  valid_until timestamptz
);

This is an illustrative starting point, not a schema from a production system. Tenant filtering, access-control enforcement, retention, provenance requirements, and conflict resolution must match the product. The status field makes the trust transition explicit: model output can be proposed without silently becoming accepted state.

Retrieval should receive the workspace scope from the authenticated request context, never from model-generated arguments. A simplified lexical candidate query makes the boundary visible:

SELECT id, excerpt, source_uri, observed_at
FROM evidence
WHERE tenant_id = :authenticated_tenant_id
  AND search_vector @@ websearch_to_tsquery('english', :query)
ORDER BY ts_rank(search_vector, websearch_to_tsquery('english', :query)) DESC
LIMIT :bounded_k;

This sketch assumes the caller has already resolved membership and that the database role cannot cross tenant boundaries. Production code still needs parameterized queries, row-level access rules where appropriate, lifecycle and document ACL filters, and a tested policy for stale or deleted sources. Ranking happens only after authorization filtering.

For search, combine exact identifiers and full-text matching with semantic retrieval when the query needs it. PostgreSQL supports text-search vectors and indexes; an index improves repeated search at the cost of write and storage overhead, so validate the query plan against the workload instead of adding one by habit.

What this looks like in a professional-memory system

The ProfMe implementation makes the layers above concrete. Meeting sessions and transcript references provide evidence; extraction creates structured entities such as people, commitments, decisions, projects, and topics; lifecycle state distinguishes current from superseded or resolved information; retrieval combines lexical and semantic paths. SQLite and PostgreSQL adapters sit behind the professional-memory API, so product code can depend on the domain boundary instead of taking a dependency on one store’s schema.

source session ──► provenance reference ──► extracted memory candidate
                                              │
                                              ▼
                                  validate / review / ingest
                                              │
                 ┌────────────────────────────┴──────────────────────────┐
                 ▼                                                       ▼
        canonical state + lifecycle                         search representations
        (active / superseded / resolved)                     (lexical + semantic)
                 └────────────────────────────┬──────────────────────────┘
                                              ▼
                              scope-aware context for a task

The source material matters because this architecture has real limits. A local synthetic retrieval report recorded hybrid Recall@5 of 1.0 across nine cases, while top-1 citation correctness was 7/9; answer faithfulness was not measured. That tells me the retrieval path can find relevant fixtures in that test, and that citation correctness still needs separate attention. It says nothing about arbitrary users’ complete histories, production latency, or answer quality in the field.

The broader lesson is to make freshness and trust first-class. Retrieval should filter by workspace and lifecycle before ranking; a source reference should travel with the candidate; a correction should update canonical state without erasing the prior event. A vector database can help find semantically related material, but it cannot supply those product semantics on its own.

Limits and further reading

Retrieval quality is bounded by ingestion quality, authorization filters, freshness, and the way relevance is evaluated. A vector score is not confidence that a fact is true. Treat recalled content as evidence with provenance, and decide separately whether it is allowed to update canonical state.

• PostgreSQL full text search
• PostgreSQL text-search indexes
• LangGraph persistence and checkpointing