james.maesandClaude Opus 5.5
a160ef216d
Declare a named foreign key for every join and table possible-value field between the five webhooks tables. Endpoint secrets and subscriptions are deleted with their endpoint; deliveries and attempts keep it (RESTRICT); a delivery outlives its subscription (SET NULL); attempts go with their delivery. The upgrade script quarantines orphans before adding the keys and replaces the NO ACTION endpoint keys. A Testcontainers guardrail test fails when a new relation has no key. Co-Authored-By: Claude Opus 5.5 <noreply@anthropic.com>
69 lines
4.3 KiB
PL/PgSQL
69 lines
4.3 KiB
PL/PgSQL
-- qbit-webhooks: add the foreign keys to an existing PostgreSQL schema
|
|
-- (one created from webhooks-postgres.sql before they were declared there).
|
|
--
|
|
-- 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 (endpoint_secret and subscription -> endpoint were NO ACTION) is
|
|
-- replaced by the named one.
|
|
--
|
|
-- Order matters: deliveries of a missing endpoint are removed before their
|
|
-- attempts 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-webhooks:webhooks-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('webhooks_endpoint_secret', 'endpoint_id', 'webhooks_endpoint', 'CASCADE');
|
|
SELECT pg_temp.qqq_add_fk('webhooks_subscription', 'endpoint_id', 'webhooks_endpoint', 'CASCADE');
|
|
SELECT pg_temp.qqq_add_fk('webhooks_delivery', 'endpoint_id', 'webhooks_endpoint', 'RESTRICT');
|
|
SELECT pg_temp.qqq_add_fk('webhooks_delivery', 'subscription_id', 'webhooks_subscription', 'SET NULL');
|
|
SELECT pg_temp.qqq_add_fk('webhooks_delivery_attempt', 'delivery_id', 'webhooks_delivery', 'CASCADE');
|
|
SELECT pg_temp.qqq_add_fk('webhooks_delivery_attempt', 'endpoint_id', 'webhooks_endpoint', 'RESTRICT');
|
|
|
|
CREATE INDEX IF NOT EXISTS webhooks_delivery_subscription_ix ON webhooks_delivery (subscription_id);
|
|
|
|
COMMIT;
|