Skip to content

Latest commit

 

History

History

db

Folders and files

NameName
Last commit message
Last commit date

parent directory

..
migration
migration
 
 
repeated
repeated
 
 
README.md
README.md
 
 

README.md

db/

erDiagram
    Achievement {
        timestamp_with_time_zone createdAt "{NOT_NULL}"
        timestamp_with_time_zone deletedAt
        text description
        uuid id PK "{NOT_NULL}"
        boolean isActive
        Tag leaderboard "<Patina,Hunter,Nyu,Baruch,Rpi,Gwc,Sbu,Ccny,Columbia,Cornell,Bmcc,MHCPlusPlus>"
        AchievementPlaceEnum place "<ONE,TWO,THREE>  {NOT_NULL}"
        character_varying title "{NOT_NULL}"
        uuid userId FK "{NOT_NULL}"
    }

    Announcement {
        timestamp_with_time_zone createdAt "{NOT_NULL}"
        timestamp_with_time_zone expiresAt "{NOT_NULL}"
        uuid id PK "{NOT_NULL}"
        text message "{NOT_NULL}"
        boolean showTimer
    }

    ApiKey {
        text apiKeyHash UK "{NOT_NULL}"
        timestamp_with_time_zone createdAt "{NOT_NULL}"
        timestamp_with_time_zone expiresAt "{NOT_NULL}"
        uuid id PK "{NOT_NULL}"
        timestamp_with_time_zone updatedAt "{NOT_NULL}"
        uuid updatedBy FK "{NOT_NULL}"
    }

    ApiKeyAccess {
        ApiKeyAccessEnum access UK "<GWC_READ_BY_USER,TEST_VALUE>  {NOT_NULL}"
        uuid apiKeyId FK,UK "{NOT_NULL}"
        uuid id PK "{NOT_NULL}"
    }

    Auth {
        timestamp_with_time_zone createdAt "{NOT_NULL}"
        text csrf
        uuid id PK "{NOT_NULL}"
        text token "{NOT_NULL}"
    }

    BackgroundTask {
        timestamp_with_time_zone completedAt "{NOT_NULL}"
        uuid id PK "{NOT_NULL}"
        BackgroundTaskEnum task "<LEETCODE_QUESTION_BANK>  {NOT_NULL}"
    }

    Club {
        text description
        uuid id PK "{NOT_NULL}"
        character_varying name "{NOT_NULL}"
        character_varying password "{NOT_NULL}"
        character_varying slug UK "{NOT_NULL}"
        character_varying splashIconUrl
        Tag tag "<Patina,Hunter,Nyu,Baruch,Rpi,Gwc,Sbu,Ccny,Columbia,Cornell,Bmcc,MHCPlusPlus>  {NOT_NULL}"
    }

    DiscordClub {
        timestamp_with_time_zone createdAt "{NOT_NULL}"
        timestamp_with_time_zone deletedAt
        text description
        uuid id PK "{NOT_NULL}"
        text name "{NOT_NULL}"
        Tag tag "<Patina,Hunter,Nyu,Baruch,Rpi,Gwc,Sbu,Ccny,Columbia,Cornell,Bmcc,MHCPlusPlus>  {NOT_NULL}"
    }

    DiscordClubMetadata {
        uuid discordClubId FK "{NOT_NULL}"
        text guildId
        uuid id PK "{NOT_NULL}"
        text leaderboardChannelId
    }

    Job {
        integer attempts "{NOT_NULL}"
        timestamp_with_time_zone completedAt
        timestamp_with_time_zone createdAt "{NOT_NULL}"
        uuid id PK "{NOT_NULL}"
        timestamp_with_time_zone nextAttemptAt "{NOT_NULL}"
        timestamp_with_time_zone processedAt
        character_varying questionId "{NOT_NULL}"
        JobStatus status "<COMPLETE,PROCESSING,INCOMPLETE>  {NOT_NULL}"
    }

    Leaderboard {
        timestamp_with_time_zone createdAt "{NOT_NULL}"
        timestamp_with_time_zone deletedAt
        uuid id PK "{NOT_NULL}"
        character_varying name "{NOT_NULL}"
        timestamp_with_time_zone shouldExpireBy
        text syntaxHighlightingLanguage
    }

    Lobby {
        timestamp_with_time_zone createdAt "{NOT_NULL}"
        timestamp_with_time_zone expiresAt
        uuid id PK "{NOT_NULL}"
        text joinCode "{NOT_NULL}"
        integer playerCount "{NOT_NULL}"
        LobbyStatusEnum status "<CLOSED,AVAILABLE,ACTIVE,COMPLETED>  {NOT_NULL}"
        boolean tie "{NOT_NULL}"
        uuid winnerId FK
    }

    LobbyPlayer {
        uuid id PK "{NOT_NULL}"
        uuid lobbyId FK "{NOT_NULL}"
        uuid playerId FK "{NOT_NULL}"
        integer points "{NOT_NULL}"
    }

    LobbyPlayerQuestion {
        uuid id PK "{NOT_NULL}"
        uuid lobbyPlayerId FK,UK "{NOT_NULL}"
        integer points
        uuid questionId FK,UK
    }

    LobbyQuestion {
        timestamp_with_time_zone createdAt "{NOT_NULL}"
        uuid id PK "{NOT_NULL}"
        uuid lobbyId FK "{NOT_NULL}"
        uuid questionBankId FK "{NOT_NULL}"
        integer userSolvedCount "{NOT_NULL}"
    }

    Metadata {
        timestamp_with_time_zone createdAt "{NOT_NULL}"
        uuid id PK "{NOT_NULL}"
        uuid leaderboardId FK "{NOT_NULL}"
        integer totalScore "{NOT_NULL}"
        uuid userId FK "{NOT_NULL}"
    }

    POTD {
        timestamp_without_time_zone createdAt "{NOT_NULL}"
        uuid id PK "{NOT_NULL}"
        double_precision multiplier "{NOT_NULL}"
        character_varying slug "{NOT_NULL}"
        character_varying title "{NOT_NULL}"
    }

    Question {
        double_precision acceptanceRate "{NOT_NULL}"
        text code
        timestamp_with_time_zone createdAt "{NOT_NULL}"
        text description
        uuid id PK "{NOT_NULL}"
        text language
        character_varying memory
        integer pointsAwarded
        question_difficulty questionDifficulty "<Easy,Medium,Hard>  {NOT_NULL}"
        text questionLink "{NOT_NULL}"
        smallint questionNumber "{NOT_NULL}"
        character_varying questionSlug "{NOT_NULL}"
        text questionTitle "{NOT_NULL}"
        character_varying runtime
        text submissionId UK
        timestamp_with_time_zone submittedAt "{NOT_NULL}"
        uuid userId FK "{NOT_NULL}"
    }

    QuestionBank {
        real acceptanceRate "{NOT_NULL}"
        timestamp_with_time_zone createdAt "{NOT_NULL}"
        text description
        uuid id PK "{NOT_NULL}"
        question_difficulty questionDifficulty "<Easy,Medium,Hard>  {NOT_NULL}"
        text questionLink "{NOT_NULL}"
        smallint questionNumber "{NOT_NULL}"
        character_varying questionSlug "{NOT_NULL}"
        text questionTitle "{NOT_NULL}"
    }

    QuestionTopic {
        timestamp_with_time_zone createdAt "{NOT_NULL}"
        uuid id PK "{NOT_NULL}"
        uuid questionBankId FK,UK "Exactly one of questionBankId or questionId must be non-null. If questionBankId is non-null, this topic refers to a question bank question."
        uuid questionId FK,UK "Exactly one of questionId or questionBankId must be non-null. If questionId is non-null, this topic refers to a question submission."
        LeetcodeTopicEnum topic UK "<stack,data-stream,rejection-sampling,geometry,counting,design,probability-and-statistics,minimum-spanning-tree,line-sweep,number-theory,rolling-hash,segment-tree,biconnected-component,monotonic-stack,iterator,queue,radix-sort,bucket-sort,shell,memoization,string,prefix-sum,concurrency,database,shortest-path,sorting,linked-list,sliding-window,suffix-array,doubly-linked-list,simulation,ordered-set,graph,math,ordered-map,game-theory,dynamic-programming,recursion,monotonic-queue,matrix,reservoir-sampling,merge-sort,combinatorics,interactive,binary-tree,randomized,bitmask,breadth-first-search,string-matching,greedy,brainteaser,backtracking,bit-manipulation,union-find,binary-search-tree,two-pointers,array,depth-first-search,eulerian-circuit,tree,binary-search,strongly-connected-component,enumeration,heap-priority-queue,divide-and-conquer,hash-function,hash-table,trie,topological-sort,quickselect,binary-indexed-tree,counting-sort,unknown>  {NOT_NULL}"
        text topicSlug "{NOT_NULL}"
    }

    Report {
        timestamp_with_time_zone createdAt "{NOT_NULL}"
        text description "{NOT_NULL}"
        character_varying email
        uuid id PK "{NOT_NULL}"
        character_varying title "{NOT_NULL}"
    }

    Session {
        timestamp_with_time_zone expiresAt "{NOT_NULL}"
        character_varying id PK "{NOT_NULL}"
        uuid userId FK "{NOT_NULL}"
    }

    User {
        boolean admin "{NOT_NULL}"
        character_varying discordId UK "{NOT_NULL}"
        character_varying discordName "{NOT_NULL}"
        uuid id PK "{NOT_NULL}"
        character_varying leetcodeUsername UK
        character_varying nickname
        character_varying profileUrl
        character_varying schoolEmail
        text verifyKey
    }

    UserTag {
        timestamp_with_time_zone createdAt "{NOT_NULL}"
        uuid id PK "{NOT_NULL}"
        Tag tag UK "<Patina,Hunter,Nyu,Baruch,Rpi,Gwc,Sbu,Ccny,Columbia,Cornell,Bmcc,MHCPlusPlus>  {NOT_NULL}"
        uuid userId FK,UK "{NOT_NULL}"
    }

    WeeklyMessage {
        timestamp_with_time_zone createdAt "{NOT_NULL}"
        uuid id PK "{NOT_NULL}"
    }

    flyway_schema_history {
        integer checksum
        character_varying description "{NOT_NULL}"
        integer execution_time "{NOT_NULL}"
        character_varying installed_by "{NOT_NULL}"
        timestamp_without_time_zone installed_on "{NOT_NULL}"
        integer installed_rank PK "{NOT_NULL}"
        character_varying script "{NOT_NULL}"
        boolean success "{NOT_NULL}"
        character_varying type "{NOT_NULL}"
        character_varying version
    }

    Achievement }o--|| User : "userId"
    ApiKey }o--|| User : "updatedBy"
    ApiKeyAccess }o--|| ApiKey : "apiKeyId"
    DiscordClubMetadata }o--|| DiscordClub : "discordClubId"
    Metadata }o--|| Leaderboard : "leaderboardId"
    Lobby }o--|| User : "winnerId"
    LobbyPlayer }o--|| Lobby : "lobbyId"
    LobbyQuestion }o--|| Lobby : "lobbyId"
    LobbyPlayer }o--|| User : "playerId"
    LobbyPlayerQuestion }o--|| LobbyPlayer : "lobbyPlayerId"
    LobbyPlayerQuestion }o--|| Question : "questionId"
    LobbyQuestion }o--|| QuestionBank : "questionBankId"
    Metadata }o--|| User : "userId"
    Question }o--|| User : "userId"
    QuestionTopic }o--|| Question : "questionId"
    QuestionTopic }o--|| QuestionBank : "questionBankId"
    Session }o--|| User : "userId"
    UserTag }o--|| User : "userId"
Loading

Generated with mermerd -c=postgresql://postgres:passwordnomas@localhost:5432/codebloom-dev-2 -s=public --outputMode=stdout --useAllTables --showDescriptions enumValues,columnComments,notNull

Also view in mermaid.live

Last updated: 02/15/2026

This directory contains the migrations that are applied to our PostgreSQL databases.

Commands

To migrate your local database using your root .env, you can simply run:

just migrate

If you need to drop it quickly, you can simply run:

just drop

Versioned Migrations

Versioned migrations are under db/migration/

Note

Versioned migrations are applied to all databases

Explanation

  • Versioned migrations will only run once.
  • You can use these migrations to define tables, schemas, columns (otherwise known as DDL) OR define insertions/updates/deletes of certain columns (otherwise known as DML).

    If you need to generate mock data that does not need to be in production, please look at the docs on repeatable migrations.

Naming Scheme

Requirements

V00{number}__{description}.SQL

  1. Name must be prefixed with a V.
  2. Version numbers must be sequential and unique.
    • Version numbers must be 4 digits wide. You may pad the left-side with 0s until you reach that goal.
  3. Double underscores (__) separate the version from the description
  4. Use underscores (_) instead of spaces in descriptions
  5. Files must have .sql (or .SQL) extension

Examples

V0005__Add_user_table.SQL
V0642__Insert_new_tag_enums.SQL
V9999__Delete_user_table.SQL

Repeatable Migrations

Versioned migrations are under db/repeated/

Note

Repeatable migrations are only applied to local & CI databases.
Repeatable migrations are NOT applied to the production & staging database.

Explanation

  1. Instead of being run just once, repeatable migrations are (re-)applied to a database on migrate every time their checksum changes.
  2. Our main use for repeatable migrations are to generate mock data to use locally and in our CI database, but is not needed for our production or staging database.

Requirements

  1. Name must be prefixed with an R__Mock
  2. Version numbers must be sequential and unique
  3. Use underscores (_) instead of spaces in descriptions
  4. Files must have .sql (or .SQL) extension

Examples

R__Mock_V0005_Insert_mock_users.SQL
R__Mock_V0011_Add_old_leaderboards.SQL
R__Mock_V9999_Delete_old_mock_users_and_insert_new_users.SQL