Database

import { Aside } from ‘@astrojs/starlight/components’;

Platform

Supabase (managed PostgreSQL 15) — us-east-1 region.

Schema

leads

Stores all booking inquiry form submissions.

CREATE TABLE leads (
  id            UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  created_at    TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at    TIMESTAMPTZ NOT NULL DEFAULT NOW(),

  -- Contact info
  name          TEXT NOT NULL CHECK (char_length(name) <= 100),
  email         TEXT NOT NULL,
  organization  TEXT NOT NULL CHECK (char_length(organization) <= 150),

  -- Engagement details
  engagement_type  TEXT NOT NULL CHECK (engagement_type IN (
    'keynote', 'workshop', 'training', 'consulting', 'media', 'other'
  )),
  budget_range  TEXT NOT NULL CHECK (budget_range IN (
    '<10k', '10k-25k', '25k-50k', '50k-75k', '75k+'
  )),
  event_date    DATE,
  message       TEXT NOT NULL CHECK (char_length(message) <= 2000),

  -- Pipeline state
  status        TEXT NOT NULL DEFAULT 'new' CHECK (status IN (
    'new', 'contacted', 'qualified', 'proposal_sent',
    'booked', 'closed_won', 'closed_lost', 'spam'
  )),

  -- CRM sync
  hubspot_contact_id  TEXT,
  hubspot_deal_id     TEXT,

  -- Metadata (PII minimized)
  source        TEXT DEFAULT 'website',
  ip_hash       TEXT,   -- SHA-256 of IP address
  user_agent    TEXT
);

media_kit_requests

Tracks email-gated media kit downloads.

CREATE TABLE media_kit_requests (
  id            UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  created_at    TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  email         TEXT NOT NULL,
  name          TEXT NOT NULL,
  organization  TEXT,
  media_type    TEXT NOT NULL CHECK (media_type IN (
    'journalist', 'producer', 'academic', 'other'
  )),
  download_token   TEXT UNIQUE NOT NULL,
  token_expires_at TIMESTAMPTZ NOT NULL,
  downloaded_at    TIMESTAMPTZ,
  download_count   INT NOT NULL DEFAULT 0
);

press_features

Press/media appearances — CMS fallback if Contentful is unavailable.

CREATE TABLE press_features (
  id            UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  outlet        TEXT NOT NULL,
  headline      TEXT NOT NULL,
  url           TEXT,
  published_at  DATE NOT NULL,
  category      TEXT CHECK (category IN ('tv', 'print', 'podcast', 'online', 'documentary')),
  featured      BOOLEAN NOT NULL DEFAULT FALSE,
  logo_key      TEXT
);

Row-Level Security

RLS is **enabled on all tables**. Default policy is **deny everything**. Only service role can access leads and media kit requests.

-- leads: service role only
ALTER TABLE leads ENABLE ROW LEVEL SECURITY;
CREATE POLICY "service_role_only" ON leads
  USING (auth.role() = 'service_role');

-- press_features: public read, service role write
ALTER TABLE press_features ENABLE ROW LEVEL SECURITY;
CREATE POLICY "public_read" ON press_features
  FOR SELECT USING (TRUE);
CREATE POLICY "service_role_write" ON press_features
  FOR ALL USING (auth.role() = 'service_role');

Data Retention

Table	Retention	Reason
leads	3 years	Sales pipeline compliance
media_kit_requests	1 year	GDPR minimization
press_features	Indefinite	Reference data

Expired records purged monthly via a pg_cron scheduled function.

Migrations

Managed with the Supabase CLI:

# Create a new migration
supabase migration new add_leads_table

# Apply locally
supabase db reset

# Deploy to remote
supabase db push --linked

Migration files live in supabase/migrations/ at the repository root.

Backups

• Supabase Pro: daily automated backups (7-day retention) + PITR
• Additional: weekly pg_dump to Cloudflare R2 (brettjohnson-backups/)