lti-api/internal/database/migrations/20260102045853_fix_soft_delete_fk_casts.up.sql

CREATE OR REPLACE FUNCTION soft_delete_handle_fk() RETURNS TRIGGER AS $$
DECLARE
    fk record;
    child_column text;
    parent_column text;
    parent_value text;
    child_has_deleted_at boolean;
    ref_exists boolean;
    sql text;
    child_type text;
BEGIN
    IF OLD.deleted_at IS NULL AND NEW.deleted_at IS NOT NULL THEN
        FOR fk IN
            SELECT conrelid::regclass AS child_table,
                   conkey AS child_cols,
                   confkey AS parent_cols,
                   confdeltype
            FROM pg_constraint
            WHERE contype = 'f'
              AND confrelid = TG_RELID
        LOOP
            IF array_length(fk.child_cols, 1) IS DISTINCT FROM 1
                OR array_length(fk.parent_cols, 1) IS DISTINCT FROM 1 THEN
                RAISE NOTICE 'soft_delete_handle_fk skipped composite fk on %', fk.child_table;
                CONTINUE;
            END IF;

            SELECT attname INTO child_column
            FROM pg_attribute
            WHERE attrelid = fk.child_table
              AND attnum = fk.child_cols[1]
              AND NOT attisdropped;

            SELECT attname INTO parent_column
            FROM pg_attribute
            WHERE attrelid = TG_RELID
              AND attnum = fk.parent_cols[1]
              AND NOT attisdropped;

            SELECT format_type(atttypid, atttypmod) INTO child_type
            FROM pg_attribute
            WHERE attrelid = fk.child_table
              AND attname = child_column
              AND NOT attisdropped;

            IF child_type IS NULL THEN
                child_type := 'text';
            END IF;

            EXECUTE format('SELECT ($1).%I', parent_column)
                INTO parent_value
                USING OLD;

            SELECT EXISTS (
                SELECT 1
                FROM pg_attribute
                WHERE attrelid = fk.child_table
                  AND attname = 'deleted_at'
                  AND NOT attisdropped
            ) INTO child_has_deleted_at;

            IF fk.confdeltype IN ('r', 'a') THEN
                sql := format(
                    'SELECT EXISTS (SELECT 1 FROM %s WHERE %I = $1::%s %s)',
                    fk.child_table,
                    child_column,
                    child_type,
                    CASE WHEN child_has_deleted_at THEN 'AND deleted_at IS NULL' ELSE '' END
                );
                EXECUTE sql INTO ref_exists USING parent_value;
                IF ref_exists THEN
                    RAISE EXCEPTION 'Cannot soft delete %, still referenced by %',
                        TG_TABLE_NAME, fk.child_table;
                END IF;
            ELSIF fk.confdeltype = 'n' THEN
                sql := format(
                    'UPDATE %s SET %I = NULL WHERE %I = $1::%s %s',
                    fk.child_table,
                    child_column,
                    child_column,
                    child_type,
                    CASE WHEN child_has_deleted_at THEN 'AND deleted_at IS NULL' ELSE '' END
                );
                EXECUTE sql USING parent_value;
            ELSIF fk.confdeltype = 'c' THEN
                IF child_has_deleted_at THEN
                    sql := format(
                        'UPDATE %s SET deleted_at = NOW() WHERE %I = $1::%s AND deleted_at IS NULL',
                        fk.child_table,
                        child_column,
                        child_type
                    );
                    EXECUTE sql USING parent_value;
                ELSE
                    sql := format(
                        'DELETE FROM %s WHERE %I = $1::%s',
                        fk.child_table,
                        child_column,
                        child_type
                    );
                    EXECUTE sql USING parent_value;
                END IF;
            ELSIF fk.confdeltype = 'd' THEN
                sql := format(
                    'UPDATE %s SET %I = DEFAULT WHERE %I = $1::%s %s',
                    fk.child_table,
                    child_column,
                    child_column,
                    child_type,
                    CASE WHEN child_has_deleted_at THEN 'AND deleted_at IS NULL' ELSE '' END
                );
                EXECUTE sql USING parent_value;
            END IF;
        END LOOP;
    END IF;

    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

DO $$
DECLARE
    r record;
    trigger_name text;
BEGIN
    FOR r IN
        SELECT table_schema, table_name
        FROM information_schema.columns
        WHERE column_name = 'deleted_at'
          AND table_schema = 'public'
        GROUP BY table_schema, table_name
    LOOP
        trigger_name := format('trg_soft_delete_fk_%s', r.table_name);
        EXECUTE format('DROP TRIGGER IF EXISTS %I ON %I.%I', trigger_name, r.table_schema, r.table_name);
        EXECUTE format(
            'CREATE TRIGGER %I BEFORE UPDATE OF deleted_at ON %I.%I FOR EACH ROW EXECUTE FUNCTION soft_delete_handle_fk()',
            trigger_name,
            r.table_schema,
            r.table_name
        );
    END LOOP;
END $$;