psql --version    # Should output: psql (PostgreSQL) 16.x

-- Create dedicated user with minimal privileges
CREATE USER openprose_agent WITH PASSWORD 'changeme';
CREATE SCHEMA openprose AUTHORIZATION openprose_agent;
GRANT ALL ON SCHEMA openprose TO openprose_agent;
-- User can only access the openprose schema, nothing else

Is your program <30 statements with no parallel blocks?
  YES -> Use in-context state (zero friction)
  NO  -> Continue...

Do external tools (dashboards, monitoring, analytics) need to query state?
  YES -> Use PostgreSQL (network access required)
  NO  -> Continue...

Do multiple machines or team members need shared access to the same run?
  YES -> Use PostgreSQL (collaboration)
  NO  -> Continue...

Do you have >5 concurrent parallel branches writing simultaneously?
  YES -> Use PostgreSQL (concurrency)
  NO  -> Continue...

Will outputs exceed 1GB or writes exceed 100/minute?
  YES -> Use PostgreSQL (scale)
  NO  -> Use filesystem (default) or SQLite (if you want SQL queries)

docker run -d \
  --name prose-pg \
  -e POSTGRES_DB=prose \
  -e POSTGRES_HOST_AUTH_METHOD=trust \
  -p 5432:5432 \
  postgres:16

mkdir -p .prose
echo "OPENPROSE_POSTGRES_URL=postgresql://postgres@localhost:5432/prose" > .prose/.env

docker ps | grep prose-pg    # Check if running
docker logs prose-pg         # View logs
docker stop prose-pg         # Stop
docker start prose-pg        # Start again
docker rm -f prose-pg        # Remove completely

brew install postgresql@16
brew services start postgresql@16
createdb myproject
echo "OPENPROSE_POSTGRES_URL=postgresql://localhost/myproject" >> .prose/.env

sudo apt install postgresql
sudo systemctl start postgresql
sudo -u postgres createdb myproject
echo "OPENPROSE_POSTGRES_URL=postgresql:///myproject" >> .prose/.env

# Example: Neon
echo "OPENPROSE_POSTGRES_URL=postgresql://user:[email protected]/neondb?sslmode=require" >> .prose/.env

your-project/
├── .prose/
│   ├── .env                    # OPENPROSE_POSTGRES_URL=...
│   └── runs/                   # Execution metadata and attachments
│       └── {YYYYMMDD}-{HHMMSS}-{random}/
│           ├── program.prose   # Copy of running program
│           └── attachments/    # Large outputs (optional)
├── .gitignore                  # Should exclude .prose/.env
└── your-program.prose

# OpenProse sensitive files
.prose/.env
.prose/runs/

Binding written: research
Location: openprose.bindings WHERE name='research' AND run_id='20260116-143052-a7b3c9' AND execution_id IS NULL
Summary: AI safety research covering alignment, robustness, and interpretability with 15 citations.

Binding written: result
Location: openprose.bindings WHERE name='result' AND run_id='20260116-143052-a7b3c9' AND execution_id=43
Execution ID: 43
Summary: Processed chunk into 3 sub-parts for recursive processing.

-- Create dedicated schema for OpenProse state
CREATE SCHEMA IF NOT EXISTS openprose;

-- Run metadata
CREATE TABLE IF NOT EXISTS openprose.run (
    id TEXT PRIMARY KEY,
    program_path TEXT,
    program_source TEXT,
    started_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    status TEXT NOT NULL DEFAULT 'running'
        CHECK (status IN ('running', 'completed', 'failed', 'interrupted')),
    state_mode TEXT NOT NULL DEFAULT 'postgres',
    metadata JSONB DEFAULT '{}'::jsonb
);

-- Execution position and history
CREATE TABLE IF NOT EXISTS openprose.execution (
    id SERIAL PRIMARY KEY,
    run_id TEXT NOT NULL REFERENCES openprose.run(id) ON DELETE CASCADE,
    statement_index INTEGER NOT NULL,
    statement_text TEXT,
    status TEXT NOT NULL DEFAULT 'pending'
        CHECK (status IN ('pending', 'executing', 'completed', 'failed', 'skipped')),
    started_at TIMESTAMPTZ,
    completed_at TIMESTAMPTZ,
    error_message TEXT,
    parent_id INTEGER REFERENCES openprose.execution(id) ON DELETE CASCADE,
    metadata JSONB DEFAULT '{}'::jsonb
);

-- All named values (input, output, let, const)
CREATE TABLE IF NOT EXISTS openprose.bindings (
    name TEXT NOT NULL,
    run_id TEXT NOT NULL REFERENCES openprose.run(id) ON DELETE CASCADE,
    execution_id INTEGER,  -- NULL for root scope, non-null for block invocations
    kind TEXT NOT NULL CHECK (kind IN ('input', 'output', 'let', 'const')),
    value TEXT,
    source_statement TEXT,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    attachment_path TEXT,
    metadata JSONB DEFAULT '{}'::jsonb,
    PRIMARY KEY (name, run_id, COALESCE(execution_id, -1))  -- Composite key with scope
);

-- Persistent agent memory
CREATE TABLE IF NOT EXISTS openprose.agents (
    name TEXT NOT NULL,
    run_id TEXT,  -- NULL for project-scoped and user-scoped agents
    scope TEXT NOT NULL CHECK (scope IN ('execution', 'project', 'user', 'custom')),
    memory TEXT,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    metadata JSONB DEFAULT '{}'::jsonb,
    PRIMARY KEY (name, COALESCE(run_id, '__project__'))
);

-- Agent invocation history
CREATE TABLE IF NOT EXISTS openprose.agent_segments (
    id SERIAL PRIMARY KEY,
    agent_name TEXT NOT NULL,
    run_id TEXT,  -- NULL for project-scoped agents
    segment_number INTEGER NOT NULL,
    timestamp TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    prompt TEXT,
    summary TEXT,
    metadata JSONB DEFAULT '{}'::jsonb,
    UNIQUE (agent_name, COALESCE(run_id, '__project__'), segment_number)
);

-- Import registry
CREATE TABLE IF NOT EXISTS openprose.imports (
    alias TEXT NOT NULL,
    run_id TEXT NOT NULL REFERENCES openprose.run(id) ON DELETE CASCADE,
    source_url TEXT NOT NULL,
    fetched_at TIMESTAMPTZ,
    inputs_schema JSONB,
    outputs_schema JSONB,
    content_hash TEXT,
    metadata JSONB DEFAULT '{}'::jsonb,
    PRIMARY KEY (alias, run_id)
);

-- Indexes for common queries
CREATE INDEX IF NOT EXISTS idx_execution_run_id ON openprose.execution(run_id);
CREATE INDEX IF NOT EXISTS idx_execution_status ON openprose.execution(status);
CREATE INDEX IF NOT EXISTS idx_execution_parent_id ON openprose.execution(parent_id) WHERE parent_id IS NOT NULL;
CREATE INDEX IF NOT EXISTS idx_execution_metadata_gin ON openprose.execution USING GIN (metadata jsonb_path_ops);
CREATE INDEX IF NOT EXISTS idx_bindings_run_id ON openprose.bindings(run_id);
CREATE INDEX IF NOT EXISTS idx_bindings_execution_id ON openprose.bindings(execution_id) WHERE execution_id IS NOT NULL;
CREATE INDEX IF NOT EXISTS idx_agents_run_id ON openprose.agents(run_id) WHERE run_id IS NOT NULL;
CREATE INDEX IF NOT EXISTS idx_agents_project_scoped ON openprose.agents(name) WHERE run_id IS NULL;
CREATE INDEX IF NOT EXISTS idx_agent_segments_lookup ON openprose.agent_segments(agent_name, run_id);

-- Find binding 'result' starting from execution_id 43 in run '20260116-143052-a7b3c9'
WITH RECURSIVE scope_chain AS (
  -- Start with current execution
  SELECT id, parent_id FROM openprose.execution WHERE id = 43
  UNION ALL
  -- Walk up to parent
  SELECT e.id, e.parent_id
  FROM openprose.execution e
  JOIN scope_chain s ON e.id = s.parent_id
)
SELECT b.* FROM openprose.bindings b
WHERE b.name = 'result'
  AND b.run_id = '20260116-143052-a7b3c9'
  AND (b.execution_id IN (SELECT id FROM scope_chain) OR b.execution_id IS NULL)
ORDER BY
  CASE WHEN b.execution_id IS NULL THEN 1 ELSE 0 END,  -- Prefer scoped over root
  b.execution_id DESC NULLS LAST  -- Prefer deeper (more local) scope
LIMIT 1;

-- Direct lookup: check current scope (43), then parent (42), then root (NULL)
SELECT * FROM openprose.bindings
WHERE name = 'result'
  AND run_id = '20260116-143052-a7b3c9'
  AND (execution_id = 43 OR execution_id = 42 OR execution_id IS NULL)
ORDER BY execution_id DESC NULLS LAST
LIMIT 1;

# Initialize schema
psql "$OPENPROSE_POSTGRES_URL" -f schema.sql

# Register a new run
psql "$OPENPROSE_POSTGRES_URL" -c "
  INSERT INTO openprose.run (id, program_path, program_source, status)
  VALUES ('20260116-143052-a7b3c9', '/path/to/program.prose', 'program source...', 'running')
"

# Update execution position
psql "$OPENPROSE_POSTGRES_URL" -c "
  INSERT INTO openprose.execution (run_id, statement_index, statement_text, status, started_at)
  VALUES ('20260116-143052-a7b3c9', 3, 'session \"Research AI safety\"', 'executing', NOW())
"

# Read a binding
psql "$OPENPROSE_POSTGRES_URL" -t -A -c "
  SELECT value FROM openprose.bindings WHERE name = 'research' AND run_id = '20260116-143052-a7b3c9'
"

# Check parallel branch status
psql "$OPENPROSE_POSTGRES_URL" -c "
  SELECT metadata->>'branch' AS branch, status FROM openprose.execution
  WHERE run_id = '20260116-143052-a7b3c9' AND metadata->>'parallel_id' = 'p1'
"

Your output goes to PostgreSQL state.

| Property | Value |
|----------|-------|
| Connection | `postgresql://user:***@host:5432/db` |
| Schema | `openprose` |
| Run ID | `20260116-143052-a7b3c9` |
| Binding | `research` |
| Execution ID | (root scope) |

When complete, write your output:

psql "$OPENPROSE_POSTGRES_URL" -c "
  INSERT INTO openprose.bindings (name, run_id, execution_id, kind, value, source_statement)
  VALUES (
    'research',
    '20260116-143052-a7b3c9',
    NULL,  -- root scope
    'let',
    E'AI safety research covers alignment, robustness...',
    'let research = session: researcher'
  )
  ON CONFLICT (name, run_id, COALESCE(execution_id, -1)) DO UPDATE
  SET value = EXCLUDED.value, updated_at = NOW()
"

Your output goes to PostgreSQL state.

| Property | Value |
|----------|-------|
| Connection | `postgresql://user:***@host:5432/db` |
| Schema | `openprose` |
| Run ID | `20260116-143052-a7b3c9` |
| Binding | `result` |
| Execution ID | `43` |
| Block | `process` |
| Depth | `3` |

When complete, write your output:

psql "$OPENPROSE_POSTGRES_URL" -c "
  INSERT INTO openprose.bindings (name, run_id, execution_id, kind, value, source_statement)
  VALUES (
    'result',
    '20260116-143052-a7b3c9',
    43,  -- scoped to this execution
    'let',
    E'Processed chunk into 3 sub-parts...',
    'let result = session \"Process chunk\"'
  )
  ON CONFLICT (name, run_id, COALESCE(execution_id, -1)) DO UPDATE
  SET value = EXCLUDED.value, updated_at = NOW()
"

Your memory is in the database:

Read your current state:
  psql "$OPENPROSE_POSTGRES_URL" -t -A -c "SELECT memory FROM openprose.agents WHERE name = 'captain' AND run_id = '20260116-143052-a7b3c9'"

Update when done:
  psql "$OPENPROSE_POSTGRES_URL" -c "UPDATE openprose.agents SET memory = '...', updated_at = NOW() WHERE name = 'captain' AND run_id = '20260116-143052-a7b3c9'"

Record this segment:
  psql "$OPENPROSE_POSTGRES_URL" -c "INSERT INTO openprose.agent_segments (agent_name, run_id, segment_number, prompt, summary) VALUES ('captain', '20260116-143052-a7b3c9', 3, '...', '...')"

-- Read project-scoped agent memory
SELECT memory FROM openprose.agents WHERE name = 'advisor' AND run_id IS NULL;

-- Update project-scoped agent memory
UPDATE openprose.agents SET memory = '...' WHERE name = 'advisor' AND run_id IS NULL;

[Position] Statement 3: let research = session: researcher
   Spawning session, will write to state database
   [Task tool call]
[Success] Session complete, binding written to DB
[Binding] research = <stored in openprose.bindings>

-- VM marks parallel start
INSERT INTO openprose.execution (run_id, statement_index, statement_text, status, started_at, metadata)
VALUES ('20260116-143052-a7b3c9', 5, 'parallel:', 'executing', NOW(),
  '{"parallel_id": "p1", "strategy": "all", "branches": ["a", "b", "c"]}'::jsonb)
RETURNING id;  -- Save as parent_id (e.g., 42)

-- VM creates execution record for each branch
INSERT INTO openprose.execution (run_id, statement_index, statement_text, status, started_at, parent_id, metadata)
VALUES
  ('20260116-143052-a7b3c9', 6, 'a = session "Task A"', 'executing', NOW(), 42, '{"parallel_id": "p1", "branch": "a"}'::jsonb),
  ('20260116-143052-a7b3c9', 7, 'b = session "Task B"', 'executing', NOW(), 42, '{"parallel_id": "p1", "branch": "b"}'::jsonb),
  ('20260116-143052-a7b3c9', 8, 'c = session "Task C"', 'executing', NOW(), 42, '{"parallel_id": "p1", "branch": "c"}'::jsonb);

-- Subagents write their outputs to bindings table (see "From Subagents" section)
-- Task tool signals completion to VM via substrate

-- VM marks branch complete after Task returns
UPDATE openprose.execution SET status = 'completed', completed_at = NOW()
WHERE run_id = '20260116-143052-a7b3c9' AND metadata->>'parallel_id' = 'p1' AND metadata->>'branch' = 'a';

-- VM checks if all branches complete
SELECT COUNT(*) AS pending FROM openprose.execution
WHERE run_id = '20260116-143052-a7b3c9'
  AND metadata->>'parallel_id' = 'p1'
  AND parent_id IS NOT NULL
  AND status NOT IN ('completed', 'failed', 'skipped');

SQLite (table locks):
  Branch 1 writes -------|
                         Branch 2 waits ------|
                                              Branch 3 waits -----|
  Total time: 3 * write_time (serialized)

PostgreSQL (row locks):
  Branch 1 writes  --|
  Branch 2 writes  --|  (concurrent)
  Branch 3 writes  --|
  Total time: ~1 * write_time (parallel)

-- Loop metadata tracks iteration state
INSERT INTO openprose.execution (run_id, statement_index, statement_text, status, started_at, metadata)
VALUES ('20260116-143052-a7b3c9', 10, 'loop until **analysis complete** (max: 5):', 'executing', NOW(),
  '{"loop_id": "l1", "max_iterations": 5, "current_iteration": 0, "condition": "**analysis complete**"}'::jsonb);

-- Update iteration
UPDATE openprose.execution
SET metadata = jsonb_set(metadata, '{current_iteration}', '2')
WHERE run_id = '20260116-143052-a7b3c9' AND metadata->>'loop_id' = 'l1' AND parent_id IS NULL;

-- Record failure
UPDATE openprose.execution
SET status = 'failed',
    error_message = 'Connection timeout after 30s',
    completed_at = NOW()
WHERE id = 15;

-- Track retry attempts in metadata
UPDATE openprose.execution
SET metadata = jsonb_set(jsonb_set(metadata, '{retry_attempt}', '2'), '{max_retries}', '3')
WHERE id = 15;

-- Mark run as failed
UPDATE openprose.run SET status = 'failed' WHERE id = '20260116-143052-a7b3c9';

PRIMARY KEY (name, COALESCE(run_id, '__project__'))

-- Delete execution-scoped agents for a completed run
DELETE FROM openprose.agents WHERE run_id = '20260116-143052-a7b3c9';

-- Delete a specific project-scoped agent (user-initiated)
DELETE FROM openprose.agents WHERE name = 'old_advisor' AND run_id IS NULL;

INSERT INTO openprose.bindings (name, run_id, kind, value, attachment_path, source_statement)
VALUES (
  'full_report',
  '20260116-143052-a7b3c9',
  'let',
  'Full analysis report (847KB) - see attachment',
  'attachments/full_report.md',
  'let full_report = session "Generate comprehensive report"'
)
ON CONFLICT (name, run_id) DO UPDATE
SET value = EXCLUDED.value, attachment_path = EXCLUDED.attachment_path, updated_at = NOW();

-- Find current position
SELECT statement_index, statement_text, status
FROM openprose.execution
WHERE run_id = '20260116-143052-a7b3c9' AND status = 'executing'
ORDER BY id DESC LIMIT 1;

-- Get all completed bindings
SELECT name, kind, value, attachment_path FROM openprose.bindings
WHERE run_id = '20260116-143052-a7b3c9';

-- Get agent memory states
SELECT name, scope, memory FROM openprose.agents
WHERE run_id = '20260116-143052-a7b3c9' OR run_id IS NULL;

-- Check parallel block status
SELECT metadata->>'branch' AS branch, status
FROM openprose.execution
WHERE run_id = '20260116-143052-a7b3c9'
  AND metadata->>'parallel_id' IS NOT NULL
  AND parent_id IS NOT NULL;

-- Custom metrics table
CREATE TABLE IF NOT EXISTS openprose.x_metrics (
    id SERIAL PRIMARY KEY,
    run_id TEXT REFERENCES openprose.run(id) ON DELETE CASCADE,
    execution_id INTEGER REFERENCES openprose.execution(id) ON DELETE CASCADE,
    metric_name TEXT NOT NULL,
    metric_value NUMERIC,
    recorded_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    metadata JSONB DEFAULT '{}'::jsonb
);

-- Add custom column
ALTER TABLE openprose.bindings ADD COLUMN IF NOT EXISTS token_count INTEGER;

-- Create index for common query
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_bindings_created ON openprose.bindings(created_at);