Intermediate
stage1_outputs (SQLite)
Per-session, machine-managed, queryable, gated. The pipeline's working set — fast to scan by recency, cheap to re-derive.
Deep dive · 1 of 2
How Codex remembers.
Codex's memory engine fits in two tables. It is not a transcript and not a vector blob — it's a processing pipeline with a job ledger. That framing is the entire reason it's worth copying.
This is a teardown of a real export — the schema, the extraction format, the promotion gate, and the lease mechanics — pulled straight from a live memories_1.sqlite. It's the technical companion to the portable-memory vision; if you want the head-to-head with Claude Code, jump to the comparison.
Most "agent memory" is a pile of text someone hopes the model re-reads. Codex's is a pipeline: extract, gate, promote, consolidate — each step a tracked, retryable job.
01 / 06 — the shape
A ledger, not an archive.
The database is small on purpose. It doesn't store your whole history — the raw session logs live on disk (gigabytes of them). What the DB holds is the distillation and the bookkeeping: one structured row per session, and a row per unit of background work. Memory is something the system continuously processes toward, not a bucket it dumps into.
The pipeline
raw session logs (8.5 GB on disk, not in the DB)
│
▼ stage-1 extraction per session ── job: memory_stage1
stage1_outputs row
│
▼ selected_for_phase2 = 1 ?
global consolidation ── job: memory_consolidate_global
│
▼
versioned MEMORY.md + memory_summary.md (the owned memory)
02 / 06 — the tables
Two tables do all the work.
That's the whole schema (plus a migrations table). One holds distilled sessions; one is the ledger that drives every worker. Tab between them.
One row per processed session. Keyed by thread_id. The two payload columns — raw_memory and rollout_summary — are the distillation; selected_for_phase2 is the promotion gate; usage_count / last_usage track reuse so hot memories surface.
create table stage1_outputs ( thread_id text primary key, source_updated_at integer not null, -- source session watermark raw_memory text not null, -- structured markdown (see anatomy) rollout_summary text not null, -- narrative recap rollout_slug text, generated_at integer not null, usage_count integer, last_usage integer, selected_for_phase2 integer not null default 0, selected_for_phase2_source_updated_at integer ); create index idx_stage1_source_updated on stage1_outputs(source_updated_at desc, thread_id desc);
A durable work ledger. Every background task — extract a session, consolidate globally — is a leased, retryable row. This is the reliability spine most homemade memory systems forget entirely.
create table jobs ( kind text not null, -- memory_stage1 | memory_consolidate_global job_key text not null, -- thread_id, or 'global' status text not null, -- done|pending|running|error worker_id text, ownership_token text, -- the lease holder started_at integer, finished_at integer, lease_until integer, -- lock expiry for running work retry_at integer, retry_remaining integer not null, last_error text, input_watermark integer, last_success_watermark integer, primary key (kind, job_key) ); create index idx_jobs_kind_status_retry_lease on jobs(kind, status, retry_at, lease_until);
Two access patterns fall out of those indexes: pull recent memory by source timestamp, and find runnable jobs by kind, status, retry-time and lease state. Boring, fast, correct.
03 / 06 — the anatomy
Inside a raw_memory.
The promotion gate is only as good as what it's promoting. Codex's extraction isn't free text — it's a disciplined format: YAML-ish frontmatter, then per-task blocks split into preference signals, reusable knowledge, and failures and how to do differently. Here's a real one, lightly trimmed.
stage1_outputs.raw_memory — actual export
--- description: The user wanted the OB1 front end found, then debugged, then reframed as a standalone Next.js site that pulls canonical content from the real OB1 GitHub repo... task: investigate OB1 front end, start local dev, draft standalone spec task_group: nate-website-and-ob1-directory task_outcome: success cwd: /Users/.../Nates Website keywords: OB1, Next.js, Notion, GitHub source of truth, README-to-HTML --- ### Task 1: Find OB1 front end in repo Preference signals: - when asking whether the front end was "still in the repo somewhere", the user wanted evidence-based repo discovery, not guesswork -> verify from filesystem and git history before answering Reusable knowledge: - OB1 surface lives under `Nates Website/src/pages/ob1` + `/components/ob1` - git history: `c279194 feat: /ob1 — the Open Brain directory` Failures and how to do differently: - early answers conflated "OB1 repo content" with "website surface" -> separate them
Notice what's encoded: not just what happened, but how the user likes to be worked with and what to do differently next time. That's portable across any agent — which is exactly why it's the model for a shared layer.
04 / 06 — the ledger in motion
The reliability spine, caught live.
The job ledger isn't decoration — it's load-bearing. When I opened this export, the jobs table was mid-flight: most extractions done, a handful failed and queued for retry, the global consolidation pending. Nothing swept under the rug.
jobs table · live snapshot from the export
Those five errors carry a last_error, a retry_at, and a decrementing retry_remaining — so a worker picks them up later instead of losing them. The lease_until token means two workers never grab the same job, and the input_watermark / last_success_watermark pair means a restart resumes exactly where it stopped. This is the difference between a memory system that survives a crash and one that quietly forgets.
05 / 06 — the owned memory
What promotion produces.
Sessions flagged selected_for_phase2 feed a global consolidation job, whose output is human-readable and version-controlled (the memory folder is a git repo): a MEMORY.md index plus a memory_summary.md with a User Profile, explicit preferences, and topic-scoped "what's in memory" entries.
Durable
MEMORY.md (git)
The promoted, consolidated, owned memory. Human-readable, diffable, portable — the artifact a person actually trusts and edits.
That split is the quiet genius: the SQLite layer is the ledger and scratch space; the markdown is the owned truth. Machines write the first; the second is curated and versioned.
06 / 06 — the verdict
What's brilliant, what's missing.
✓ Pipeline framing
Memory as extract → gate → consolidate, not a dumping ground.
✓ Promotion gate
selected_for_phase2 keeps the durable layer curated.
✓ Job ledger
Leases, retries, watermarks — crash-safe by construction.
✓ Versioned output
The owned memory is git, not a black box.
– Single-vendor
It's Codex's, on one disk. No other agent can read it.
– No provenance
No fact/inference status — a guess and a verified fact look identical.
– No scope
No global/project/client partition on memory items.
– No semantic recall
Recency-indexed, not embedding-indexed.
The gaps are exactly the extensions a portable Supabase layer adds: a provenance spine (our edge), scope, and pgvector recall — on top of Codex's pipeline-and-ledger bones.