Database Architecture¶

Campaign Brain uses a mix of SQLite and DuckDB databases across its services. This page documents all active databases, their schemas, and purposes.

Database Technology¶

Technology	Use Case	Projects
DuckDB	Analytical queries, large datasets, vector embeddings	cbapp, cbdistricts, cbradio, cbsurveys
SQLite	Transactional data, service management	cbtenant

By Project¶

cbtenant - Tenant Manager¶

Database	Path	Size	Type	Purpose
`manager.db`	`db/manager.db`	~276 KB	SQLite	Tenant management, deployments, themes

Tables:

Table	Description
`users`	Tenant manager admin accounts
`tenants`	Tenant definitions (name, slug, domain, ports)
`tenant_configs`	Environment settings per tenant
`tenant_themes`	Branding colors, logos, fonts
`deployments`	Deployment history and status
`ai_analysis`	Claude-generated theme suggestions
`theme_suggestions`	AI theme options
`logs`	Audit trail and system logs
`port_allocations`	Reserved port ranges
`settings`	Global configuration

cbapp - Campaign Application¶

Database	Path	Size	Type	Purpose
`pocket.db`	`db/pocket.db`	~195 MB (dev) / 5+ GB (prod)	DuckDB	Contacts, events, communications

Tables:

Table	Description
`person`	Contact records with address, phone, email
`custom_field`	User-defined field definitions
`person_custom_field`	Custom field values per contact
`tag`	Categorization labels
`record_tag`	Universal tag associations
`person_tag`	Legacy contact-tag links
`whip_status`	Support level tracking
`communication`	Email, text, call records
`event`	Campaign events
`event_registration`	Event RSVPs and check-ins
`user`	Campaign app user accounts
`goal`	Progress tracking
`organization`	Organization records
`user_organization`	User-org relationships
`template`	Communication templates
`file`	Uploaded files
`segment`	Audience segments (SQL-based)
`segment_person`	Static segment membership
`segment_execution_log`	Segment query history
`segment_survey`	Segment-to-survey links

Graph Support: Uses DuckPGQ extension for property graph queries (campaign_graph).

cbradio - Radio Advertising¶

Database	Path	Size	Type	Purpose
`cbradio.db`	`db/cbradio.db`	~22 MB	DuckDB	Stations, rate cards, documents

Tables:

Table	Description
`user`	Radio app user accounts
`station`	Radio station info (callsign, format, location, contacts)
`document`	Source PDFs, rate cards
`rate_card`	Rate card groupings by station/year
`rate`	Individual advertising rates
`extraction_log`	OCR/AI extraction history

cbsurveys - Survey Platform¶

Database	Path	Size	Type	Purpose
`registry.duckdb`	`data/registry.duckdb`	~4.3 MB	DuckDB	Central catalog (users, API keys, survey index)
`{survey_id}.duckdb`	`data/surveys/{id}.duckdb`	Varies	DuckDB	Per-survey definitions and responses

Architecture: One database per survey for data isolation.

Registry Tables:

Table	Description
`users`	Survey platform users
`api_keys`	Hashed API keys (`yasp_` prefix)
`surveys`	Survey metadata

cbdistricts - District Data¶

Database	Path	Size	Type	Purpose
`cbdistricts.duckdb`	`data/output/cbdistricts.duckdb`	~23 MB	DuckDB	Aggregated district data
`congressional_119.duckdb`	`data/layers/federal/congressional_119.duckdb`	~12 MB	DuckDB	119^th Congress boundaries
`congressional.duckdb`	`data/layers/mi/congressional.duckdb`	~2.3 MB	DuckDB	MI congressional districts
`house.duckdb`	`data/layers/mi/house.duckdb`	~3.3 MB	DuckDB	MI state house districts
`senate.duckdb`	`data/layers/mi/senate.duckdb`	~2.6 MB	DuckDB	MI state senate districts

Data Sources: Census Bureau, TIGER/Line boundaries, ACS profiles.

i360 Voter Data¶

The i360 database contains voter file data used for contact matching and enrichment.

Database	Path	Size	Type	Purpose
`i360.db`	`/opt/campaignbrain/shared/data/i360.db`	~2.1 GB	DuckDB	Voter file records

Access: Symlinked into tenant directories (e.g., ky04/db/i360.db).

Contents: - Voter registration records - Contact information - Voting history - Demographics

Integration: Used via cbapp/src/api/routes/i360.py for: - Contact lookup and matching - Voter file search - Data enrichment

See i360 Integration Guide for usage details.

Tenant Databases¶

Each deployed tenant (cbapp instance) has its own databases:

Tenant	pocket.db Size	i360 Access
ky04	~5.1 GB	Yes (symlink)
mi20-clevenger	Varies	Yes (symlink)
testsite	Varies	No

Backup Strategy: Automatic backups created during upgrades (pocket.db.backup_{timestamp}).

Database Locations¶

Development¶

/home/bisenbek/projects/nominate/
├── cbapp/db/
│   └── pocket.db          # Dev campaign data
├── cbtenant/db/
│   └── manager.db         # Tenant management
├── cbradio/db/
│   └── cbradio.db         # Radio stations/rates
├── cbsurveys/data/
│   ├── registry.duckdb    # Survey catalog
│   └── surveys/           # Per-survey databases
└── cbdistricts/data/
    ├── output/cbdistricts.duckdb
    └── layers/            # Geographic boundaries

Production (Tenants)¶

/home/bisenbek/projects/nominate/
├── ky04/db/
│   ├── pocket.db          # KY-04 campaign data
│   └── i360.db -> /opt/campaignbrain/shared/data/i360.db
├── mi20-clevenger/db/
│   ├── pocket.db          # MI-20 campaign data
│   └── i360.db -> /opt/campaignbrain/shared/data/i360.db
└── testsite/db/
    └── pocket.db          # Test environment

Shared Data¶

/opt/campaignbrain/shared/data/
└── i360.db                # Shared voter file (2.1 GB)

Schema Management¶

Project	Schema File	Create Command
cbtenant	`db/schema.sql`	`python scripts/create_schema.py`
cbapp	`scripts/create_schema.py`	`python scripts/create_schema.py`
cbradio	`scripts/create_schema.py`	`python scripts/create_schema.py`
cbsurveys	Built-in migrations	Auto-created on first run

Quick Commands¶

Connect to Databases¶

# SQLite (cbtenant)
sqlite3 /home/bisenbek/projects/nominate/cbtenant/db/manager.db

# DuckDB (cbapp)
duckdb /home/bisenbek/projects/nominate/cbapp/db/pocket.db

# DuckDB (cbradio)
duckdb /home/bisenbek/projects/nominate/cbradio/db/cbradio.db

# DuckDB (i360 - read only!)
duckdb -readonly /opt/campaignbrain/shared/data/i360.db

List Tables¶

-- SQLite
.tables

-- DuckDB
SHOW TABLES;

View Schema¶

-- SQLite
.schema tablename

-- DuckDB
DESCRIBE tablename;