Mission Control — Database Schema

erDiagram
    developers {
        varchar id PK "DEV-xxxxxxxx"
        varchar clockify_id UK
        varchar name
        varchar email
        varchar github_username
        float cost_rate "hourly"
        float monthly_cost "NEW"
        varchar status
    }

    clients {
        varchar id PK "CLI-xxxxxxxx"
        varchar clockify_id UK
        varchar name
        varchar billing_model "retainer | hourly"
        float retainer_amount
        varchar parent_client_id FK
        varchar status
    }

    projects {
        varchar id PK "PRJ-xxxxxxxx"
        varchar clockify_id UK
        varchar client_id FK
        varchar name
        float hourly_rate "HMR only"
        varchar status
    }

    repo_mappings {
        serial id PK "NEW"
        varchar repo UK "org/repo-name"
        varchar client_id FK
        varchar project_id FK
    }

    clients ||--o{ projects : "has"
    clients ||--o| clients : "parent rollup"
    clients ||--o{ repo_mappings : "owns"
    projects ||--o{ repo_mappings : "mapped"
    

erDiagram
    claude_sessions {
        varchar id PK "session UUID"
        varchar developer_id FK
        varchar client_id FK
        varchar project_id FK
        varchar model
        int input_tokens
        int output_tokens
        int cache_read_tokens
        int cache_creation_tokens
        float cost_usd
        int duration_seconds
        int api_requests
        varchar cwd
        timestamp session_start
        timestamp session_end
    }

    session_activity {
        serial id PK "NEW"
        varchar session_id FK
        timestamp bucket_start "30-min"
        timestamp bucket_end
        int input_tokens
        int output_tokens
        int api_requests
        int user_prompts
        float cost_usd
        text summary
    }

    session_prompts {
        serial id PK "NEW"
        varchar session_id FK
        timestamp timestamp
        text prompt_text
        boolean has_image
        varchar ticket_keys
    }

    session_responses {
        serial id PK "NEW"
        varchar session_id FK
        int prompt_id FK
        timestamp timestamp
        text response_text
        varchar model
        int input_tokens
        int output_tokens
        text tool_calls "JSON"
    }

    session_tickets {
        serial id PK "NEW"
        varchar session_id FK
        varchar ticket_key "CON-76"
        timestamp first_mentioned_at
    }

    claude_sessions ||--o{ session_activity : "bucketed into"
    claude_sessions ||--o{ session_prompts : "contains"
    claude_sessions ||--o{ session_responses : "contains"
    claude_sessions ||--o{ session_tickets : "mentions"
    session_prompts ||--|| session_responses : "paired with"
    

erDiagram
    time_entries {
        varchar id PK
        varchar clockify_id UK "nullable"
        varchar developer_id FK
        varchar project_id FK
        varchar client_id FK "NEW"
        varchar description
        timestamp start_time
        timestamp end_time
        int duration_seconds
        boolean billable
        date local_date
        varchar source "NEW: clockify | claude_session"
        varchar session_id FK "NEW: nullable"
    }

    commits {
        varchar id PK "CMT-xxxxxxxx"
        varchar sha UK
        varchar repo
        varchar message
        varchar developer_id FK
        varchar github_author_login
        timestamp authored_at
    }

    pull_requests {
        varchar id PK "PR-repo-number"
        int github_id UK
        varchar repo
        varchar title
        varchar state
        varchar developer_id FK
        int additions
        int deletions
        timestamp opened_at
        timestamp merged_at
    }

    claude_sessions ||--o| time_entries : "generates"
    developers ||--o{ time_entries : "logs"
    developers ||--o{ commits : "authors"
    developers ||--o{ pull_requests : "opens"
    

erDiagram
    jira_issues {
        varchar id PK
        varchar key UK "CON-76"
        varchar project_key "CON CC HD"
        varchar summary
        text description
        varchar status
        varchar status_category
        varchar issue_type
        varchar assignee_id FK "NEW"
        float story_points
        varchar parent_key "epic"
    }

    jira_transitions {
        varchar id PK
        varchar issue_id FK
        varchar issue_key
        varchar from_status
        varchar to_status
        varchar author
        timestamp transitioned_at
    }

    jira_issues ||--o{ jira_transitions : "status history"
    developers ||--o{ jira_issues : "assigned to"
    

erDiagram
    transactions {
        varchar id PK
        date date
        varchar description
        float amount
        varchar category
        varchar type "income | expense"
        varchar client_id FK "nullable"
        varchar project_id FK "nullable"
        varchar account_id FK "NEW"
        varchar entity
        varchar scope "NEW: business | personal"
        boolean allocated
    }

    accounts {
        varchar id PK "NEW: CHK-5809"
        varchar plaid_account_id
        varchar label "Business Checking"
        varchar institution "Bank of America"
        varchar scope "business | personal"
        varchar account_type "checking | savings | credit"
    }

    plaid_items {
        varchar id PK
        varchar item_id UK
        varchar access_token
        varchar institution_name
        varchar cursor
        varchar status
    }

    plaid_items ||--o{ transactions : "sources"
    accounts ||--o{ transactions : "categorizes"
    clients ||--o{ transactions : "income from"