-- token_lineage: 2026-10 pre-deploy fixes (AUTH-P2, AUTH-P4).
-- AUTH-P2: SQLite recursive triggers are off, so deleting a child inside the trigger never fires the trigger
-- for the child's own children. The trigger therefore resolves every descendant (REST-minted children via
-- access_tokens.parent_token_id, MCP delegations via mcp_token_agents.parent_token_id) in one recursive query.
DROP TRIGGER access_tokens_revoke_mcp;
CREATE TRIGGER access_tokens_revoke_mcp BEFORE DELETE ON access_tokens
BEGIN
DELETE FROM oauth_refresh_tokens WHERE access_token_id = OLD.id OR access_token_id IN (
WITH RECURSIVE edges(child, parent) AS (
SELECT id, parent_token_id FROM access_tokens WHERE parent_token_id IS NOT NULL
UNION SELECT token_id, parent_token_id FROM mcp_token_agents WHERE parent_token_id IS NOT NULL
), descendants(id) AS (
SELECT child FROM edges WHERE parent = OLD.id
UNION SELECT edges.child FROM edges JOIN descendants ON edges.parent = descendants.id
) SELECT id FROM descendants);
DELETE FROM deploy_tokens WHERE parent_token_id = OLD.id OR parent_token_id IN (
WITH RECURSIVE edges(child, parent) AS (
SELECT id, parent_token_id FROM access_tokens WHERE parent_token_id IS NOT NULL
UNION SELECT token_id, parent_token_id FROM mcp_token_agents WHERE parent_token_id IS NOT NULL
), descendants(id) AS (
SELECT child FROM edges WHERE parent = OLD.id
UNION SELECT edges.child FROM edges JOIN descendants ON edges.parent = descendants.id
) SELECT id FROM descendants);
DELETE FROM access_tokens WHERE id IN (
WITH RECURSIVE edges(child, parent) AS (
SELECT id, parent_token_id FROM access_tokens WHERE parent_token_id IS NOT NULL
UNION SELECT token_id, parent_token_id FROM mcp_token_agents WHERE parent_token_id IS NOT NULL
), descendants(id) AS (
SELECT child FROM edges WHERE parent = OLD.id
UNION SELECT edges.child FROM edges JOIN descendants ON edges.parent = descendants.id
) SELECT id FROM descendants);
END;
-- AUTH-P2: delegated tokens minted before this migration only recorded their parent in mcp_token_agents.
UPDATE access_tokens SET parent_token_id = (
SELECT m.parent_token_id FROM mcp_token_agents m WHERE m.token_id = access_tokens.id
) WHERE parent_token_id IS NULL
AND EXISTS (SELECT 1 FROM mcp_token_agents m WHERE m.token_id = access_tokens.id AND m.parent_token_id IS NOT NULL);