db-migration

name	db-migration
description	Creates a new database migration file. Use this when modifying the data model to add new tables or modify existing ones.

Creating a database migration

Instructions

Create a separate TaskCreate for each applicable step below, then work through them in order, marking each completed before starting the next. Conditional steps (marked "if applicable") should still be created as tasks — mark them completed immediately if they don't apply.

Steps:

1. Determine the next migration number.
2. Create the numbered migration file.
3. Add enum values to R__TypeCodes.sql (if any enum tables).
4. Add schema documentation to R__Comments.sql.
5. Update Config.kt (EnumTable, IdWrapper, and/or Embeddable entries as needed).
6. Add tables to SchemaDocsGenerator.kt.
7. Add enum display names to Enums_en.properties (if any localizable enums).
8. Run yarn translate to generate translations (if Enums_en.properties was changed).
9. Build, format, and test (./gradlew generateJooqClasses, ./gradlew spotlessApply, ./gradlew test).

Step 1: Determine the Next Migration Number

List the highest-numbered .sql file in src/main/resources/db/migration/ to find the current maximum migration number, then add 1.

Migrations are grouped into subdirectories in groups of 50 (e.g., 0400/ contains V400–V449, 0450/ contains V450–V499). Create a new subdirectory when crossing a 50-migration boundary.

Step 2: Create the Numbered Migration File

Create src/main/resources/db/migration/NNNN/VNNN__Description.sql where NNNN is the directory (multiple of 50 rounded down) and NNN is the migration number.

Make sure and add a newline character at the end of the file.

Run ./gradlew generateJooqClasses immediately after creating the migration to catch SQL errors early.

Table type patterns

Regular table with auto-generated primary key:

Table names should be plural for the object they represent.

CREATE TABLE schema_name.table_name
(
  id              BIGINT PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
  -- foreign keys:
  organization_id BIGINT                   NOT NULL REFERENCES organizations ON DELETE CASCADE,
  -- data columns:
  some_column     TEXT                     NOT NULL,
  optional_column TEXT,
  -- audit fields (if applicable):
  created_by      BIGINT                   NOT NULL REFERENCES users,
  created_time    TIMESTAMP WITH TIME ZONE NOT NULL,
  modified_by     BIGINT                   NOT NULL REFERENCES users,
  modified_time   TIMESTAMP WITH TIME ZONE NOT NULL,

  UNIQUE (organization_id, some_column)
);

CREATE INDEX ON schema_name.table_name (organization_id);

Enum/type code table:

CREATE TABLE schema_name.thing_types
(
  id   INTEGER PRIMARY KEY,
  name TEXT NOT NULL
);

For enums with additional properties (e.g., an active flag):

CREATE TABLE schema_name.thing_statuses
(
  id     INTEGER PRIMARY KEY,
  name   TEXT    NOT NULL,
  active BOOLEAN NOT NULL DEFAULT TRUE
);

Junction/reference table with composite primary key:

CREATE TABLE schema_name.thing_other_things
(
  thing_id       BIGINT NOT NULL REFERENCES schema_name.things ON DELETE CASCADE,
  other_thing_id BIGINT NOT NULL REFERENCES schema_name.other_things ON DELETE CASCADE,
  -- optional data columns
  some_value     TEXT,

  PRIMARY KEY (thing_id, other_thing_id)
);

CREATE INDEX ON schema_name.thing_other_things (other_thing_id);

Note: Every table with a composite primary key requires an EmbeddableDefinitionType entry in Config.kt (Step 5). Don't skip this step.

Key conventions

• Always use ON DELETE CASCADE on foreign keys (unless there's a specific reason not to).
• Use BIGINT PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY for IDs on main tables.
• Use INTEGER PRIMARY KEY for enum table IDs.
• Column names for timestamps end in _time; for dates end in _date.
• Foreign key columns are named {referenced_table_singular}_id (or created_by, modified_by for user references in audit fields).
• Always add an index on foreign key columns that aren't part of a primary key or unique constraint, especially for the non-leading column in junction tables.
• Put named constraints (CHECK, UNIQUE) inside the CREATE TABLE body, after all column definitions.
• It's fine to use PostgreSQL-only syntax; the server only runs on PostgreSQL.

Step 3: Add Enum Values to R__TypeCodes.sql (if applicable)

Add INSERT statements to src/main/resources/db/migration/R__TypeCodes.sql. Use ON CONFLICT ... DO UPDATE for idempotency (replayable migrations must be idempotent).

Keep insertions alphabetically sorted by table name. Insert them in the correct order if there are foreign key dependencies between enum tables.

INSERT INTO schema_name.thing_types (id, name)
VALUES (1, 'First Type'),
       (2, 'Second Type'),
       (3, 'Third Type')
ON CONFLICT (id) DO UPDATE SET name = excluded.name;

For enums with additional columns:

INSERT INTO schema_name.thing_statuses (id, name, active)
VALUES (1, 'Active Status', TRUE),
       (2, 'Inactive Status', FALSE)
ON CONFLICT (id) DO UPDATE SET name   = excluded.name,
                               active = excluded.active;

Step 4: Add Schema Documentation to R__Comments.sql

Add COMMENT ON statements to src/main/resources/db/migration/R__Comments.sql. Keep them sorted alphabetically by table/column name within each group. This file uses Markdown syntax.

COMMENT ON TABLE schema_name.table_name IS 'Description of what this table stores.';
COMMENT ON COLUMN schema_name.table_name.some_column IS 'What this column means.';

COMMENT ON TABLE schema_name.thing_types IS '(Enum) Types of things.';

• Prefix enum table comments with (Enum).
• Document any column whose purpose is not immediately obvious from the name.
• Document named constraints if their purpose isn't self-evident (these are rare).

Step 5: Update Config.kt (if applicable)

Edit jooq/src/main/kotlin/com/terraformation/backend/jooq/Config.kt. Keep all entries alphabetically sorted within their respective blocks.

EnumTable (for enum/type code tables)

Add an EnumTable entry to the appropriate schema in ENUM_TABLES.

Default (uses .*\\.thing_type_id as include expression):

EnumTable("thing_types"),

Custom include expression (when the FK column name doesn't follow the default convention):

EnumTable("thing_types", listOf("some_table\\.type_id")),

Multiple include expressions:

EnumTable(
  "thing_statuses",
  listOf(
    "things\\.status_id",
    "thing_summaries\\.status_id",
  ),
),

Non-localizable enum (display names don't need to be translated):

EnumTable("thing_types", isLocalizable = false),

Enum with additional columns beyond id and name:

EnumTable(
  "thing_statuses",
  additionalColumns = listOf(EnumTableColumnInfo("active", "Boolean")),
),

EnumTableColumnInfo parameters:

• columnName: The database column name.
• columnDataType: The Kotlin type (e.g., "Boolean", "String", "String?", "OtherEnumType", "OtherEnumType?").
• isTableEnum (optional, default false): Set to true if columnDataType is itself an enum table type (causes the converter to be applied).

Custom enum class name (when the default derived name is wrong):

EnumTable("thing_statuses", listOf("things\\.status_id"), "ThingStatus"),

IdWrapper (for tables with auto-generated IDs)

Add an IdWrapper entry to the appropriate schema in ID_WRAPPERS if the new table has an id column that should have a type-safe Kotlin wrapper.

IdWrapper("ThingId", listOf("things\\.id", ".*\\.thing_id")),

Add more patterns if the column appears under other names in other tables:

IdWrapper(
  "ThingId",
  listOf(
    "things\\.id",
    ".*\\.thing_id",
    ".*\\.source_thing_id",
  ),
),

Embeddable (for tables with composite primary keys)

Add an EmbeddableDefinitionType to EMBEDDABLES for any new table with a composite primary key.

EmbeddableDefinitionType()
  .withName("thing_other_thing_id")
  .withTables("schema_name.thing_other_things")
  .withColumns("thing_id", "other_thing_id"),

Step 6: Add Tables to SchemaDocsGenerator.kt

Every new table must be listed in tableSlices in src/test/kotlin/com/terraformation/backend/db/SchemaDocsGenerator.kt. The test suite will fail if a table exists in the schema but isn't listed there.

Add the table to the appropriate schema block in tableSlices. Every table should appear in ALL plus the schema slice(s) that are relevant:

"thing_types" to setOf(ALL, ACCELERATOR),
"things" to setOf(ALL, ACCELERATOR),

Use whichever slices make sense for the data; a table can appear in multiple slices if it's relevant to multiple schemas.

Entries within each schema block are sorted alphabetically by table name.

Step 7: Add Enum Display Names to Enums_en.properties (if applicable)

For each localizable enum (isLocalizable = true, which is the default), add an entry to src/main/resources/i18n/Enums_en.properties.

Format: {schema}.{EnumClassName}.{KotlinEnumValue}={Display Name}

Keep entries sorted alphabetically by key. Use # comment lines for translation hints if needed.

# "public" schema enums use the schema prefix literal "public"
public.Role.Admin=Admin
public.Role.Contributor=Contributor
public.Role.Manager=Manager
public.Role.Owner=Owner

The enum class name comes from EnumTable.enumName, which is derived from the table name by:

1. Stripping the trailing s
2. Converting to PascalCase
3. Applying suffix corrections (statuse → status, categorie → category, etc.)

The Kotlin enum value name is the PascalCase form of the name column value (spaces removed, each word capitalized).

For display names that could be ambiguous, add a comment above:

# As in compliance with legal requirements
accelerator.InternalInterest.Compliance=Compliance

Enums with isLocalizable = false do NOT get entries in Enums_en.properties.

Step 8: Run yarn translate (if Enums_en.properties was changed)

yarn translate

This updates the strings files for other languages. Only modify Enums_en.properties directly; let the translation tool update the other language files.

Step 9: Build, Format, and Test

./gradlew generateJooqClasses # Regenerates jOOQ code from schema; catches migration errors
./gradlew spotlessApply       # Formats all code
./gradlew test                # Runs the full test suite