Joey Yakimowich-Payne
7c6a3dbe4a
Ultraworked with [Sisyphus](https://github.com/code-yeongyu/oh-my-opencode) Co-authored-by: Sisyphus <clio-agent@sisyphuslabs.ai>
16 KiB
16 KiB
Object Store Schema Design
Database: PostgreSQL 16 + pgvector
1. Core Tables
1.1 sessions
Tracks proxy sessions (one per opencode session).
CREATE TABLE sessions (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
external_id TEXT UNIQUE NOT NULL, -- opencode session ID
model TEXT NOT NULL, -- primary model (e.g., claude-opus-4)
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
last_active_at TIMESTAMPTZ NOT NULL DEFAULT now(),
total_turns INTEGER NOT NULL DEFAULT 0,
total_objects INTEGER NOT NULL DEFAULT 0,
total_faults INTEGER NOT NULL DEFAULT 0,
total_micro_faults INTEGER NOT NULL DEFAULT 0,
status TEXT NOT NULL DEFAULT 'active', -- active, completed, abandoned
config JSONB NOT NULL DEFAULT '{}' -- session-level config overrides
);
CREATE INDEX idx_sessions_external ON sessions(external_id);
CREATE INDEX idx_sessions_active ON sessions(status) WHERE status = 'active';
1.2 semantic_objects
The central table. Every piece of context is a semantic object.
CREATE EXTENSION IF NOT EXISTS vector;
CREATE TABLE semantic_objects (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
session_id UUID NOT NULL REFERENCES sessions(id) ON DELETE CASCADE,
-- Identity
object_type TEXT NOT NULL, -- conversation_phase, design_decision, debugging_session,
-- file_context, tool_result, plan, error_context,
-- external_reference
source_tool TEXT, -- tool that generated this (Read, Bash, Grep, etc.)
source_key TEXT, -- dedup key (e.g., file path for Read results)
-- Multi-fidelity content
content_full TEXT NOT NULL, -- L0: complete original content
summary_detailed TEXT, -- L1: ~30% of original
summary_compact TEXT, -- L2: ~5% of original
stub TEXT NOT NULL, -- L3: one-line description (always present)
-- Declared losses (per fidelity level)
losses_l1 JSONB DEFAULT '[]', -- what L1 dropped vs L0
losses_l2 JSONB DEFAULT '[]', -- what L2 dropped vs L1
can_answer_l1 JSONB DEFAULT '[]', -- what L1 can answer
can_answer_l2 JSONB DEFAULT '[]', -- what L2 can answer
fault_when JSONB DEFAULT '[]', -- when to fault (hints for model)
-- Key entities extracted during summarization
key_entities JSONB DEFAULT '[]', -- file paths, function names, etc.
tags TEXT[] DEFAULT '{}', -- freeform tags for filtered queries
-- State
current_fidelity INTEGER NOT NULL DEFAULT 0, -- 0=L0, 1=L1, 2=L2, 3=L3, 4=evicted
pinned BOOLEAN NOT NULL DEFAULT false, -- fault-driven pin
pin_reason TEXT, -- why pinned (fault hash, anchor tag, etc.)
-- Metrics
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
last_accessed TIMESTAMPTZ NOT NULL DEFAULT now(),
access_count INTEGER NOT NULL DEFAULT 0,
fault_count INTEGER NOT NULL DEFAULT 0, -- times model faulted on this object
micro_fault_count INTEGER NOT NULL DEFAULT 0, -- times model queried without restore
-- Size tracking (for pressure calculations)
tokens_l0 INTEGER NOT NULL DEFAULT 0, -- token count at each level
tokens_l1 INTEGER,
tokens_l2 INTEGER,
tokens_l3 INTEGER NOT NULL DEFAULT 0,
-- Source message range (which messages this object was segmented from)
source_turn_start INTEGER, -- first user turn index
source_turn_end INTEGER, -- last user turn index
-- Embedding for semantic search
embedding vector(384) NOT NULL -- all-MiniLM-L6-v2 = 384 dimensions
);
-- Primary access patterns
CREATE INDEX idx_objects_session ON semantic_objects(session_id);
CREATE INDEX idx_objects_session_fidelity ON semantic_objects(session_id, current_fidelity);
CREATE INDEX idx_objects_session_type ON semantic_objects(session_id, object_type);
CREATE INDEX idx_objects_source_key ON semantic_objects(session_id, source_key)
WHERE source_key IS NOT NULL;
CREATE INDEX idx_objects_created ON semantic_objects(session_id, created_at);
CREATE INDEX idx_objects_last_accessed ON semantic_objects(session_id, last_accessed);
CREATE INDEX idx_objects_tags ON semantic_objects USING GIN(tags);
-- Vector similarity search (IVFFlat for speed, switch to HNSW at scale)
CREATE INDEX idx_objects_embedding ON semantic_objects
USING ivfflat (embedding vector_cosine_ops) WITH (lists = 100);
-- Full-text search on content
CREATE INDEX idx_objects_content_fts ON semantic_objects
USING GIN(to_tsvector('english', content_full));
1.3 object_relationships
Typed edges between semantic objects.
CREATE TABLE object_relationships (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
session_id UUID NOT NULL REFERENCES sessions(id) ON DELETE CASCADE,
source_id UUID NOT NULL REFERENCES semantic_objects(id) ON DELETE CASCADE,
target_id UUID NOT NULL REFERENCES semantic_objects(id) ON DELETE CASCADE,
relationship TEXT NOT NULL, -- parent_of, caused_by, references, supersedes, depends_on
metadata JSONB DEFAULT '{}',
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
UNIQUE(source_id, target_id, relationship)
);
CREATE INDEX idx_rels_source ON object_relationships(source_id);
CREATE INDEX idx_rels_target ON object_relationships(target_id);
CREATE INDEX idx_rels_session ON object_relationships(session_id);
1.4 fault_history
Records every fault for pinning decisions and analytics.
CREATE TABLE fault_history (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
session_id UUID NOT NULL REFERENCES sessions(id) ON DELETE CASCADE,
object_id UUID NOT NULL REFERENCES semantic_objects(id) ON DELETE CASCADE,
fault_type TEXT NOT NULL, -- full_restore, micro_fault, entropy_triggered
turn_number INTEGER NOT NULL,
content_hash TEXT NOT NULL, -- hash of content at time of eviction
question TEXT, -- for micro_faults: the question asked
answer TEXT, -- for micro_faults: the answer returned
answer_tokens INTEGER, -- tokens in the micro-fault answer
avoided_tokens INTEGER, -- tokens saved vs full restore
latency_ms INTEGER, -- time to handle the fault
created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE INDEX idx_faults_session ON fault_history(session_id);
CREATE INDEX idx_faults_object ON fault_history(object_id);
CREATE INDEX idx_faults_content_hash ON fault_history(content_hash);
1.5 fidelity_transitions
Audit log of every fidelity change (for analytics and debugging).
CREATE TABLE fidelity_transitions (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
session_id UUID NOT NULL REFERENCES sessions(id) ON DELETE CASCADE,
object_id UUID NOT NULL REFERENCES semantic_objects(id) ON DELETE CASCADE,
from_fidelity INTEGER NOT NULL,
to_fidelity INTEGER NOT NULL,
trigger TEXT NOT NULL, -- pressure, access, fault, cooperative, goal_change
turn_number INTEGER NOT NULL,
pressure_zone TEXT, -- normal, caution, warning, critical, emergency
token_count INTEGER, -- total tokens at time of transition
created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE INDEX idx_transitions_session ON fidelity_transitions(session_id);
CREATE INDEX idx_transitions_object ON fidelity_transitions(object_id);
1.6 admission_scores
Records admission decisions for tuning the scorer.
CREATE TABLE admission_scores (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
session_id UUID NOT NULL REFERENCES sessions(id) ON DELETE CASCADE,
object_id UUID REFERENCES semantic_objects(id) ON DELETE SET NULL, -- NULL if rejected
admitted BOOLEAN NOT NULL,
score_total REAL NOT NULL,
score_type REAL NOT NULL,
score_novelty REAL NOT NULL,
score_utility REAL NOT NULL,
score_recency REAL NOT NULL,
threshold REAL NOT NULL,
content_preview TEXT, -- first 200 chars (for debugging rejected items)
created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE INDEX idx_admission_session ON admission_scores(session_id);
2. Key Queries
2.1 Context Assembly (every API call)
-- Get all objects for this session, ordered by relevance for context assembly
SELECT
id, object_type, current_fidelity, pinned,
CASE current_fidelity
WHEN 0 THEN content_full
WHEN 1 THEN summary_detailed
WHEN 2 THEN summary_compact
WHEN 3 THEN stub
END AS context_content,
losses_l1, losses_l2, can_answer_l1, can_answer_l2, fault_when,
tokens_l0, tokens_l1, tokens_l2, tokens_l3,
key_entities, tags
FROM semantic_objects
WHERE session_id = $1
AND current_fidelity < 4 -- not fully evicted
ORDER BY
pinned DESC, -- pinned objects first
current_fidelity ASC, -- higher fidelity first
last_accessed DESC -- most recently accessed first
;
2.2 Semantic Search (for micro-faults and goal-aware retrieval)
-- Find objects most relevant to a query (including evicted ones)
SELECT
id, object_type, current_fidelity,
content_full, -- always return full content for micro-fault QA
stub,
key_entities,
1 - (embedding <=> $query_embedding) AS similarity
FROM semantic_objects
WHERE session_id = $1
ORDER BY embedding <=> $query_embedding
LIMIT $2;
2.3 Hybrid Search (vector + full-text)
-- Combine semantic similarity with keyword matching
WITH vector_results AS (
SELECT id, 1 - (embedding <=> $query_embedding) AS vec_score
FROM semantic_objects
WHERE session_id = $1
ORDER BY embedding <=> $query_embedding
LIMIT 20
),
text_results AS (
SELECT id, ts_rank(to_tsvector('english', content_full),
plainto_tsquery('english', $query_text)) AS text_score
FROM semantic_objects
WHERE session_id = $1
AND to_tsvector('english', content_full) @@ plainto_tsquery('english', $query_text)
LIMIT 20
)
SELECT
COALESCE(v.id, t.id) AS id,
COALESCE(v.vec_score, 0) * 0.7 + COALESCE(t.text_score, 0) * 0.3 AS combined_score
FROM vector_results v
FULL OUTER JOIN text_results t ON v.id = t.id
ORDER BY combined_score DESC
LIMIT $2;
2.4 Pressure Calculation
-- Calculate current token pressure for a session
SELECT
SUM(CASE current_fidelity
WHEN 0 THEN tokens_l0
WHEN 1 THEN COALESCE(tokens_l1, 0)
WHEN 2 THEN COALESCE(tokens_l2, 0)
WHEN 3 THEN tokens_l3
ELSE 0
END) AS total_context_tokens,
COUNT(*) FILTER (WHERE current_fidelity = 0) AS objects_at_l0,
COUNT(*) FILTER (WHERE current_fidelity = 1) AS objects_at_l1,
COUNT(*) FILTER (WHERE current_fidelity = 2) AS objects_at_l2,
COUNT(*) FILTER (WHERE current_fidelity = 3) AS objects_at_l3,
COUNT(*) FILTER (WHERE current_fidelity = 4) AS objects_evicted,
COUNT(*) FILTER (WHERE pinned) AS objects_pinned
FROM semantic_objects
WHERE session_id = $1;
2.5 Deduplication Check (for file re-reads)
-- Check if this file was already read (supersedes pattern)
SELECT id, content_full, current_fidelity
FROM semantic_objects
WHERE session_id = $1
AND source_key = $2 -- e.g., file path
AND object_type = 'file_context'
ORDER BY created_at DESC
LIMIT 1;
2.6 Related Objects (for co-fidelity management)
-- When upgrading an object, find related objects that should also upgrade
WITH RECURSIVE related AS (
SELECT target_id AS id, relationship, 1 AS depth
FROM object_relationships
WHERE source_id = $1
AND relationship IN ('depends_on', 'references', 'caused_by')
UNION ALL
SELECT r.target_id, r.relationship, rel.depth + 1
FROM object_relationships r
JOIN related rel ON r.source_id = rel.id
WHERE rel.depth < 2 -- max 2 hops
)
SELECT DISTINCT so.*
FROM related r
JOIN semantic_objects so ON so.id = r.id
WHERE so.current_fidelity > 1; -- only objects that could benefit from upgrade
3. Data Flow Examples
3.1 Model Reads a File
1. Model calls Read(src/auth/middleware.ts)
2. Proxy intercepts response
3. Segmenter creates semantic_object:
- object_type: 'file_context'
- source_tool: 'Read'
- source_key: 'src/auth/middleware.ts'
- content_full: (file contents)
- stub: "Read src/auth/middleware.ts (200 lines, auth middleware)"
- embedding: embed(content_full)
- tokens_l0: count_tokens(content_full)
4. Admission scorer: S(m) = 0.72 (above threshold) -> admitted
5. Dedup check: no existing object with source_key='src/auth/middleware.ts' -> INSERT
(If exists: create new object, add 'supersedes' relationship to old one,
degrade old one to L3)
3.2 Pressure Triggers Fidelity Degradation
1. Pressure Monitor: total_context_tokens = 78,000 (78% of 100K budget)
-> Zone: WARNING
2. Find oldest L0 objects not accessed in last 3 turns:
SELECT id FROM semantic_objects
WHERE session_id = $1 AND current_fidelity = 0
AND last_accessed < (now() - interval '3 turns')
ORDER BY last_accessed ASC;
3. For each candidate:
a. Call Helper LLM to generate L1 summary + declared losses
b. UPDATE semantic_objects SET
summary_detailed = $summary,
losses_l1 = $losses,
can_answer_l1 = $can_answer,
current_fidelity = 1,
tokens_l1 = count_tokens($summary)
WHERE id = $candidate_id;
c. INSERT INTO fidelity_transitions (trigger='pressure', ...)
4. Recalculate pressure. If still in WARNING, degrade L1 -> L2.
3.3 Micro-Fault
1. Model generates: memory_query("What error code for expired tokens?")
2. Proxy intercepts phantom tool call
3. Proxy queries backing store:
SELECT id, content_full
FROM semantic_objects
WHERE session_id = $1
ORDER BY embedding <=> embed("error code expired tokens")
LIMIT 3;
4. Proxy sends to Helper LLM:
"Answer this question using ONLY the provided context:
Q: What error code does auth middleware return for expired tokens?
Context: {top-3 objects' full content}"
5. Helper returns: "The auth middleware returns HTTP 401 with error code
'TOKEN_EXPIRED' and body { error: 'token_expired', message: '...' }"
6. Proxy injects as synthetic tool result (NOT the full object content)
7. INSERT INTO fault_history (fault_type='micro_fault',
answer_tokens=45, avoided_tokens=3200, ...)
8. Object stays at current fidelity (no upgrade)
4. Migration Path
Phase 1 (no DB needed)
Pichay's proxy uses in-memory state + client's message history as backing store. No PostgreSQL required.
Phase 2 (SQLite prototype)
Add SQLite with sqlite-vec for local development:
- Single file, no server
- Same schema, adapted types (TEXT instead of vector, custom cosine function)
Phase 3+ (PostgreSQL)
Full schema as defined above. Migration from SQLite via:
-- Export from SQLite, import to PostgreSQL
-- pgloader or custom Python migration script
Future: Cross-Session Memory (L5)
-- Additional table for cross-session persistent objects
CREATE TABLE persistent_objects (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
user_id TEXT NOT NULL, -- across all sessions for this user
source_object_id UUID, -- which session object it came from
source_session_id UUID,
object_type TEXT NOT NULL,
content TEXT NOT NULL, -- curated persistent version
embedding vector(384) NOT NULL,
confidence REAL NOT NULL DEFAULT 1.0, -- decays if not reinforced
last_reinforced TIMESTAMPTZ NOT NULL DEFAULT now(),
created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE INDEX idx_persistent_user ON persistent_objects(user_id);
CREATE INDEX idx_persistent_embedding ON persistent_objects
USING ivfflat (embedding vector_cosine_ops) WITH (lists = 50);