PCP Data Model

Overview

Personal Context Protocol uses PostgreSQL via Neon. This document describes the schema, relationships, and constraints.

Design principles

1. Immutable messages: Messages are never updated or deleted

2. Scoped tokens: Each session token is bound to exactly one session

3. Audit trail: Every action is logged as an event

4. Stable IDs: IDs use prefix_timestamp_id format for readability

5. Soft deletes: Archive/revoke flags instead of hard deletes

Tables

app_instance

Single-row table tracking the application instance.

Column	Type	Description
id	TEXT	Always instance_1
created_at	TIMESTAMPTZ	Instance creation time
initialized_at	TIMESTAMPTZ	When setup was completed
version	TEXT	Schema version

Constraints:

• Primary key: id

• Always exactly one row

Example:

{
  "id": "instance_1",
  "created_at": "2026-06-22T22:45:00Z",
  "initialized_at": "2026-06-22T22:50:00Z",
  "version": "0.1.15"
}

ui_auth

Stores the UI admin token hash.

Column	Type	Description
id	TEXT	Always ui_1
token_hash	TEXT	Salted hash of UI token
salt	TEXT	Salt for hashing
source	TEXT	Credential owner: deploy, env, or user
created_at	TIMESTAMPTZ	Token creation time
updated_at	TIMESTAMPTZ	Last rotation time
last_used_at	TIMESTAMPTZ	Last successful auth

Constraints:

• Primary key: id

• Always exactly one row

Source / rotation: deploy tokens are auto-generated and rotated on every deploy (the previous one stops working); env tokens come from PCP_ADMIN_TOKEN and are reconciled each deploy; user tokens are set in Settings and never auto-rotated. Precedence is environment > user > deploy. The default for any credential not explicitly set in Settings (including pre-existing ones) is deploy, so it rotates each deploy until the admin sets their own token or PCP_ADMIN_TOKEN.

Note: The raw UI token is shown only during setup, never stored.

topics

Human-managed topics/categories for organizing sessions.

Column	Type	Description
id	TEXT	topic_<timestamp>_<id>
title	TEXT	Human-readable title
description	TEXT	Optional description
archived	BOOLEAN	Soft delete flag
created_at	TIMESTAMPTZ	Creation time
updated_at	TIMESTAMPTZ	Last update time

Constraints:

• Primary key: id

• Unique: title (where archived = false)

• Index: archived

AI access: AI tokens CANNOT create, rename, delete, or archive topics.

sessions

AI recording sessions, optionally within a topic.

Column	Type	Description
id	TEXT	ses_<timestamp>_<id>
topic_id	TEXT	Parent topic (foreign key, nullable: NULL = uncategorized)
title	TEXT	Session title (may be suggested by AI)
mode	TEXT	Recording mode the agent is told to honor: wild (default) or exact
public	BOOLEAN	When true, a read-only view is available without the admin token
archived	BOOLEAN	Soft delete flag
created_at	TIMESTAMPTZ	Creation time
updated_at	TIMESTAMPTZ	Last update time
last_message_at	TIMESTAMPTZ	Last message timestamp

Constraints:

• Primary key: id

• Foreign key: topic_id → topics.id (nullable)

• Index: topic_id

• Index: archived

Uncategorized: a session may have no topic (topic_id NULL). The UI groups these under “Uncategorized”. Title uniqueness is scoped to the group — sessions under the same topic, or all topic-less sessions.

Public sharing: when public is true, anyone can read the session and its messages via GET /api/v1/public/sessions/:id and the /s/:id page, without the admin UI token. Private (default) sessions return 404 from that endpoint so their existence is not leaked. Tokens are never exposed on the public surface.

Recording mode: wild (default) tells the agent it may redact secrets it judges unsafe; exact tells it to record verbatim (including credentials) for faithful task migration. The mode is surfaced to the agent in the protocol descriptor (recording_mode + recording_guidance) and the generated instruction. It is guidance to the agent, not a server-side filter.

AI access: AI can only view/append to sessions for which it has a valid token.

session_tokens

Scoped tokens for AI agents.

Column	Type	Description
id	TEXT	tok_<timestamp>_<id>
session_id	TEXT	Bound session (foreign key)
token_hash	TEXT	Salted hash of token
salt	TEXT	Salt for hashing
name	TEXT	Human-readable name (e.g., “Claude session”)
can_rename_session	BOOLEAN	Allow session title suggestions
revoked	BOOLEAN	Soft revoke flag
created_at	TIMESTAMPTZ	Creation time
expires_at	TIMESTAMPTZ	Expiry; NULL means never expire
last_used_at	TIMESTAMPTZ	Last successful use
token_prefix	TEXT	First 16 chars for fast lookup

Expiration: Tokens may be created with expires_in of 1h, 24h, 7d (default), 30d, or never. never stores a NULL expires_at; tokens created before expiration existed also have NULL and are treated as never-expiring. Expired tokens are rejected at authentication time. Status is derived as active, expired, or revoked.

Constraints:

• Primary key: id

• Foreign key: session_id → sessions.id

• Index: session_id

• Index: token_hash (for lookup)

• Index: revoked

Capabilities:

• Always: append messages to bound session

• If can_rename_session = true: suggest session title

• Never: access other sessions, manage topics, delete messages

Note: Raw token is shown only at creation, never stored.

messages

Immutable conversation messages.

Column	Type	Description
id	TEXT	msg_<timestamp>_<id>
session_id	TEXT	Parent session (foreign key)
topic_id	TEXT	Denormalized topic (foreign key, nullable; NULL for uncategorized sessions)
ordinal	INTEGER	Order within session (1, 2, 3…)
role	TEXT	user, assistant, system, tool, correction
content	TEXT	Message content (markdown/text)
content_type	TEXT	text, markdown, json
provider	TEXT	AI provider (e.g., “anthropic”, “openai”)
base_model	TEXT	Model name (e.g., “claude-3-5-sonnet”)
provider_timestamp	TIMESTAMPTZ	Provider’s timestamp (if available)
observed_at	TIMESTAMPTZ	When recorded locally
source_json	JSONB	Raw provider response (optional)
metadata_json	JSONB	Extra metadata
created_at	TIMESTAMPTZ	Insertion time

Constraints:

• Primary key: id

• Foreign key: session_id → sessions.id

• Foreign key: topic_id → topics.id

• Unique: (session_id, ordinal)

• Index: session_id

• Index: topic_id

• Index: role

Immutability: Messages are append-only for AI tokens — an agent can only append; it can never update or delete, and AI corrections are new messages. A human admin (UI token) may edit or delete a message to fix a wrongly-recorded or wrongly-imported entry (PATCH/POST .../messages/delete), which is audited (message.edited, message.deleted). Deletes are hard deletes; ordinal gaps are fine because new appends use max(ordinal) + 1.

Content:

• Store pure text/markdown only

• If provider timestamp unavailable, use observed_at and "unknown" for provider/model

• Always populate observed_at

Roles:

• user: Human message

• assistant: AI message

• system: System instructions

• tool: Tool response

• correction: Correction to previous message

compactions

Durable session summaries stored alongside raw messages, for when full message upload is impossible. Compactions are additional records and never replace messages.

Column	Type	Description
id	TEXT	cmp_<timestamp>_<id>
session_id	TEXT	Parent session (foreign key)
summary	TEXT	Required summary text
timeline_json	JSONB	Optional timeline
decisions_json	JSONB	Optional decisions
requirements_json	JSONB	Optional requirements
open_questions_json	JSONB	Optional open questions
artifacts_json	JSONB	Optional artifacts
warnings_json	JSONB	Optional warnings
provider	TEXT	AI provider
base_model	TEXT	Model name
created_at	TIMESTAMPTZ	Insertion time
metadata_json	JSONB	Extra metadata

Constraints:

• Primary key: id

• Foreign key: session_id → sessions.id

• Index: session_id

events

Audit trail for all actions.

Column	Type	Description
id	TEXT	evt_<timestamp>_<id>
session_id	TEXT	Related session (nullable)
topic_id	TEXT	Related topic (nullable)
action	TEXT	Action type
actor	TEXT	Who performed action (human, ai:<session_id>)
details_json	JSONB	Action details
created_at	TIMESTAMPTZ	When action occurred

Constraints:

• Primary key: id

• Index: session_id

• Index: topic_id

• Index: action

• Index: created_at

Actions:

• session.created

• session.renamed

• session.archived

• token.created

• token.revoked

• message.appended

• message.edited

• message.deleted

• compaction.recorded

• token.revoked

• token.renamed

• session.shared

• session.unshared

• topic.created

• topic.renamed

• topic.archived

• user.authenticated

schema_migrations

Track applied migrations.

Column	Type	Description
version	TEXT	Migration version (e.g., “001_initial”)
applied_at	TIMESTAMPTZ	When applied
checksum	TEXT	File checksum for validation

Constraints:

• Primary key: version

ID format

All IDs use prefix_<timestamp>_<random> format:

Prefix	Table
instance_	app_instance
ui_	ui_auth
topic_	topics
ses_	sessions
tok_	session_tokens
msg_	messages
cmp_	compactions
evt_	events

Example: topic_1719099900_a3f2b1c4

This format:

• Is human-readable

• Sorts chronologically

• Doesn’t reveal database internals (unlike sequential integers)

• Is unique across tables

Relationships

app_instance (1) ──┐
ui_auth (1)        │
                   │
topics (N) ───┐    │
              │    │
              │    │
              └─┐  │
                │  │
sessions (N) ───┼──┘
                │
                ├── session_tokens (N)
                │
                ├── messages (N)
                │
                └── events (N)

Indexes

Performance-critical indexes

1. messages(session_id, ordinal) - Sequential message retrieval

2. session_tokens(token_hash) - Token validation (lookup by hash)

3. sessions(topic_id, created_at DESC) - Topic session listing

4. events(session_id, created_at DESC) - Event timeline

Composite indexes

• messages(topic_id, created_at DESC) - Topic-level message search

• session_tokens(session_id, revoked) - Session token lookup

Migrations

See docs/migrations.md.

Rules:

• Never drop tables or columns in v0.1

• Never delete data

• Always backward-compatible

• Test on fresh DB before deployment

Security considerations

Token storage

• Never store raw tokens

• Always store salted hashes (Argon2 or bcrypt)

• Use per-token random salts

• Never log or return tokens after creation

Message content

• Store as plain text/markdown

• No encryption in v0.1

• Provider secrets never enter database

• User data is trusted (single-user app)

Access control

• Session tokens are scoped by foreign key

• Token validation checks session match

• Topic routes reject AI tokens (403)

Example queries

Get all messages for a session (chronological)

SELECT * 
FROM messages 
WHERE session_id = 'ses_1719099900_xxx'
ORDER BY ordinal ASC;

Get session with topic info (admin view)

SELECT s.*, t.title as topic_title
FROM sessions s
JOIN topics t ON s.topic_id = t.id
WHERE s.id = 'ses_1719099900_xxx';

Validate session token

SELECT id, session_id, can_rename_session
FROM session_tokens
WHERE token_hash = $1 AND revoked = false;

Get recent events

SELECT * 
FROM events 
WHERE session_id = $1
ORDER BY created_at DESC
LIMIT 50;

Version history

v0.1.0 (initial)

• All tables listed above

• Soft deletes for topics, sessions, tokens

• Immutable messages

• Audit events

• Migration tracking

v0.1.3 (agent recording URL)

• Added compactions table for durable session summaries

• Session access tokens gained expiration choices (expires_at, NULL = never)

• Agent recording-URL + token model with public protocol discovery

v0.1.4 (admin token rotation)

• Added ui_auth.source (deploy | env | user) for per-deploy rotation

v0.1.5 (uncategorized sessions)

• sessions.topic_id and messages.topic_id made nullable (NULL = no topic)

• ChatGPT-style nav: collapsible topic groups with nested sessions

v0.1.6 (token rotation default fix)

• Corrected the 003 backfill: credentials not set in Settings default to deploy and rotate each deploy (migration 005)

v0.1.7 (public sharing + token revocation)

• Added sessions.public for read-only public sharing (migration 006)

• Token revocation API + dashboard modal; recording URL built from the page origin

v0.1.8 (human fallback import)

• POST /api/v1/sessions/:id/import records a pasted agent fallback block, skipping messages already present (dedup by role + content)

v0.1.9 (recording modes + token rename)

• Added sessions.mode (wild default / exact, migration 007) surfaced to the agent; token rename via PATCH; auto-distinct token names on creation

v0.1.10 (message correction)

• Human admin may edit/delete messages (AI stays append-only); audited message.edited / message.deleted

v0.1.15 (compaction rendering + mixed ingest + hardening)

• Compactions rendered as first-class content; <PCP_INGEST> / mixed ingest records messages + a compaction; shared config validator; API doc-drift test