Inside Personal Memory — MCP, OAuth, PostgreSQL, and pgvector for Cross-AI Long-Term Memory (Part 2)

Part 1: I stopped re-explaining myself to every AI explained why I wanted one memory shared by ChatGPT, Claude, Codex, and Claude Code, how I chose the right level of detail, and how shared context changed the advice I receive. This second part covers the implementation: an MCP server, OAuth with PKCE, PostgreSQL plus pgvector, temporal revisions, hybrid retrieval, historical conversation backfill, and the failures I encountered along the way.

0. The complete architecture first

Personal Memory is not another chat service with a model inside it. The server is responsible for storage, retrieval, authentication, and consistency. The connected AI client decides what is worth remembering, which memories matter to the current question, and what case record should be derived from a conversation.

The service runs on Firebase Functions v2, but Firestore is not the source of truth; PostgreSQL on Cloud SQL is. The code lives in the same repository as my portfolio backend, while the Firebase/GCP project, Functions codebase, service account, secrets, Cloud SQL instance, Hosting site, and billing are isolated under yudai-personal-memory. A normal portfolio deployment should never accidentally redeploy the memory backend.

1. Why PostgreSQL plus pgvector

I considered Firestore, Cloudflare Workers, Google Drive, and Graphiti. Vector search was only one reason I chose PostgreSQL plus pgvector:

1. I wanted explicit relationships among memories, revisions, evidence, and public projections
2. I needed transactional optimistic locking with expectedRevision
3. I wanted full-text, trigram, and vector retrieval over one source of truth
4. I wanted to learn SQL and turn the project into credible portfolio experience
5. The data is large for one person, but nowhere near the scale that requires a distributed graph database

pgvector alone does not create a memory system. It finds records with semantically similar representations. Time, revisions, verbatim evidence, publication boundaries, and protection against resurrecting deleted data all require separate data structures and policies.

2. Data model: separate the current synthesis from history

These are the main layers:

memories is the readable present. revisions and evidence form the ledger that preserves the past. search_projections is derived data optimized for retrieval. Keeping the roles separate means I can compress an index without deleting source evidence.

What a case record looks like

The actual payload is richer than a short key-value profile:

{
  "title": "PII handling policy for portfolio screenshots",
  "memoryKind": "episode",
  "canonicalSummaryJa": "A detailed Japanese record containing context, concerns, alternatives, rationale, result, and unresolved questions",
  "aliases": [
    "スクリーンショットの個人情報",
    "portfolio screenshot pii",
    "why not blur"
  ],
  "evidence": [
    {
      "language": "ja",
      "text": "A high-information verbatim statement actually made by the owner",
      "source": "stable conversation message key"
    }
  ],
  "revision": 4,
  "validFrom": "2026-08-01T00:00:00.000Z"
}

The embedding input uses the canonical summary, title, aliases, tags, and source metadata. Raw evidence is not sent to the embedding provider.

3. Authentication: a public MCP URL does not make the memories public

ChatGPT on the web, Claude on the web, and local CLIs use different callback URIs. Instead of pasting one static token into every client, I implemented OAuth Authorization Code with S256 PKCE.

The boundaries are explicit:

• callbacks are limited to exact Claude, ChatGPT, loopback CLI, and configured operator URIs
• access tokens are opaque and expire after 15 minutes
• refresh tokens rotate and expire after one year of inactivity
• reuse of an old refresh token revokes the entire token family
• normal use refreshes the session, so I do not enter an owner secret every time
• /mcp never accepts a Google token or owner secret directly
• tool arguments, memory text, search queries, tokens, and redirect headers are excluded from application logs

My first version asked me to paste an owner secret into the authorization page. It was enough to prove the connection, but “enter credentials every time” was not a usable daily workflow. Google sign-in now handles the human approval step; the authorization server then issues Personal Memory-specific tokens.

Scopes are separate too. Reading, proposing, writing private data, deleting, and publishing are not the same permission. The authorization page shows exactly which scopes a client is requesting.

4. Retrieval: index → hybrid search → summary → evidence

The initial load_context returned every detailed body. At 100 records it produced roughly 535 KB, or 260,000 characters, so I changed retrieval to index-first.

Japanese does not delimit every word with spaces, so PostgreSQL's standard full-text search is not enough. I use Intl.Segmenter to extract Japanese and English search tokens for FTS and trigram/substring matching, then add a 768-dimensional embedding search. Reciprocal Rank Fusion combines the result lists.

I intentionally give lexical evidence slightly more weight. In personal memory, an exact project name, person, or phrase is often stronger evidence than broad semantic similarity. Vector search and bilingual aliases become especially useful when the source was recorded in English but the later question is in Japanese, or vice versa.

5. Writes: keep model judgment separate from server truth

A write is not a blind upsert.

The normal flow is:

1. Find a related topic with load_context or search_memory
2. Do nothing if the new information is materially identical
3. Use propose_memory for a new topic
4. For an append, include targetMemoryId and the current expectedRevision
5. Commit only after policy and revision checks pass

Ordinary private create, evidence, transition, and closure writes can be committed under the owner's standing approval. A correction, merge, delete, or publish operation requires separate confirmation.

expectedRevision matters because several AIs can update the same record. If one client reads revision 7 and another commits revision 8 while the first is preparing a proposal, that proposal is stale and must be rejected. Silent last-write-wins behavior could replace a detailed case record with a much shorter update.

6. Public projections: separate the data path before debating encryption

For the blog visualization, I did not expose the private database and add a frontend filter. GET /public returns only individually approved projections.

{
  "items": [
    {
      "id": "opaque-public-id",
      "title": "A title edited for publication",
      "summary": "A redacted public summary",
      "category": "achievement",
      "occurredAt": "2026-08-01T00:00:00.000Z",
      "tags": ["personal-memory", "mcp"]
    }
  ]
}

This endpoint does not decrypt or read a private source row. The publication step creates and reviews the exact text that will be public. A separate copy gives mistakes a smaller blast radius than dynamically returning private text based on a visibility flag.

7. Backfilling historical conversations

Saving only future conversations would leave the system empty of the context that made it worthwhile. I built a resumable backfill from official Claude and ChatGPT exports and local Codex sessions.

The exports were not small:

For ChatGPT, a local prefilter removed pure code generation, error-log analysis, API mechanics, minor UI changes, and word-definition chats before spending model usage. A technical conversation stayed when it contained a personal decision, a learning transition, an outcome, or a lesson from failure.

A normal batch contains at most four conversations and 120,000 characters. Every batch writes a result.json, while the runner maintains run-state.json. A restart skips valid completed batches. For a long-running job, protecting completed units mattered more than peak throughput.

8. What broke in practice

The final design looks orderly. Getting there was not.

Failure 1: the summary layer did not actually exist

In the first 100 records, content and canonicalSummaryJa were identical for every item, and load_context returned about 535 KB. A summary in name only is still the full document.

Fix: I separated indexSummary from the canonical case record. The index stays compact, the case record stays detailed, and evidence is loaded only when necessary. Because an index is derived data, refreshing it does not create a semantic revision.

Failure 2: a compatibility test overwrote a long record with one paragraph

While testing ChatGPT Web write compatibility, I accidentally replaced an existing long canonicalSummaryJa with a short compatibility-test paragraph.

Recovery: the immutable revision still contained the detailed version. I combined that revision with the new test result and restored the current synthesis. The revision ledger paid for itself during the first real data-loss incident.

Failure 3: the model “improved” an evidence quote

While reconciling a large update group in chunks, the model lightly polished a quote without changing its meaning. That is acceptable prose, but it is no longer verbatim evidence.

Fix: the model now produces only the semantic case record. Persisted evidence is selected deterministically from the original candidates' exact source and text, and apply-time validation checks that each excerpt appears verbatim in the source message.

Failure 4: parallelism reached rate limits before it delivered speed

Five parallel Claude workers hit the Max plan's five-hour session limit. Eight parallel Codex workers processing the ChatGPT export hit the Personal Memory API limit of 300 requests per 600 seconds and received HTTP 429 responses.

Fix: I preserved every completed result.json, waited for the next rate window, and resumed with concurrency 3—and then 2 when necessary. Reusing completed units mattered far more than squeezing out maximum parallelism.

Failure 5: clients interpreted MCP results differently

A structured result that worked in Claude was rejected by ChatGPT as undeclared structuredContent.

Fix: responses use standard MCP text content and avoid client-specific implicit behavior. A server is not proven compatible until each real client completes load → propose → commit → search again.

Failure 6: I treated “OAuth works once” as finished

The owner-secret prototype connected successfully, but reconnecting could require the credential again.

Fix: short-lived access tokens are paired with rotating refresh tokens and a rolling inactivity window. Authentication is not just a security feature; whether the system remains connected is part of the product experience.

9. What I tested

In addition to unit tests, I ran round trips through the actual clients:

• ChatGPT Web and Claude Web can read the same index count
• a revision committed by one client can be found by the other
• a proposal with a stale expectedRevision is rejected
• private source data cannot appear in GET /public
• forbidden secrets are rejected before proposal, indexing, publishing, migration, or embedding
• materially identical evidence does not create another revision or embedding
• deleted lineage cannot be resurrected by a delayed job
• OAuth callback allowlisting, PKCE, scopes, token rotation, and refresh-token reuse revocation work

The most meaningful user test was not “does it know a record ID?” It was: can it explain a previous decision without guessing, including the rationale and open questions? Can it combine several records while clearly separating retrieved fact from its own inference?

10. Cost and operations

Cloud SQL is the main always-on cost in this design. Functions use minInstances=0, and the request volume is small for personal use. I designed around a $20 monthly ceiling, but the actual amount depends on the Cloud SQL instance, storage, backups, region, and embedding usage; it is not a fixed-price system.

For strictly local personal use, SQLite plus a local MCP server—or even Markdown plus an index—may be enough. I chose Cloud SQL because I wanted one endpoint available from multiple machines and web clients, and because I wanted hands-on experience with PostgreSQL, OAuth, migrations, and operations.

Operationally, I keep the following boundaries:

• the GCP project and service account are separate from the main portfolio deployment
• database runtime and migration-operator privileges are separate
• temporary administrative access exists only during a migration and is removed afterward
• private text, queries, vectors, and credentials are excluded from logs
• a budget alert is configured
• exports and job artifacts stay outside the repository with owner-only permissions

11. What I would do differently next time

If I rebuilt it from scratch, I would change the order:

1. Define “raw transcript,” “case record,” and “index summary” as different concepts on day one
2. Complete one create, append, stale update, and history restore end to end
3. Connect real clients one at a time using only standard MCP text responses
4. Start backfill last, with checkpoints and a source ledger from the beginning
5. Measure API and model rate limits before increasing concurrency
6. Never implement public projections as a filter added on top of the private database

I would still avoid calling a model inside Cloud Functions to “automatically summarize everything.” It would require a deployment for every prompt change and duplicate intelligence already present in the client. The server should be the anchor for facts; the client should be the layer that interprets them.

12. Conclusion

The center of a long-term memory system is not the vector database.

The hard parts are the boundaries:

• what to preserve and what to discard
• how to keep both source evidence and a compressed representation
• how to distinguish a current state from a past one
• how to separate model inference from facts I actually stated
• how to distinguish automatic private saving from destructive actions that need review
• how to separate a private source of truth from a public narrative

MCP, PostgreSQL, pgvector, and OAuth are components that implement those boundaries. The result is not an AI that “remembers everything.” It is a small personal data system that retrieves the right parts of the past with evidence and returns change as history.

← Part 1: I stopped re-explaining myself to every AI