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