Agent-Ledger: Proof of Leverage on Agentic Postgres (BINFLOW x Tiger)

┌────────────────────────────┐         ┌────────────────────────────┐
│  Agentic Postgres (Prod)   │         │  Agentic Postgres (Fork A) │
│  - patterns                 │ <fork>  │  - agents refine patterns  │
│  - flow_events (hypertable) │         │  - run sims/embeddings     │
│  - embeddings (pgvector)    │         │  - propose merges          │
│  - pol_materialized_view    │         └───────────┬────────────────┘
│  - text search (pg_text)    │                     │ merge (Tiger CLI/MCP)
└───────┬───────────┬────────┘         ┌───────────▼────────────────┐
        │           │                   │     Agentic Postgres (Fork B)
        │           │                   │     - alternate agent team
        │           │                   └────────────────────────────┘
        │           │
        │    Fluid Storage  (artifacts: models/notebooks/assets)
        │           │
        ▼           ▼
   MCP Router   Tiger CLI  (create forks, deploy tasks, run agents)

-- Enable required extensions
CREATE EXTENSION IF NOT EXISTS timescaledb;
CREATE EXTENSION IF NOT EXISTS vector;        -- for embeddings
-- pg_text is Tiger's full-text feature; enable/assume available in Tiger Cloud context

-- 1) Core entities
CREATE TABLE patterns (
  pattern_id      UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  name            TEXT NOT NULL,
  binary_code     TEXT NOT NULL,                -- sha256 or content hash
  created_at      TIMESTAMPTZ NOT NULL DEFAULT now(),
  description     TEXT,
  tags            TEXT[]
);

-- 2) Time-labeled flow events (BINFLOW)
CREATE TABLE flow_events (
  event_id        BIGSERIAL PRIMARY KEY,
  pattern_id      UUID REFERENCES patterns(pattern_id) ON DELETE CASCADE,
  phase           TEXT CHECK (phase IN ('Focus','Loop','Transition','Pause','Emergence')),
  action          TEXT CHECK (action IN ('create','reuse','modify','publish','healthcheck')),
  payload         JSONB,
  occurred_at     TIMESTAMPTZ NOT NULL DEFAULT now()
);

-- Turn into hypertable for time-series performance
SELECT create_hypertable('flow_events', 'occurred_at', if_not_exists => TRUE);

-- 3) Hybrid search: embeddings + full text
CREATE TABLE pattern_embeddings (
  pattern_id      UUID REFERENCES patterns(pattern_id) ON DELETE CASCADE,
  embedding       VECTOR(768),        -- adjust to your model
  updated_at      TIMESTAMPTZ DEFAULT now(),
  PRIMARY KEY (pattern_id)
);

-- Tiger pg_text configuration (example)
-- Create a ts_vector column for full-text search and index it
ALTER TABLE patterns ADD COLUMN tsv tsvector;
UPDATE patterns SET tsv = to_tsvector('english', coalesce(name,'') || ' ' || coalesce(description,''));
CREATE INDEX patterns_tsv_idx ON patterns USING GIN(tsv);

-- Helper trigger to keep tsv up to date
CREATE FUNCTION patterns_tsv_update() RETURNS trigger AS $$
BEGIN
  NEW.tsv := to_tsvector('english', coalesce(NEW.name,'') || ' ' || coalesce(NEW.description,''));
  RETURN NEW;
END; $$ LANGUAGE plpgsql;

CREATE TRIGGER trg_patterns_tsv
BEFORE INSERT OR UPDATE ON patterns
FOR EACH ROW EXECUTE FUNCTION patterns_tsv_update();

-- 4) PoL computation (view + materialized view)
-- Simple baseline formula:
-- PoL = log(1 + uses) * (phase_weight / (1 + sqrt(days_since_creation)))
CREATE OR REPLACE FUNCTION phase_weight(p TEXT)
RETURNS NUMERIC AS $$
  SELECT CASE p
    WHEN 'Focus' THEN 1.0
    WHEN 'Loop' THEN 1.3
    WHEN 'Transition' THEN 1.5
    WHEN 'Pause' THEN 0.7
    WHEN 'Emergence' THEN 1.9
    ELSE 1.0 END;
$$ LANGUAGE sql IMMUTABLE;

CREATE VIEW pol_live AS
SELECT
  p.pattern_id,
  p.name,
  p.created_at,
  COALESCE(SUM( LOG(1 + 1) * phase_weight(e.phase)  -- each event adds weighted increment
               / NULLIF(1 + sqrt(EXTRACT(EPOCH FROM (now() - p.created_at))/86400)::NUMERIC,0)
         ), 0) AS pol_score,
  COUNT(e.*) AS event_count
FROM patterns p
LEFT JOIN flow_events e ON e.pattern_id = p.pattern_id
GROUP BY p.pattern_id;

-- Optionally materialize for speed during judging:
DROP MATERIALIZED VIEW IF EXISTS pol_mv;
CREATE MATERIALIZED VIEW pol_mv AS SELECT * FROM pol_live;
CREATE INDEX pol_mv_score_idx ON pol_mv(pol_score DESC);

-- 1) Full-text match
WITH ft AS (
  SELECT pattern_id, ts_rank_cd(tsv, plainto_tsquery('english', $1)) AS ft_score
  FROM patterns
  WHERE tsv @@ plainto_tsquery('english', $1)
),
-- 2) Vector similarity (use cosine distance)
vs AS (
  SELECT p.pattern_id, 1 - (p.embedding <=> $2::vector) AS vec_score
  FROM pattern_embeddings p
)
SELECT pat.pattern_id, pat.name, coalesce(ft.ft_score,0) AS ft_score,
       coalesce(vs.vec_score,0) AS vec_score,
       (coalesce(ft.ft_score,0) * 0.4 + coalesce(vs.vec_score,0) * 0.6) AS hybrid
FROM patterns pat
LEFT JOIN ft ON ft.pattern_id = pat.pattern_id
LEFT JOIN vs ON vs.pattern_id = pat.pattern_id
WHERE ($3::text IS NULL OR $3 = ANY(pat.tags))
ORDER BY hybrid DESC
LIMIT 20;

# Create main project + database
tiger projects create agent-ledger
tiger db create --project agent-ledger --name prod-ledger

# Fork for Team A
tiger db fork prod-ledger --name agent-fork-a

# Run MCP router + register agents (sample)
tiger mcp up --project agent-ledger --config mcp.yml

# Apply schema to prod and fork
tiger db exec prod-ledger -f schema.sql
tiger db exec agent-fork-a -f schema.sql

import os, json, time, hashlib
import requests
import psycopg2
from datetime import datetime, timezone

PG_URL = os.getenv("PG_URL")  # Tiger Cloud connection string

PHASES = ["Focus","Loop","Transition","Pause","Emergence"]

def dual_code(data: str, phase: str):
    binary = hashlib.sha256(data.encode()).hexdigest()
    ts = datetime.now(timezone.utc).isoformat()
    temporal = hashlib.sha256(f"{data}-{phase}-{ts}".encode()).hexdigest()[:12]
    return binary, f"{phase}:{ts}:{temporal}"

def record_event(conn, pattern_id, phase, action, payload=None):
    with conn.cursor() as cur:
        cur.execute("""
            INSERT INTO flow_events(pattern_id, phase, action, payload)
            VALUES (%s, %s, %s, %s::jsonb)
        """, (pattern_id, phase, action, json.dumps(payload or {})))
    conn.commit()

def upsert_pattern(conn, name, description, tags, sample_data, phase="Focus"):
    binary, temporal = dual_code(sample_data, phase)
    with conn.cursor() as cur:
        cur.execute("""
          INSERT INTO patterns(name, binary_code, description, tags)
          VALUES (%s, %s, %s, %s) RETURNING pattern_id
        """, (name, binary, description, tags))
        pid = cur.fetchone()[0]
    conn.commit()
    return pid, binary, temporal

if __name__ == "__main__":
    conn = psycopg2.connect(PG_URL)
    pid, bcode, tcode = upsert_pattern(
        conn,
        name="flow_balance_v1",
        description="BINFLOW utility to normalize phase transitions",
        tags=["binflow","utils","python"],
        sample_data="def balance(flow): return flow"
    )
    record_event(conn, pid, "Focus", "create", {"binary_code": bcode, "temporal_code": tcode})
    time.sleep(1)
    record_event(conn, pid, "Loop", "modify", {"note": "tuned thresholds"})
    time.sleep(1)
    record_event(conn, pid, "Emergence", "publish", {"platform": "dev.to"})
    print("seeded; check pol_live or pol_mv")

tiger login
tiger projects create agent-ledger
tiger db create --project agent-ledger --name prod-ledger --plan free

# Apply schema + seed
tiger db exec prod-ledger -f schema.sql
python seed.py
tiger db exec prod-ledger -c "REFRESH MATERIALIZED VIEW pol_mv;"

# Forks for agents
tiger db fork prod-ledger --name fork-a
tiger db fork prod-ledger --name fork-b

# MCP router
tiger mcp up --project agent-ledger --config mcp.yml

# Deploy app (example Docker)
docker build -t agent-ledger:web .
tiger deploy web --image agent-ledger:web --env PG_URL=...

REFRESH MATERIALIZED VIEW pol_mv;  -- if needed
SELECT name, pol_score, event_count FROM pol_mv ORDER BY pol_score DESC LIMIT 10;

SELECT * FROM flow_events ORDER BY occurred_at DESC LIMIT 25;

SELECT name, ts_rank_cd(tsv, plainto_tsquery('english','phase transitions')) AS rank
FROM patterns
WHERE tsv @@ plainto_tsquery('english','phase transitions')
ORDER BY rank DESC
LIMIT 10;