Database Schema

The complete database schema for the Hephaestus project:

--- config: layout: elk --- erDiagram %% Generated automatically from PostgreSQL database schema %% using scripts/generate_mermaid_erd.py %% To regenerate: npm run db:erd:generate direction LR BadPracticeDetection { BIGINT id PK TIMESTAMPTZ detection_time OID summary VARCHAR(255) trace_id BIGINT pullrequest_id FK } BadPracticeFeedback { BIGINT id PK OID explanation VARCHAR(255) type BIGINT pull_request_bad_practice_id FK TIMESTAMPTZ creation_time } Issue { VARCHAR(31) issue_type "NOT NULL" BIGINT id PK TIMESTAMPTZ created_at TIMESTAMPTZ updated_at OID body TIMESTAMPTZ closed_at INTEGER comments_count "NOT NULL" VARCHAR(255) html_url BOOLEAN is_locked "NOT NULL" INTEGER number "NOT NULL" VARCHAR(255) state VARCHAR(255) title INTEGER additions INTEGER changed_files INTEGER commits INTEGER deletions BOOLEAN is_draft BOOLEAN is_mergeable BOOLEAN is_merged BOOLEAN maintainer_can_modify VARCHAR(255) merge_commit_sha VARCHAR(255) mergeable_state TIMESTAMPTZ merged_at BIGINT author_id FK BIGINT milestone_id FK BIGINT repository_id FK BIGINT merged_by_id FK BOOLEAN has_pull_request "NOT NULL" TIMESTAMP last_sync_at OID bad_practice_summary TIMESTAMPTZ last_detection_time } IssueAssignee { BIGINT issue_id PK,FK BIGINT user_id PK,FK } IssueComment { BIGINT id PK TIMESTAMPTZ created_at TIMESTAMPTZ updated_at VARCHAR(255) author_association OID body VARCHAR(255) html_url BIGINT author_id FK BIGINT issue_id FK } IssueLabel { BIGINT issue_id PK,FK BIGINT label_id PK,FK } Label { BIGINT id PK VARCHAR(255) color VARCHAR(255) description VARCHAR(255) name BIGINT repository_id FK } Message { BIGINT id PK VARCHAR(32767) content VARCHAR(255) sender TIMESTAMP sent_at BIGINT session_id FK "NOT NULL" INTEGER messages_order } Milestone { BIGINT id PK TIMESTAMPTZ created_at TIMESTAMPTZ updated_at TIMESTAMPTZ closed_at OID description TIMESTAMPTZ due_on VARCHAR(255) html_url INTEGER number "NOT NULL" VARCHAR(255) state VARCHAR(255) title BIGINT creator_id FK BIGINT repository_id FK } PullRequestRequestedReviewer { BIGINT pull_request_id PK,FK BIGINT user_id PK,FK } PullRequestReview { BIGINT id PK OID body VARCHAR(255) commit_id VARCHAR(255) html_url BOOLEAN is_dismissed "NOT NULL" VARCHAR(255) state TIMESTAMPTZ submitted_at BIGINT author_id FK BIGINT pull_request_id FK } PullRequestReviewComment { BIGINT id PK TIMESTAMPTZ created_at TIMESTAMPTZ updated_at VARCHAR(255) author_association OID body VARCHAR(255) commit_id OID diff_hunk VARCHAR(255) html_url INTEGER line "NOT NULL" VARCHAR(255) original_commit_id INTEGER original_line "NOT NULL" INTEGER original_position "NOT NULL" INTEGER original_start_line VARCHAR(255) path INTEGER position "NOT NULL" VARCHAR(255) side INTEGER start_line VARCHAR(255) start_side BIGINT author_id FK BIGINT pull_request_id FK BIGINT review_id FK } PullRequestBadPractice { BIGINT id PK OID description VARCHAR(255) title BIGINT pullrequest_id FK SMALLINT state TIMESTAMPTZ detection_time TIMESTAMPTZ last_update_time SMALLINT user_state SMALLINT detection_pullrequest_lifecycle_state VARCHAR(255) detection_trace_id BIGINT bad_practice_detection_id FK } Repository { BIGINT id PK TIMESTAMPTZ created_at TIMESTAMPTZ updated_at VARCHAR(255) default_branch VARCHAR(255) description BOOLEAN has_issues "NOT NULL" BOOLEAN has_projects "NOT NULL" BOOLEAN has_wiki "NOT NULL" VARCHAR(255) homepage VARCHAR(255) html_url BOOLEAN is_archived "NOT NULL" BOOLEAN is_disabled "NOT NULL" BOOLEAN is_private "NOT NULL" VARCHAR(255) name VARCHAR(255) name_with_owner TIMESTAMPTZ pushed_at INTEGER stargazers_count "NOT NULL" VARCHAR(255) visibility INTEGER watchers_count "NOT NULL" } RepositoryToMonitor { BIGINT id PK TIMESTAMP issues_and_pull_requests_synced_at TIMESTAMP labels_synced_at TIMESTAMP milestones_synced_at VARCHAR(255) name_with_owner TIMESTAMP repository_synced_at BIGINT workspace_id FK } Session { BIGINT id PK TIMESTAMP created_at BIGINT user_id FK BOOLEAN is_closed "NOT NULL" } Team { BIGINT id PK VARCHAR(255) color VARCHAR(255) name BOOLEAN hidden "NOT NULL" } TeamLabel { BIGINT team_id PK,FK BIGINT label_id PK,FK } TeamMember { BIGINT team_id PK,FK BIGINT user_id PK,FK } TeamRepository { BIGINT team_id PK,FK BIGINT repository_id PK,FK } User { BIGINT id PK TIMESTAMPTZ created_at TIMESTAMPTZ updated_at VARCHAR(255) avatar_url VARCHAR(255) blog VARCHAR(255) company VARCHAR(255) description VARCHAR(255) email INTEGER followers "NOT NULL" INTEGER following "NOT NULL" VARCHAR(255) html_url VARCHAR(255) location VARCHAR(255) login VARCHAR(255) name VARCHAR(255) type INTEGER league_points "NOT NULL" BOOLEAN notifications_enabled "NOT NULL" } Workspace { BIGINT id PK TIMESTAMP users_synced_at } %% Relationships %% One-to-Many relationships Issue ||--o{ BadPracticeDetection : references PullRequestBadPractice ||--o{ BadPracticeFeedback : references User ||--o{ Issue : authored_by User ||--o{ Issue : merged_by Milestone ||--o{ Issue : has Repository ||--o{ Issue : has User ||--o{ IssueComment : commented_on Issue ||--o{ IssueComment : commented_on Repository ||--o{ Label : labeled Session ||--o{ Message : has User ||--o{ Milestone : created_by Repository ||--o{ Milestone : has User ||--o{ PullRequestReview : reviewed Issue ||--o{ PullRequestReview : reviewed User ||--o{ PullRequestReviewComment : commented_on Issue ||--o{ PullRequestReviewComment : commented_on PullRequestReview ||--o{ PullRequestReviewComment : commented_on BadPracticeDetection ||--o{ PullRequestBadPractice : has Issue ||--o{ PullRequestBadPractice : references Workspace ||--o{ RepositoryToMonitor : monitors User ||--o{ Session : has %% Many-to-Many relationships Issue }o--o{ IssueAssignee : assigned_to User }o--o{ IssueAssignee : assigned_to Issue }o--o{ IssueLabel : labeled Label }o--o{ IssueLabel : labeled Issue }o--o{ PullRequestRequestedReviewer : reviewed User }o--o{ PullRequestRequestedReviewer : reviewed Label }o--o{ TeamLabel : labeled Team }o--o{ TeamLabel : labeled Team }o--o{ TeamMember : belongs_to User }o--o{ TeamMember : belongs_to Repository }o--o{ TeamRepository : has Team }o--o{ TeamRepository : has %% Styling classDef primaryEntity fill:#e1f5fe,stroke:#01579b,stroke-width:2px classDef associationEntity fill:#f3e5f5,stroke:#4a148c,stroke-width:2px classDef metadataEntity fill:#e8f5e8,stroke:#1b5e20,stroke-width:2px class Issue primaryEntity class IssueAssignee associationEntity class IssueLabel associationEntity class Label primaryEntity class Message metadataEntity class Milestone primaryEntity class PullRequestRequestedReviewer associationEntity class Repository primaryEntity class RepositoryToMonitor metadataEntity class Session metadataEntity class Team primaryEntity class TeamLabel associationEntity class TeamMember associationEntity class TeamRepository associationEntity class User primaryEntity class Workspace primaryEntity