# Fk Dependency Graph

> Routes foreign key dependency graph analysis to the Database CRUD Orchestrator for determining safe operation ordering across Paperclip's 237+ FK relationships. Generates dependency-ordered delete sequences and validates FK integrity.

- Skill: `construct-ai-primary/fk-dependency-graph` (Agent Skill)
- Install (CLI): `npx skillmds@latest add construct-ai-primary/fk-dependency-graph`
- Raw SKILL.md: https://api.skillmd.com/api/skills/construct-ai-primary/fk-dependency-graph/raw
- Safety review: pending
- Works with: Claude Code, Claude.ai, OpenAI Codex
- Category: Coding & Dev Tools
- Author: Construct-AI-primary (https://skillmd.com/u/construct-ai-primary)
- Updated: 2026-09-08
- Page: https://skillmd.com/skills/construct-ai-primary/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:

```sql
-- 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:

```sql
-- 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:

```sql
-- 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;
```

```sql
-- 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.*