Skip to main content

Blue North Database Design

This document describes the planned production database system for Blue North Canvassing. It assumes Supabase as the first production provider, using managed PostgreSQL, PostGIS, Supabase Auth, Row Level Security, automated backups, and point-in-time recovery.

The goal is one shared production system that can safely support many campaigns, districts, users, voter files, turfs, canvassing operations, and Blue North platform administrators.

Goals​

  • Support many campaigns in one shared application.
  • Let users belong to one or more campaigns.
  • Keep campaign data isolated by default.
  • Let campaign managers, field directors, organizers, and volunteers share data inside the campaigns where they are members.
  • Keep each campaign's districts, voter universe, lists, turfs, contacts, schedules, and settings unique to that campaign.
  • Give trusted BlueNorthAdmin accounts controlled access across all campaigns.
  • Make backups, restores, migrations, secrets, and production changes part of a repeatable human process.
  • Use standard PostgreSQL patterns so the system can move from Supabase to another PostgreSQL host later if needed.

Provider Assumption​

Initial production database:

Supabase
  PostgreSQL
  PostGIS
  Supabase Auth
  Row Level Security
  Daily backups
  Point-in-time recovery for production

Supabase is useful because it gives us managed Postgres, auth, SQL migrations, a dashboard, API access, and row-level security in one place. The schema should still be normal PostgreSQL so we can move to AWS RDS, Aurora PostgreSQL, Crunchy Bridge, or another managed Postgres provider later.

Tenancy Model​

We should use one production database and separate campaign-owned data with campaign_id.

We should not create one database per user. We should also avoid one Supabase project per campaign unless a customer needs hard isolation, separate billing, or a special compliance boundary.

Recommended model:

One Supabase project
  One production database
    many campaigns
      many districts
      many voters
      many users through memberships

Most operational tables should include campaign_id, including voters, voter lists, turfs, contacts, canvassers, sites, schedules, imports, and settings.

Users can belong to multiple campaigns through campaign_memberships.

auth.users
  -> profiles
  -> campaign_memberships
  -> campaigns

The frontend can remember a user's active campaign, but the database must enforce access. The app should never trust only a frontend campaign_id.

Core Roles​

Campaign roles:

  • campaign_manager: can manage campaign settings, users, imports, districts, voter data, lists, turfs, canvassing operations, and exports.
  • field_director: can manage field users, voter lists, turfs, schedules, sites, contacts, and canvassing workflows.
  • organizer: can create lists, cut turf, manage assigned canvassers, and view assigned campaign data.
  • volunteer: can view and update assigned turfs, contacts, and shifts.
  • viewer: read-only access to campaign dashboards and reporting.

Platform roles:

  • bluenorth_admin: trusted Blue North operator with cross-campaign visibility and support tools.
  • bluenorth_support: limited cross-campaign support role, ideally read-only unless explicitly elevated.

BlueNorthAdmin access must be rare, audited, and protected with strong authentication.

High-Level System Diagram​

flowchart LR
  User["Campaign User"] --> App["Next.js App"]
  Admin["BlueNorthAdmin"] --> App
  App --> Auth["Supabase Auth"]
  App --> API["App API / Server Actions"]
  API --> DB[("Supabase Postgres + PostGIS")]
  DB --> RLS["Row Level Security Policies"]
  DB --> Backups["Backups + PITR"]
  DB --> Audit["Audit Logs"]
  Admin --> AdminDash["Blue North Admin Dashboard"]
  AdminDash --> API

Data Ownership Diagram​

erDiagram
  PROFILES ||--o{ CAMPAIGN_MEMBERSHIPS : has
  CAMPAIGNS ||--o{ CAMPAIGN_MEMBERSHIPS : grants_access
  CAMPAIGNS ||--o{ DISTRICTS : contains
  CAMPAIGNS ||--o{ VOTERS : owns
  DISTRICTS ||--o{ VOTERS : groups
  CAMPAIGNS ||--o{ VOTER_LISTS : owns
  VOTER_LISTS ||--o{ VOTER_LIST_MEMBERS : contains
  VOTERS ||--o{ VOTER_LIST_MEMBERS : included_in
  CAMPAIGNS ||--o{ TURFS : owns
  TURFS ||--o{ TURF_MEMBERS : contains
  VOTERS ||--o{ TURF_MEMBERS : assigned_to
  CAMPAIGNS ||--o{ CONTACT_ATTEMPTS : owns
  VOTERS ||--o{ CONTACT_ATTEMPTS : receives
  PROFILES ||--o{ CONTACT_ATTEMPTS : records

Core Tables​

Identity and Access​

profiles

  • id uuid primary key references auth.users(id)
  • email text not null
  • full_name text
  • created_at timestamptz not null
  • last_seen_at timestamptz
  • active_campaign_id uuid references campaigns(id)

campaigns

  • id uuid primary key
  • name text not null
  • slug text unique not null
  • status text not null
  • created_at timestamptz not null
  • created_by uuid references profiles(id)

campaign_memberships

  • id uuid primary key
  • campaign_id uuid not null references campaigns(id)
  • user_id uuid not null references profiles(id)
  • role text not null
  • status text not null
  • invited_by uuid references profiles(id)
  • created_at timestamptz not null
  • Unique index on (campaign_id, user_id).

platform_admins

  • user_id uuid primary key references profiles(id)
  • role text not null
  • created_at timestamptz not null
  • created_by uuid references profiles(id)
  • disabled_at timestamptz

BlueNorthAdmin checks should use this table or Supabase custom claims, but the source of truth should be auditable.

Campaign Geography​

districts

  • id uuid primary key
  • campaign_id uuid not null references campaigns(id)
  • name text not null
  • district_type text
  • external_id text
  • boundary geometry(MultiPolygon, 4326)
  • created_at timestamptz not null

precincts

  • id uuid primary key
  • campaign_id uuid not null references campaigns(id)
  • district_id uuid references districts(id)
  • precinct_code text not null
  • boundary geometry(MultiPolygon, 4326)
  • metadata jsonb not null default '{}'

Voter Data​

voters

  • id uuid primary key
  • campaign_id uuid not null references campaigns(id)
  • district_id uuid references districts(id)
  • external_voter_id text
  • first_name text
  • middle_name text
  • last_name text
  • registered_address text
  • mailing_address text
  • phone text
  • email text
  • party text
  • age int
  • scores jsonb not null default '{}'
  • attributes jsonb not null default '{}'
  • created_at timestamptz not null
  • updated_at timestamptz not null

voter_locations

  • voter_id uuid primary key references voters(id)
  • campaign_id uuid not null references campaigns(id)
  • location geography(Point, 4326)
  • geocode_quality text
  • source text
  • updated_at timestamptz not null

Voter location keeps geospatial indexes separate from large voter profile rows.

Lists and Turfs​

voter_lists

  • id uuid primary key
  • campaign_id uuid not null references campaigns(id)
  • name text not null
  • created_by uuid references profiles(id)
  • filters jsonb not null default '{}'
  • created_at timestamptz not null

voter_list_members

  • list_id uuid not null references voter_lists(id)
  • voter_id uuid not null references voters(id)
  • campaign_id uuid not null references campaigns(id)
  • Primary key on (list_id, voter_id).

turfs

  • id uuid primary key
  • campaign_id uuid not null references campaigns(id)
  • district_id uuid references districts(id)
  • list_id uuid references voter_lists(id)
  • name text not null
  • polygon geometry(Polygon, 4326)
  • color text
  • created_by uuid references profiles(id)
  • created_at timestamptz not null

turf_members

  • turf_id uuid not null references turfs(id)
  • voter_id uuid not null references voters(id)
  • campaign_id uuid not null references campaigns(id)
  • Primary key on (turf_id, voter_id).

Field Operations​

canvassers

  • id uuid primary key
  • campaign_id uuid not null references campaigns(id)
  • profile_id uuid references profiles(id)
  • name text not null
  • phone text
  • email text
  • status text not null
  • created_at timestamptz not null

shifts

  • id uuid primary key
  • campaign_id uuid not null references campaigns(id)
  • canvasser_id uuid references canvassers(id)
  • turf_id uuid references turfs(id)
  • site_id uuid references launch_sites(id)
  • starts_at timestamptz not null
  • ends_at timestamptz
  • status text not null

contact_attempts

  • id uuid primary key
  • campaign_id uuid not null references campaigns(id)
  • voter_id uuid not null references voters(id)
  • canvasser_id uuid references canvassers(id)
  • method text not null
  • result text not null
  • notes text
  • created_by uuid references profiles(id)
  • created_at timestamptz not null

launch_sites

  • id uuid primary key
  • campaign_id uuid not null references campaigns(id)
  • name text not null
  • kind text not null
  • address text
  • location geography(Point, 4326)
  • supplies jsonb not null default '{}'
  • settings jsonb not null default '{}'

Row Level Security Model​

All campaign-owned tables should enable RLS.

Policy rule:

A normal user can read/write rows only when:
  row.campaign_id is in that user's active campaign memberships
  and the user's role allows the attempted action

BlueNorthAdmin rule:

A BlueNorthAdmin can read all campaign rows.
Write access should be narrower, audited, and ideally require an explicit support-mode reason.

Example policy concept:

exists (
  select 1
  from campaign_memberships cm
  where cm.campaign_id = target_table.campaign_id
    and cm.user_id = auth.uid()
    and cm.status = 'active'
)

Platform admin override concept:

exists (
  select 1
  from platform_admins pa
  where pa.user_id = auth.uid()
    and pa.role = 'bluenorth_admin'
    and pa.disabled_at is null
)

Admin Dashboard​

BlueNorthAdmin accounts need a separate dashboard for platform operations.

Initial dashboard views:

  • Total campaigns.
  • Active campaigns.
  • Campaign user counts.
  • Voter record counts by campaign.
  • Storage and import volume by campaign.
  • Recent imports.
  • Recent errors.
  • Backup/PITR status.
  • Audit events.
  • Suspicious activity signals.
  • Support access log.

Admin dashboard actions:

  • View campaign metadata.
  • View campaign membership and roles.
  • Help a campaign manager resend invites.
  • Disable compromised users.
  • Place a campaign in read-only/suspended status.
  • Trigger export or support diagnostics.
  • View restore readiness status.

Dangerous admin actions should require confirmation, a reason, and an audit event.

Backup System​

Production backup requirements:

  • Enable Supabase daily backups.
  • Enable point-in-time recovery for production.
  • Keep at least 7 days of PITR at launch.
  • Increase to 14 or 28 days for larger customers or sensitive production usage.
  • Export periodic logical backups before major migrations.
  • Keep a runbook for restores.
  • Run restore drills monthly.
  • Test application startup against restored staging databases.

Backup layers:

flowchart TD
  Prod[("Production Supabase Database")]
  Daily["Daily Managed Backups"]
  PITR["Point-in-Time Recovery"]
  PreMigration["Pre-Migration Snapshot / Export"]
  RestoreStaging[("Restore Drill Staging DB")]
  Runbook["Human Restore Runbook"]

  Prod --> Daily
  Prod --> PITR
  Prod --> PreMigration
  Daily --> RestoreStaging
  PITR --> RestoreStaging
  Runbook --> RestoreStaging

Restore Process​

Restore process for accidental data damage:

  1. Stop the damaging process if it is still running.
  2. Put the affected campaign or app section into maintenance/read-only mode if needed.
  3. Identify the earliest bad timestamp.
  4. Choose the restore target timestamp just before the bad event.
  5. Restore to a separate database first, not directly over production.
  6. Compare affected tables and confirm the data needed.
  7. Choose recovery strategy:
    • full database restore when the whole production database is damaged
    • selective data repair when only one campaign or table was affected
  8. Record what happened in an incident log.
  9. Add tests, constraints, or process changes to prevent repeat damage.

Monthly restore drill:

  1. Pick a recent backup.
  2. Restore it to a staging database.
  3. Run migrations if required.
  4. Start the app against the restored database.
  5. Verify login, campaign dashboard, voter search, turf views, and contact history.
  6. Record restore time and any issues.

Secure Deployment Process​

Secrets and keys:

  • Never commit .env files with real credentials.
  • Store production environment variables only in Vercel and Supabase dashboards.
  • Use separate keys for local, preview, staging, and production.
  • Limit who can view production secrets.
  • Rotate production service-role keys after any suspected exposure.
  • Do not use Supabase service-role keys in browser code.
  • Use the anon key only with strict RLS policies.

Human access:

  • Require MFA for Supabase, Vercel, GitHub, and domain/DNS accounts.
  • Keep a written list of production admins.
  • Remove access when a person leaves the project.
  • Review admin access monthly.
  • Use individual accounts, not shared logins.

GitHub and deployment:

  • Protect the main branch.
  • Require pull request review before production deploys.
  • Require CI to pass before merge.
  • Keep production deploy permissions limited.
  • Use preview deployments for pull requests.
  • Keep database migrations in source control.
  • Review migrations before deploy, especially destructive changes.

Production change process:

flowchart LR
  Dev["Developer Branch"] --> PR["Pull Request"]
  PR --> CI["Lint / Typecheck / Tests"]
  CI --> Review["Code + Migration Review"]
  Review --> Preview["Preview Deploy"]
  Preview --> Merge["Merge to Main"]
  Merge --> Migration["Run DB Migration"]
  Migration --> Prod["Production Deploy"]
  Prod --> Monitor["Monitor Logs + Metrics"]

Migration Process​

Rules:

  • Every schema change must be a migration.
  • Test migrations against local or staging first.
  • Avoid destructive migrations during normal deploys.
  • For large tables, prefer expand-and-contract migrations.
  • Add columns as nullable first, backfill in batches, then enforce constraints.
  • Create indexes concurrently where supported.
  • Keep rollback notes for every risky migration.

Safe migration pattern:

1. Expand schema.
2. Deploy code that writes both old and new shapes if needed.
3. Backfill data.
4. Validate counts and constraints.
5. Switch reads to new shape.
6. Remove old shape in a later deploy.

Scalability Plan​

Database design:

  • Put campaign_id on every campaign-owned table.
  • Index common filters with campaign_id first.
  • Use PostGIS indexes for voter locations, turf polygons, districts, and launch sites.
  • Keep large import files out of Postgres rows.
  • Store raw import files in Supabase Storage or object storage.
  • Use background jobs for imports, geocoding, deduplication, and large exports.

Important indexes:

create index voters_campaign_last_name_idx on voters (campaign_id, last_name);
create index voters_campaign_external_id_idx on voters (campaign_id, external_voter_id);
create index voter_locations_campaign_location_idx on voter_locations using gist (location);
create index turfs_campaign_polygon_idx on turfs using gist (polygon);
create index contact_attempts_campaign_voter_created_idx on contact_attempts (campaign_id, voter_id, created_at desc);

Performance practices:

  • Paginate voter search results.
  • Avoid loading entire voter files into the browser.
  • Use server-side filtering for lists.
  • Cache expensive dashboard counts.
  • Track slow queries.
  • Add read replicas only after query and index tuning.
  • Keep analytics/reporting queries away from hot transactional paths when they get heavy.

Growth path:

flowchart TD
  Start["Supabase Pro: one shared project"] --> Tune["Indexes, RLS, query tuning"]
  Tune --> Jobs["Background imports and exports"]
  Jobs --> PITR["Longer PITR + stronger restore process"]
  PITR --> Replica["Read replicas for reporting"]
  Replica --> Dedicated["Dedicated compute / higher tier"]
  Dedicated --> Enterprise["Move to AWS RDS/Aurora if needed"]

Audit and Incident Logging​

We should keep audit logs for sensitive actions.

Audit events:

  • User invited to campaign.
  • User role changed.
  • User removed from campaign.
  • BlueNorthAdmin viewed campaign.
  • BlueNorthAdmin changed campaign state.
  • Voter import started/completed/failed.
  • Bulk voter data changed.
  • Turf deleted.
  • List deleted.
  • Export created.
  • Production setting changed.

audit_events

  • id uuid primary key
  • campaign_id uuid references campaigns(id)
  • actor_user_id uuid references profiles(id)
  • action text not null
  • target_type text
  • target_id uuid
  • metadata jsonb not null default '{}'
  • created_at timestamptz not null

Platform-wide audit events can have campaign_id = null.

Open Decisions​

  • Whether to keep district boundaries globally reusable or copy them per campaign.
  • Whether BlueNorthAdmin write access should be always available or require temporary support elevation.
  • Whether each campaign should get an exportable data package for portability.
  • Whether high-risk campaigns need separate Supabase projects or dedicated databases.
  • Whether contact history needs append-only protections after launch.
  • Whether analytics should stay in Postgres or move to a warehouse later.

First Implementation Slice​

The first production database slice should be small and valuable:

  1. Add Supabase project and local CLI setup.
  2. Create auth, profiles, campaigns, campaign memberships, and platform admins.
  3. Move saved voter lists from localStorage to Postgres.
  4. Add RLS tests for campaign isolation.
  5. Add a minimal BlueNorthAdmin campaign dashboard.
  6. Add backup/PITR setup notes and a first restore drill.

This gives us the foundation for every other data feature without migrating the entire prototype at once.