Fk Dependency Graph

FK Dependency Graph

Overview

This skill provides foreign key dependency graph analysis and resolution for the Paperclip database schema. With 237+ FK relationships across ~80+ tables, understanding the dependency graph is critical for safe CRUD operations. This skill builds ordered dependency lists for any target table, identifying child-parent chains, cascade rules, and potential conflict points.

Routing Criteria

Route to this skill when:

• Determining safe deletion order for any table
• Analyzing FK dependency chains across multiple tables
• Validating FK integrity before bulk operations
• Planning schema changes that affect FK relationships
• Debugging FK violation errors
• Building ordered migration scripts
• Any operation requiring knowledge of "what references this table"

FK Graph Structure

Core Dependency Patterns

The Paperclip schema follows these key FK patterns:

Central Hub Tables (referenced by 10+ tables):

• companies — ~25+ FK references (the root of nearly everything)
• agents — ~20+ FK references (heartbeat_runs, agent_api_keys, issues, activity_log, etc.)
• issues — ~15+ FK references (issue_attachments, issue_comments, issue_labels, etc.)
• heartbeat_runs — ~8+ FK references (heartbeat_run_events, cost_events, etc.)

FK Rule Distribution (from the 237 FKs):

• NO ACTION — ~70% (must delete children explicitly before parent)
• CASCADE — ~20% (auto-delete children when parent is deleted)
• SET NULL — ~10% (nullify FK reference on parent deletion)

Dependency Depth Algorithm

Level 0 (Leaf tables): tables with no FK references to other tables
Level 1: tables whose FKs only reference Level 0 or Level 1 tables
Level N: tables whose FKs reference tables at Level N-1 or below

For DELETE: Process Level N → N-1 → ... → 0 (deepest children first)
For INSERT: Process Level 0 → 1 → ... → N (parents first)

Capabilities

Dependency Graph Analysis

• FK Discovery: Query information_schema.table_constraints and information_schema.referential_constraints for all FK definitions
• Depth Calculation: Compute dependency depth for each table using recursive graph traversal
• Rule Classification: Classify each FK as CASCADE, SET NULL, NO ACTION, or RESTRICT
• Cycle Detection: Identify circular FK references (e.g., issues.parent_id → issues.id)
• Self-Reference Detection: Find self-referencing FKs (e.g., agents.reports_to → agents.id)

Deletion Order Generation

Given a target table, generate a complete ordered deletion sequence:

-- Example: Deletion order for 'agents' table
-- Generated by FK graph analysis

-- Level 4+ (deepest children)
DELETE FROM heartbeat_run_events WHERE run_id IN (SELECT id FROM heartbeat_runs WHERE agent_id = <id>);
DELETE FROM agent_config_revisions WHERE agent_id = <id>;
DELETE FROM agent_wakeup_requests WHERE agent_id = <id>;

-- Level 3
DELETE FROM agent_task_sessions WHERE agent_id = <id>;
DELETE FROM agent_runtime_state WHERE agent_id = <id>;

-- Level 2
DELETE FROM agent_api_keys WHERE agent_id = <id>;
DELETE FROM agent_skill_assignments WHERE agent_id = <id>;
UPDATE heartbeat_runs SET agent_id = NULL WHERE agent_id = <id>;
UPDATE activity_log SET agent_id = NULL WHERE agent_id = <id>;

-- Level 1 (self-referencing)
UPDATE agents SET reports_to = NULL WHERE reports_to = <id>;

-- Level 0 (target)
DELETE FROM agents WHERE id = <id>;

Insertion Order Generation

Given a target table, generate the insert sequence:

-- Example: Insertion order for 'agents' table
-- Parents must exist before children

-- Level 0 (root - no FK dependencies)
-- companies must exist first

-- Level 1 (one FK dependency)
-- agents references companies(id) and optionally agents(id) for reports_to
-- So companies must exist, and manager agents must already exist

Integration with Existing Tools

FK CSV Mapping

The complete FK relationship map is maintained in: /docs-paperclip/schema/FK table relationships.csv

This CSV contains all 237 FK relationships with:

• Source table and column
• Referenced table and column
• Delete rule (CASCADE, SET NULL, NO ACTION)
• Update rule

Information Schema Queries

For live FK graph analysis:

-- Get all FK relationships for a specific table
SELECT
    tc.table_name AS source_table,
    kcu.column_name AS source_column,
    ccu.table_name AS referenced_table,
    ccu.column_name AS referenced_column,
    rc.delete_rule,
    rc.update_rule
FROM information_schema.table_constraints tc
JOIN information_schema.key_column_usage kcu
    ON tc.constraint_name = kcu.constraint_name
    AND tc.table_schema = kcu.table_schema
JOIN information_schema.constraint_column_usage ccu
    ON ccu.constraint_name = tc.constraint_name
    AND ccu.table_schema = tc.table_schema
JOIN information_schema.referential_constraints rc
    ON rc.constraint_name = tc.constraint_name
    AND rc.constraint_schema = tc.table_schema
WHERE tc.constraint_type = 'FOREIGN KEY'
    AND tc.table_schema = 'public'
    AND tc.table_name = 'agents'
ORDER BY tc.table_name;

-- Get all tables that reference a specific table
SELECT
    tc.table_name AS referencing_table,
    kcu.column_name AS referencing_column
FROM information_schema.table_constraints tc
JOIN information_schema.key_column_usage kcu
    ON tc.constraint_name = kcu.constraint_name
JOIN information_schema.constraint_column_usage ccu
    ON ccu.constraint_name = tc.constraint_name
WHERE tc.constraint_type = 'FOREIGN KEY'
    AND ccu.table_schema = 'public'
    AND ccu.table_name = 'agents'
ORDER BY tc.table_name;

Common FK Graph Patterns

Agent Deletion Chain

companies {id}
    ↑
    ├── agents {company_id}
    │   ↑
    │   ├── agent_api_keys {agent_id}
    │   ├── agent_config_revisions {agent_id}  [CASCADE]
    │   ├── agent_runtime_state {agent_id}
    │   ├── agent_wakeup_requests {agent_id}
    │   ├── heartbeat_runs {agent_id}
    │   │   ↑
    │   │   └── heartbeat_run_events {run_id}
    │   ├── agent_task_sessions {agent_id}
    │   ├── activity_log {agent_id}
    │   ├── issues {assignee_agent_id} [nullable]
    │   └── agents {reports_to} [self-ref, nullable]
    │
    └── agent_models {company_id}

Issue Deletion Chain

issues {id}
    ↑
    ├── issue_attachments {issue_id}  [CASCADE]
    ├── issue_comments {issue_id}
    ├── issue_labels {issue_id}  [CASCADE]
    ├── issue_relations {issue_id}  [CASCADE]
    │                      └── {related_issue_id}  [CASCADE]
    ├── issue_work_products {issue_id}  [CASCADE]
    ├── issue_inbox_archives {issue_id}
    ├── issue_execution_decisions {issue_id}  [CASCADE]
    └── issues {parent_id}  [self-ref]

Related Skills

• crud-operations - Core CRUD implementation using FK graph
• foreign-key-dependency-management - Shared FK mapping (complementary)
• database-schema-inspection - Schema analysis providing FK metadata
• drizzle-crud-templates - Code generation using FK graph resolutions

This skill provides the FK graph intelligence needed for safe, ordered database operations across Paperclip's complex schema.