james.maesandClaude Opus 5.5
877b6eb6fb
Declare a named foreign key for every join and table possible-value field between the ten email tables, plus suppression.source_message_id. Owned rows cascade, providers/brands/templates with history are RESTRICT, and optional pointers (template versions, sender address, suppression sources) are SET NULL so message retention keeps working. The provider delete check also covers attempts, events and inbox rows. The upgrade script quarantines orphans before adding the keys; a Testcontainers guardrail test fails when a new relation has no key. Co-Authored-By: Claude Opus 5.5 <noreply@anthropic.com>
90 lines
6.2 KiB
PL/PgSQL
90 lines
6.2 KiB
PL/PgSQL
-- qbit-email: add the foreign keys to an existing PostgreSQL schema (one
|
|
-- created from email-postgres.sql before they were declared there). Pause
|
|
-- enqueue, dispatch and webhook processing while it runs.
|
|
--
|
|
-- Safe to run more than once: a foreign key that already exists with the
|
|
-- right ON DELETE (under any name) is left alone. Before each key is added,
|
|
-- rows that point at a missing parent are recorded in qqq_fk_quarantine and
|
|
-- then set to NULL (nullable column) or deleted (NOT NULL column; the whole
|
|
-- row is kept in row_data). An existing key on the column with a different
|
|
-- ON DELETE is replaced by the named one.
|
|
--
|
|
-- Order matters: sender addresses, attempts and events of a missing parent are
|
|
-- removed before the references to them (message.from_address_id,
|
|
-- suppression.source_event_id) are checked.
|
|
|
|
BEGIN;
|
|
|
|
CREATE TABLE IF NOT EXISTS qqq_fk_quarantine (id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY, table_name VARCHAR(100) NOT NULL, column_name VARCHAR(100) NOT NULL, row_id VARCHAR(100), missing_parent_id VARCHAR(100), action VARCHAR(20) NOT NULL, row_data TEXT, changeset VARCHAR(200), quarantined_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP);
|
|
|
|
CREATE OR REPLACE FUNCTION pg_temp.qqq_add_fk(p_table TEXT, p_column TEXT, p_parent TEXT, p_on_delete TEXT) RETURNS VOID LANGUAGE plpgsql AS
|
|
$fn$
|
|
DECLARE
|
|
v_name TEXT := 'fk_' || p_table || '_' || p_column;
|
|
v_rule "char" := CASE p_on_delete WHEN 'CASCADE' THEN 'c' WHEN 'SET NULL' THEN 'n' WHEN 'RESTRICT' THEN 'r' ELSE 'a' END;
|
|
v_nullable BOOLEAN;
|
|
v_changeset TEXT := 'qbit-email:email-foreign-keys-upgrade.sql';
|
|
v_old RECORD;
|
|
BEGIN
|
|
IF EXISTS (SELECT 1 FROM pg_constraint c JOIN pg_attribute a ON a.attrelid = c.conrelid AND a.attnum = c.conkey[1]
|
|
WHERE c.contype = 'f' AND c.conrelid = p_table::regclass AND cardinality(c.conkey) = 1 AND a.attname = p_column
|
|
AND c.confrelid = p_parent::regclass AND c.confdeltype = v_rule) THEN
|
|
RETURN;
|
|
END IF;
|
|
|
|
FOR v_old IN SELECT c.conname FROM pg_constraint c JOIN pg_attribute a ON a.attrelid = c.conrelid AND a.attnum = c.conkey[1]
|
|
WHERE c.contype = 'f' AND c.conrelid = p_table::regclass AND cardinality(c.conkey) = 1 AND a.attname = p_column LOOP
|
|
EXECUTE format('ALTER TABLE %I DROP CONSTRAINT %I', p_table, v_old.conname);
|
|
END LOOP;
|
|
|
|
SELECT is_nullable = 'YES' INTO v_nullable FROM information_schema.columns
|
|
WHERE table_schema = current_schema() AND table_name = p_table AND column_name = p_column;
|
|
|
|
IF v_nullable THEN
|
|
EXECUTE format('INSERT INTO qqq_fk_quarantine (table_name, column_name, row_id, missing_parent_id, action, changeset) '
|
|
|| 'SELECT %L, %L, t.id::TEXT, t.%I::TEXT, ''NULLED'', %L FROM %I t WHERE t.%I IS NOT NULL AND NOT EXISTS (SELECT 1 FROM %I p WHERE p.id = t.%I)',
|
|
p_table, p_column, p_column, v_changeset, p_table, p_column, p_parent, p_column);
|
|
EXECUTE format('UPDATE %I t SET %I = NULL WHERE t.%I IS NOT NULL AND NOT EXISTS (SELECT 1 FROM %I p WHERE p.id = t.%I)',
|
|
p_table, p_column, p_column, p_parent, p_column);
|
|
ELSE
|
|
EXECUTE format('INSERT INTO qqq_fk_quarantine (table_name, column_name, row_id, missing_parent_id, action, row_data, changeset) '
|
|
|| 'SELECT %L, %L, t.id::TEXT, t.%I::TEXT, ''DELETED'', row_to_json(t)::TEXT, %L FROM %I t WHERE NOT EXISTS (SELECT 1 FROM %I p WHERE p.id = t.%I)',
|
|
p_table, p_column, p_column, v_changeset, p_table, p_parent, p_column);
|
|
EXECUTE format('DELETE FROM %I t WHERE NOT EXISTS (SELECT 1 FROM %I p WHERE p.id = t.%I)', p_table, p_parent, p_column);
|
|
END IF;
|
|
|
|
EXECUTE format('ALTER TABLE %I ADD CONSTRAINT %I FOREIGN KEY (%I) REFERENCES %I (id) ON DELETE %s', p_table, v_name, p_column, p_parent, p_on_delete);
|
|
END
|
|
$fn$;
|
|
|
|
SELECT pg_temp.qqq_add_fk('email_provider_sender_address', 'provider_id', 'email_provider', 'CASCADE');
|
|
SELECT pg_temp.qqq_add_fk('email_message', 'provider_id', 'email_provider', 'RESTRICT');
|
|
SELECT pg_temp.qqq_add_fk('email_message', 'from_address_id', 'email_provider_sender_address', 'SET NULL');
|
|
SELECT pg_temp.qqq_add_fk('email_message', 'template_id', 'email_template', 'SET NULL');
|
|
SELECT pg_temp.qqq_add_fk('email_message', 'template_version_id', 'email_template_version', 'SET NULL');
|
|
SELECT pg_temp.qqq_add_fk('email_message', 'brand_id', 'email_brand', 'SET NULL');
|
|
SELECT pg_temp.qqq_add_fk('email_template', 'brand_id', 'email_brand', 'RESTRICT');
|
|
SELECT pg_temp.qqq_add_fk('email_template', 'active_version_id', 'email_template_version', 'SET NULL');
|
|
SELECT pg_temp.qqq_add_fk('email_template', 'approved_version_id', 'email_template_version', 'SET NULL');
|
|
SELECT pg_temp.qqq_add_fk('email_template_version', 'template_id', 'email_template', 'RESTRICT');
|
|
SELECT pg_temp.qqq_add_fk('email_message_attempt', 'message_id', 'email_message', 'CASCADE');
|
|
SELECT pg_temp.qqq_add_fk('email_message_attempt', 'provider_id', 'email_provider', 'RESTRICT');
|
|
SELECT pg_temp.qqq_add_fk('email_event', 'message_id', 'email_message', 'CASCADE');
|
|
SELECT pg_temp.qqq_add_fk('email_event', 'provider_id', 'email_provider', 'RESTRICT');
|
|
SELECT pg_temp.qqq_add_fk('email_webhook_inbox', 'provider_id', 'email_provider', 'RESTRICT');
|
|
SELECT pg_temp.qqq_add_fk('email_suppression', 'source_message_id', 'email_message', 'SET NULL');
|
|
SELECT pg_temp.qqq_add_fk('email_suppression', 'source_event_id', 'email_event', 'SET NULL');
|
|
|
|
CREATE INDEX IF NOT EXISTS email_message_from_address ON email_message (from_address_id);
|
|
CREATE INDEX IF NOT EXISTS email_message_template ON email_message (template_id);
|
|
CREATE INDEX IF NOT EXISTS email_message_template_version ON email_message (template_version_id);
|
|
CREATE INDEX IF NOT EXISTS email_message_brand ON email_message (brand_id);
|
|
CREATE INDEX IF NOT EXISTS email_template_brand ON email_template (brand_id);
|
|
CREATE INDEX IF NOT EXISTS email_template_active_version ON email_template (active_version_id);
|
|
CREATE INDEX IF NOT EXISTS email_template_approved_version ON email_template (approved_version_id);
|
|
CREATE INDEX IF NOT EXISTS email_suppression_source_message ON email_suppression (source_message_id);
|
|
CREATE INDEX IF NOT EXISTS email_suppression_source_event ON email_suppression (source_event_id);
|
|
CREATE INDEX IF NOT EXISTS email_event_provider ON email_event (provider_id);
|
|
|
|
COMMIT;
|