How I Structured My Automation Database From Scratch

#postgres #supabase #database #python

Most people start with code and bolt on a database later. I did the opposite.

The database came first. The agents were built around it. Here is the schema philosophy that runs six businesses on one Postgres instance.

The Core Design Rules

Rule 1: Every business gets its own table prefix.

Load Bearing Capital tables start with nothing special — they were first.
Petroleum Noir tables start with pn_.
StructCalc tables start with sc_.
Demo company tables start with demo_.

When you have 142 tables, prefixes save your sanity.

Rule 2: Status enums everywhere.

Every table that tracks a process has a status column with a CHECK constraint.

status text CHECK (status = ANY (ARRAY[
  'pending', 'in_progress', 'done', 'failed', 'cancelled'
]))

This forces consistency. Your agents cannot write invalid states. Your queries filter cleanly.

Rule 3: pg_cron for scheduling, not cron daemons.

Running a cron daemon on your server is one more thing to break. pg_cron runs inside Postgres. If Postgres is up, your jobs run.

SELECT cron.schedule(
  'publish-articles-daily',
  '0 15 * * *',
  $$SELECT net.http_post(url := 'https://your-project.supabase.co/functions/v1/publish-build-content', headers := jsonb_build_object('Authorization', 'Bearer ' || current_setting('app.service_key')))$$
);

Rule 4: Row-level security on everything.

Even if you are the only user right now. RLS is a habit. Enable it on every table from day one.

ALTER TABLE leads ENABLE ROW LEVEL SECURITY;
CREATE POLICY "service_only" ON leads USING (auth.role() = 'service_role');

Your service key bypasses RLS. Your anon key cannot touch the data.

Rule 5: jarvis_memory for agent state.

One table, five memory types: identity, episodic, strategy, policy, state.

CREATE TABLE jarvis_memory (
  id uuid DEFAULT gen_random_uuid() PRIMARY KEY,
  memory_type text CHECK (memory_type = ANY (ARRAY[
    'identity', 'episodic', 'strategy', 'policy', 'state'
  ])),
  key text NOT NULL,
  value jsonb NOT NULL,
  confidence numeric DEFAULT 0.8,
  created_at timestamptz DEFAULT now(),
  updated_at timestamptz DEFAULT now()
);

Every agent reads from this at session start. Every agent writes back. The state memory type stores where the last session left off. No lost context between runs.

The Tables That Matter Most

leads — 47 columns. Every distressed property lead across 14 counties. Distress score, equity estimate, owner contact, scrape source.

dial_queue — The call queue. Status, attempts, assigned VAPI agent, scheduled time.

call_outcomes — Every call result. Transcript, sentiment, follow-up flag, raw VAPI payload.

content_queue — Video and social content pipeline. Script, caption, hashtags, video URL, platform, posting status.

build_content_queue — Dev.to and written content. Drafted article, scheduled publish time, posted URL.

jarvis_memory — Agent brain. Identity, strategy, state, policy.

claude_code_queue — Tasks queued for Claude Code on the server. 2,234 tasks logged so far.

How Agents Read the Database

Every agent gets a Supabase client with the service key. It reads what it needs at the start of a task and writes results at the end.

from supabase import create_client

sb = create_client(SUPABASE_URL, SUPABASE_SERVICE_KEY)

# Agent reads current state
state = sb.table("jarvis_memory").select("key,value").eq("memory_type","state").execute()

# Agent does its work
# ...

# Agent writes result
sb.table("call_outcomes").insert({
  "lead_id": lead_id,
  "outcome": "answered_interested",
  "transcript": transcript
}).execute()