#!/usr/bin/env python3
"""
Migration: Drop plaintext PII columns, keep encrypted columns.
Tables affected:
- users: drop email, name, display_name
- webhook_destinations: drop url
- webhook_logs: drop webhook_url
- invoice_schedules: drop customer_name, customer_email
- team_members: drop invited_email
Backup first: cp relay.db relay.db.pre-cleanup
"""
import sqlite3
import sys
DB_PATH = "/home/vincent/projects/agentforms/data/relay.db"
def migrate():
conn = sqlite3.connect(DB_PATH)
conn.row_factory = sqlite3.Row
try:
# 1. Users table ā drop email, name, display_name
conn.execute("""
CREATE TABLE users_new (
id INTEGER PRIMARY KEY,
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,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
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
)
""")
conn.execute("""
INSERT INTO users_new (id, email_hash, email_encrypted, password_hash, name_encrypted, display_name_encrypted, tier, stripe_customer_id, stripe_subscription_id, created_at, email_verified, email_verification_token, email_verification_sent_at, pending_email, password_reset_token, password_reset_sent_at, magic_login_token, magic_login_sent_at, magic_login_enabled)
SELECT id, email_hash, email_encrypted, password_hash, name_encrypted, display_name_encrypted, tier, stripe_customer_id, stripe_subscription_id, created_at, email_verified, email_verification_token, email_verification_sent_at, pending_email, password_reset_token, password_reset_sent_at, magic_login_token, magic_login_sent_at, magic_login_enabled FROM users
""")
conn.execute("DROP TABLE users")
conn.execute("ALTER TABLE users_new RENAME TO users")
print("ā users: dropped email, name, display_name")
# 2. Webhook destinations ā drop url
conn.execute("""
CREATE TABLE webhook_destinations_new (
id INTEGER PRIMARY KEY AUTOINCREMENT,
site_id INTEGER NOT NULL,
name TEXT NOT NULL DEFAULT 'Integration',
type TEXT NOT NULL,
url_encrypted TEXT,
url_hash TEXT,
config TEXT,
enabled INTEGER DEFAULT 1,
last_status TEXT,
last_error TEXT,
last_delivered_at TIMESTAMP,
success_count INTEGER DEFAULT 0,
failure_count INTEGER DEFAULT 0,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
)
""")
conn.execute("""
INSERT INTO webhook_destinations_new (id, site_id, name, type, url_encrypted, url_hash, config, enabled, last_status, last_error, last_delivered_at, success_count, failure_count, created_at)
SELECT id, site_id, name, type, url_encrypted, url_hash, config, enabled, last_status, last_error, last_delivered_at, success_count, failure_count, created_at FROM webhook_destinations
""")
conn.execute("DROP TABLE webhook_destinations")
conn.execute("ALTER TABLE webhook_destinations_new RENAME TO webhook_destinations")
conn.execute("CREATE INDEX idx_destinations_site ON webhook_destinations(site_id)")
print("ā webhook_destinations: dropped url")
# 3. Webhook logs ā drop webhook_url
conn.execute("""
CREATE TABLE webhook_logs_new (
id INTEGER PRIMARY KEY AUTOINCREMENT,
site_id INTEGER NOT NULL,
submission_id INTEGER NOT NULL,
webhook_url_encrypted TEXT NOT NULL,
attempt_count INTEGER DEFAULT 0,
status TEXT DEFAULT 'pending',
last_error TEXT,
next_retry_at TIMESTAMP,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
destination_id INTEGER,
retrying_by TEXT
)
""")
conn.execute("""
INSERT INTO webhook_logs_new (id, site_id, submission_id, webhook_url_encrypted, attempt_count, status, last_error, next_retry_at, created_at, destination_id, retrying_by)
SELECT id, site_id, submission_id, webhook_url_encrypted, attempt_count, status, last_error, next_retry_at, created_at, destination_id, retrying_by FROM webhook_logs
""")
conn.execute("DROP TABLE webhook_logs")
conn.execute("ALTER TABLE webhook_logs_new RENAME TO webhook_logs")
conn.execute("CREATE INDEX idx_webhook_logs_retry ON webhook_logs(next_retry_at, status)")
conn.execute("CREATE INDEX idx_webhook_logs_destination ON webhook_logs(destination_id)")
print("ā webhook_logs: dropped webhook_url")
# 4. Invoice schedules ā drop customer_name, customer_email
conn.execute("""
CREATE TABLE invoice_schedules_new (
id INTEGER PRIMARY KEY AUTOINCREMENT,
site_id INTEGER NOT NULL,
template_id INTEGER NOT NULL,
customer_name_encrypted TEXT NOT NULL,
customer_email_encrypted TEXT NOT NULL,
line_items TEXT NOT NULL DEFAULT '[]',
document_number_prefix TEXT NOT NULL DEFAULT 'INV',
interval TEXT NOT NULL DEFAULT 'monthly',
interval_count INTEGER NOT NULL DEFAULT 1,
start_date DATE NOT NULL,
next_run DATE,
last_run DATE,
status TEXT NOT NULL DEFAULT 'active',
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
)
""")
conn.execute("""
INSERT INTO invoice_schedules_new (id, site_id, template_id, customer_name_encrypted, customer_email_encrypted, line_items, document_number_prefix, interval, interval_count, start_date, next_run, last_run, status, created_at, updated_at)
SELECT id, site_id, template_id, customer_name_encrypted, customer_email_encrypted, line_items, document_number_prefix, interval, interval_count, start_date, next_run, last_run, status, created_at, updated_at FROM invoice_schedules
""")
conn.execute("DROP TABLE invoice_schedules")
conn.execute("ALTER TABLE invoice_schedules_new RENAME TO invoice_schedules")
conn.execute("CREATE INDEX idx_invoice_schedules_site ON invoice_schedules(site_id)")
conn.execute("CREATE INDEX idx_invoice_schedules_next_run ON invoice_schedules(next_run)")
print("ā invoice_schedules: dropped customer_name, customer_email")
# 5. Team members ā drop invited_email
conn.execute("""
CREATE TABLE team_members_new (
id INTEGER PRIMARY KEY AUTOINCREMENT,
team_id INTEGER NOT NULL,
user_id INTEGER,
role TEXT NOT NULL DEFAULT 'member',
invited_email_encrypted TEXT,
invited_email_hash TEXT,
status TEXT NOT NULL DEFAULT 'pending',
invite_token TEXT UNIQUE,
joined_at TIMESTAMP,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
)
""")
conn.execute("""
INSERT INTO team_members_new (id, team_id, user_id, role, invited_email_encrypted, invited_email_hash, status, invite_token, joined_at, created_at)
SELECT id, team_id, user_id, role, invited_email_encrypted, invited_email_hash, status, invite_token, joined_at, created_at FROM team_members
""")
conn.execute("DROP TABLE team_members")
conn.execute("ALTER TABLE team_members_new RENAME TO team_members")
conn.execute("CREATE INDEX idx_team_members_team ON team_members(team_id)")
conn.execute("CREATE INDEX idx_team_members_user ON team_members(user_id)")
conn.execute("CREATE INDEX idx_team_members_token ON team_members(invite_token)")
print("ā team_members: dropped invited_email")
conn.commit()
print("\nā
All migrations complete")
# Verify
print("\nVerification:")
for table in ["users", "webhook_destinations", "webhook_logs", "invoice_schedules", "team_members"]:
cols = [row["name"] for row in conn.execute(f"PRAGMA table_info({table})").fetchall()]
print(f" {table}: {', '.join(cols)}")
except Exception as e:
conn.rollback()
print(f"ā Migration failed: {e}", file=sys.stderr)
raise
finally:
conn.close()
if __name__ == "__main__":
migrate()