"""Database initialization — schema creation and migration orchestration."""
import sqlite3
from app.db import get_db, write_lock
from app.migrations import (
migrate_add_abuse_protection,
migrate_add_agents,
migrate_add_ai_builder,
migrate_add_analytics,
migrate_add_api_keys,
migrate_add_branding,
migrate_add_document_generation_status,
migrate_add_document_templates,
migrate_add_email_campaigns,
migrate_add_email_phase3,
migrate_add_email_phase4,
migrate_add_email_tracking,
migrate_add_email_verification,
migrate_add_field_config,
migrate_add_form_actions,
migrate_add_form_analytics,
migrate_add_form_sessions,
migrate_add_form_templates,
migrate_add_form_variants,
migrate_add_form_versions,
migrate_add_indexes,
migrate_add_invite_expiration,
migrate_add_rate_limit,
migrate_add_submission_data,
migrate_add_teams,
migrate_add_usage,
migrate_add_user_id_to_doc_templates,
migrate_add_user_id_to_invoice_schedules,
migrate_add_user_id_to_sites,
migrate_add_user_referral_cols,
migrate_add_webhook_destinations,
migrate_add_webhook_retry_locking,
migrate_normalize_field_configs,
)
def init_db():
"""Initialize database schema and run all migrations. Idempotent."""
# Serialize schema changes across gunicorn workers + RQ worker
with write_lock():
conn = None
try:
conn = get_db()
conn.executescript("""
CREATE TABLE IF NOT EXISTS users (
id INTEGER PRIMARY KEY AUTOINCREMENT,
email_hash TEXT UNIQUE NOT NULL,
email_encrypted TEXT,
password_hash TEXT NOT NULL,
name_encrypted TEXT,
display_name_encrypted TEXT,
tier TEXT DEFAULT 'free',
stripe_customer_id TEXT,
stripe_subscription_id TEXT,
email_verified INTEGER DEFAULT 0,
email_verification_token TEXT,
email_verification_sent_at TIMESTAMP,
pending_email TEXT,
password_reset_token TEXT,
password_reset_sent_at TIMESTAMP,
magic_login_token TEXT,
magic_login_sent_at TIMESTAMP,
magic_login_enabled INTEGER DEFAULT 1,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
CREATE TABLE IF NOT EXISTS sites (
id INTEGER PRIMARY KEY AUTOINCREMENT,
token TEXT UNIQUE NOT NULL,
name TEXT NOT NULL,
owner_email TEXT NOT NULL,
smtp_from TEXT,
user_id INTEGER,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (user_id) REFERENCES users(id)
);
CREATE TABLE IF NOT EXISTS submissions (
id INTEGER PRIMARY KEY AUTOINCREMENT,
site_id INTEGER NOT NULL REFERENCES sites(id),
customer_name TEXT,
customer_phone TEXT,
customer_email TEXT,
customer_equipment TEXT,
customer_message TEXT,
submitted_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
CREATE INDEX IF NOT EXISTS idx_submissions_site_id ON submissions(site_id);
CREATE INDEX IF NOT EXISTS idx_submissions_created ON submissions(submitted_at DESC);
CREATE TABLE IF NOT EXISTS waitlist (
id INTEGER PRIMARY KEY AUTOINCREMENT,
email TEXT UNIQUE NOT NULL,
source TEXT DEFAULT 'landing',
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
CREATE INDEX IF NOT EXISTS idx_sites_user_id ON sites(user_id);
""")
conn.commit()
finally:
if conn:
conn.close()
# Run migrations (all idempotent — safe on both fresh and existing DBs)
migrate_add_field_config()
migrate_add_submission_data()
migrate_add_user_id_to_sites()
migrate_add_usage()
migrate_add_abuse_protection()
migrate_add_rate_limit()
migrate_add_analytics()
migrate_add_email_verification()
migrate_add_indexes()
migrate_add_api_keys()
migrate_normalize_field_configs()
migrate_add_form_templates()
migrate_add_teams()
migrate_add_form_analytics()
migrate_add_webhook_destinations()
migrate_add_ai_builder()
migrate_add_form_versions()
migrate_add_form_sessions()
migrate_add_form_variants()
migrate_add_branding()
migrate_add_webhook_retry_locking()
migrate_add_document_templates()
migrate_add_invite_expiration()
migrate_add_user_id_to_doc_templates()
migrate_add_user_id_to_invoice_schedules()
migrate_add_email_campaigns()
migrate_add_email_tracking()
migrate_add_email_phase3()
migrate_add_email_phase4()
migrate_add_form_actions()
migrate_add_user_referral_cols()
migrate_add_agents()
migrate_add_document_generation_status()
# Run last — encrypts existing plaintext PII data
try:
from app.migrations import migrate_encrypt_existing_data
migrate_encrypt_existing_data()
except (sqlite3.OperationalError, AttributeError):
pass
# Encrypt remaining plaintext PII (users, webhook destinations, invoice schedules, etc.)
try:
from app.migrations import migrate_encrypt_user_pii
migrate_encrypt_user_pii()
except (sqlite3.OperationalError, AttributeError):
pass
# Re-encrypt submissions from site-scoped keys to user-scoped keys
try:
from app.migrations import migrate_rescope_to_user_keys
migrate_rescope_to_user_keys()
except (sqlite3.OperationalError, AttributeError):
pass
# Rotate encryption keys if V2 is configured
try:
from app.migrations import migrate_rotate_encryption_keys
migrate_rotate_encryption_keys()
except (sqlite3.OperationalError, AttributeError):
pass