Convert gitgram/ and aitimemachine/ from embedded git repos (which showed as opaque submodule gitlinks on GitHub) into fully-tracked first-party content. AITM now merges its 7 vendored repos as first-party engines: - memory: beads (Go task DAG) + context7 (docs SDK), unified into saved_contexts - git: vendored C git (bundled binary) + gitnexus (in-process code intelligence) - agentic: a2a (@a2a-js/sdk) + pal (AITM-managed Python subprocess) Includes the full session's product build: Gitgram web/mobile/backend features, AITM website/CLI/MCP, the unified memory layer, engine build tooling (scripts/build-engines.sh, Dockerfile, CI), docs (README, ENGINES.md, ARCHITECTURE.md), and extensive test suites. Build artifacts, node_modules, external/, and gemini-cli are gitignored. Co-Authored-By: Claude Opus 4.8 (1M context) <noreply@anthropic.com>
9.1 KiB
9.1 KiB
Backend Database Schema - Complete Reference
This document shows the complete Neon (Postgres) schema that stores everything for AITimeMachine.
Canonical reference: ../../NORTH_STAR_PRD_ARCHITECTURE_V1.md.
Canonical SQL source: ../../CANONICAL_SQL_SCHEMA_V1.sql.
Note:
- This file is kept as an explanatory AITM-focused reference.
- For implementation and migrations, use
../../CANONICAL_SQL_SCHEMA_V1.sqlas source of truth.
💾 Complete Database Schema
-- ============================================
-- USERS & AUTHENTICATION
-- ============================================
CREATE TABLE users (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
email TEXT UNIQUE NOT NULL,
api_key TEXT UNIQUE NOT NULL,
created_at TIMESTAMP DEFAULT NOW()
);
CREATE TABLE projects (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
user_id UUID REFERENCES users(id),
name TEXT NOT NULL,
path TEXT NOT NULL,
created_at TIMESTAMP DEFAULT NOW(),
updated_at TIMESTAMP DEFAULT NOW()
);
-- ============================================
-- TIME LAYER: Files, Folders, Checkpoints
-- ============================================
-- All file content (content-addressable)
CREATE TABLE blobs (
hash TEXT PRIMARY KEY,
content BYTEA NOT NULL,
size INTEGER NOT NULL,
compression TEXT DEFAULT 'brotli'
);
-- Directory trees (Merkle trees)
CREATE TABLE trees (
hash TEXT PRIMARY KEY,
entries JSONB NOT NULL -- [{name, type, hash, mode}]
);
-- Checkpoints (snapshots)
CREATE TABLE checkpoints (
id TEXT PRIMARY KEY,
project_id UUID REFERENCES projects(id),
user_id UUID REFERENCES users(id),
timestamp TIMESTAMP NOT NULL,
description TEXT,
parent_id TEXT REFERENCES checkpoints(id),
tree_hash TEXT REFERENCES trees(hash),
session_id TEXT,
trigger_type TEXT, -- 'manual', 'auto', 'before_action', 'after_action'
linked_checkpoint_id TEXT REFERENCES checkpoints(id) -- Links before/after
);
-- Files in each checkpoint
CREATE TABLE checkpoint_files (
checkpoint_id TEXT REFERENCES checkpoints(id),
file_path TEXT NOT NULL,
blob_hash TEXT REFERENCES blobs(hash),
size INTEGER,
mode INTEGER,
change_type TEXT, -- 'added', 'modified', 'deleted', 'unchanged'
PRIMARY KEY (checkpoint_id, file_path)
);
-- Pre-computed diffs (for speed)
CREATE TABLE diffs (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
from_checkpoint_id TEXT REFERENCES checkpoints(id),
to_checkpoint_id TEXT REFERENCES checkpoints(id),
file_path TEXT,
diff_content TEXT,
stats JSONB, -- {added: 10, removed: 5, ...}
algorithm TEXT DEFAULT 'histogram'
);
-- ============================================
-- CONTEXT LAYER: Chats, Actions, Mental State
-- ============================================
-- All messages (every chat)
CREATE TABLE messages (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
project_id UUID REFERENCES projects(id),
session_id TEXT NOT NULL,
checkpoint_id TEXT REFERENCES checkpoints(id), -- Link to code state
timestamp TIMESTAMP DEFAULT NOW(),
role TEXT CHECK (role IN ('user', 'assistant', 'system')),
content TEXT NOT NULL,
thinking TEXT, -- AI's internal reasoning
metadata JSONB
);
-- All tool calls (every AI action)
CREATE TABLE tool_calls (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
message_id UUID REFERENCES messages(id),
checkpoint_before_id TEXT REFERENCES checkpoints(id),
checkpoint_after_id TEXT REFERENCES checkpoints(id),
tool_name TEXT NOT NULL,
arguments JSONB NOT NULL,
result JSONB,
success BOOLEAN,
error TEXT,
timestamp TIMESTAMP DEFAULT NOW()
);
-- Mental state snapshots
CREATE TABLE contexts (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
checkpoint_id TEXT REFERENCES checkpoints(id),
session_id TEXT,
summary TEXT,
active_files JSONB, -- ["src/api.ts", "src/db.ts"]
cursor_position JSONB, -- {file: "api.ts", line: 42, col: 10}
current_task TEXT,
goals JSONB, -- ["Implement auth", "Add tests"]
timestamp TIMESTAMP DEFAULT NOW()
);
-- ============================================
-- AUGMENTED LAYER: Search & Intelligence
-- ============================================
-- Code embeddings (for semantic search)
CREATE TABLE embeddings (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
project_id UUID REFERENCES projects(id),
checkpoint_id TEXT REFERENCES checkpoints(id),
file_path TEXT,
chunk_text TEXT,
chunk_start_line INTEGER,
chunk_end_line INTEGER,
embedding vector(1536), -- OpenAI ada-002
metadata JSONB,
created_at TIMESTAMP DEFAULT NOW()
);
CREATE INDEX ON embeddings USING ivfflat (embedding vector_cosine_ops);
-- Full-text search on messages
CREATE INDEX idx_messages_search
ON messages USING gin(to_tsvector('english', content));
-- Documentation index
CREATE TABLE indexed_docs (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
project_id UUID REFERENCES projects(id),
url TEXT NOT NULL,
title TEXT,
content TEXT,
embedding vector(1536),
metadata JSONB,
indexed_at TIMESTAMP DEFAULT NOW()
);
-- Oracle research jobs
CREATE TABLE research_jobs (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
project_id UUID REFERENCES projects(id),
user_id UUID REFERENCES users(id),
query TEXT NOT NULL,
status TEXT DEFAULT 'pending', -- 'pending', 'running', 'completed', 'failed'
result JSONB,
created_at TIMESTAMP DEFAULT NOW(),
completed_at TIMESTAMP
);
-- ============================================
-- INDEXES for Performance
-- ============================================
CREATE INDEX idx_checkpoints_project ON checkpoints(project_id);
CREATE INDEX idx_checkpoints_timestamp ON checkpoints(timestamp);
CREATE INDEX idx_checkpoints_session ON checkpoints(session_id);
CREATE INDEX idx_messages_project ON messages(project_id);
CREATE INDEX idx_messages_session ON messages(session_id);
CREATE INDEX idx_tool_calls_message ON tool_calls(message_id);
CREATE INDEX idx_diffs_from ON diffs(from_checkpoint_id);
CREATE INDEX idx_diffs_to ON diffs(to_checkpoint_id);
📝 Example: Complete Data Flow
When AI creates a file, here's everything that gets saved:
-- 1. USER MESSAGE
INSERT INTO messages VALUES (
id: 'msg_abc123',
project_id: 'proj_xyz',
session_id: 'sess_001',
role: 'user',
content: 'Create an API endpoint'
);
-- 2. BEFORE_ACTION CHECKPOINT
INSERT INTO checkpoints VALUES (
id: 'cp_before_def456',
project_id: 'proj_xyz',
description: 'Before create_file: src/api.ts',
tree_hash: 'tree_old123',
trigger_type: 'before_action'
);
-- 3. CURRENT FILES SAVED
INSERT INTO checkpoint_files VALUES
('cp_before_def456', 'src/index.ts', 'blob_aaa', 1024, 0644, 'unchanged'),
('cp_before_def456', 'package.json', 'blob_bbb', 512, 0644, 'unchanged');
-- 4. TOOL CALL RECORDED
INSERT INTO tool_calls VALUES (
id: 'tool_ghi789',
message_id: 'msg_abc123',
checkpoint_before_id: 'cp_before_def456',
tool_name: 'create_file',
arguments: '{"path": "src/api.ts", "content": "..."}'
);
-- 5. NEW FILE CONTENT SAVED
INSERT INTO blobs VALUES (
hash: 'blob_ccc',
content: <compressed bytes>,
size: 2048
);
-- 6. AFTER_ACTION CHECKPOINT
INSERT INTO checkpoints VALUES (
id: 'cp_after_jkl012',
project_id: 'proj_xyz',
description: 'After create_file: src/api.ts',
tree_hash: 'tree_new456',
trigger_type: 'after_action',
linked_checkpoint_id: 'cp_before_def456' -- LINK!
);
-- 7. NEW FILES SAVED
INSERT INTO checkpoint_files VALUES
('cp_after_jkl012', 'src/index.ts', 'blob_aaa', 1024, 0644, 'unchanged'),
('cp_after_jkl012', 'package.json', 'blob_bbb', 512, 0644, 'unchanged'),
('cp_after_jkl012', 'src/api.ts', 'blob_ccc', 2048, 0644, 'added'); -- NEW!
-- 8. DIFF SAVED
INSERT INTO diffs VALUES (
from_checkpoint_id: 'cp_before_def456',
to_checkpoint_id: 'cp_after_jkl012',
file_path: 'src/api.ts',
diff_content: '@@ -0,0 +1,50 @@\n+export const api = ...',
stats: '{"added": 50, "removed": 0}'
);
-- 9. CONTEXT SAVED
INSERT INTO contexts VALUES (
checkpoint_id: 'cp_after_jkl012',
session_id: 'sess_001',
summary: 'Created API endpoint',
active_files: '["src/api.ts"]',
current_task: 'Implementing REST API'
);
-- 10. EMBEDDINGS GENERATED (async)
INSERT INTO embeddings VALUES (
project_id: 'proj_xyz',
checkpoint_id: 'cp_after_jkl012',
file_path: 'src/api.ts',
chunk_text: 'export const api = ...',
embedding: [0.123, -0.456, ...] -- 1536 dimensions
);
-- 11. TOOL RESULT UPDATED
UPDATE tool_calls SET
checkpoint_after_id = 'cp_after_jkl012',
result = '{"success": true}',
success = true
WHERE id = 'tool_ghi789';
🔍 Query Examples
-- Get all messages in a session with their checkpoints
SELECT
m.content,
m.role,
c.description as checkpoint_desc,
c.timestamp
FROM messages m
LEFT JOIN checkpoints c ON m.checkpoint_id = c.id
WHERE m.session_id = 'sess_001'
ORDER BY m.timestamp;
-- Find what changed in a file over time
SELECT
c.timestamp,
c.description,
d.stats
FROM diffs d
JOIN checkpoints c ON d.to_checkpoint_id = c.id
WHERE d.file_path = 'src/api.ts'
ORDER BY c.timestamp;
-- Search code semantically
SELECT
file_path,
chunk_text,
1 - (embedding <=> '[query_embedding]') as similarity
FROM embeddings
WHERE project_id = 'proj_xyz'
ORDER BY embedding <=> '[query_embedding]'
LIMIT 10;