Intermediate

// File: src/main/java/db/migration/V2__SeedAdminUser.java
// => Package must be db.migration (matches Flyway default classpath scan location)
// => Class name V2__SeedAdminUser follows exact same naming rules as SQL files
 
import org.flywaydb.core.api.migration.BaseJavaMigration;
// => Extend BaseJavaMigration to get default checksum and versioning behavior
import org.flywaydb.core.api.migration.Context;
// => Context provides the active JDBC Connection for this migration's transaction
 
import java.sql.PreparedStatement;  // => Use PreparedStatement for parameterized SQL
 
public class V2__SeedAdminUser extends BaseJavaMigration {
    // => Class must be public for Flyway's class scanner to instantiate it
    // => BaseJavaMigration provides getVersion(), getDescription(), getChecksum()
 
    @Override
    public void migrate(Context context) throws Exception {
        // => Flyway calls migrate() inside the migration transaction
        // => context.getConnection() returns the active Connection for this migration
 
        try (PreparedStatement stmt = context.getConnection().prepareStatement(
            "INSERT INTO users (username, email, display_name, password_hash, role) " +
            "VALUES (?, ?, ?, ?, ?)"
        )) {
            // => PreparedStatement prevents SQL injection in programmatic migrations
            stmt.setString(1, "admin");              // => Position 1: username
            stmt.setString(2, "admin@example.com");  // => Position 2: email
            stmt.setString(3, "Administrator");      // => Position 3: display_name
            stmt.setString(4, "$2b$12$hashed_value");// => Position 4: bcrypt hash placeholder
            stmt.setString(5, "ADMIN");              // => Position 5: role (use enum value)
            stmt.executeUpdate();                    // => Executes INSERT; returns rows affected
            // => If executeUpdate() throws, Flyway rolls back the entire migration
        }
    }
}

// File: src/main/kotlin/db/migration/V3__SetDefaultCurrency.kt
// => Package db.migration is Flyway's default classpath scan location
// => Kotlin class V3__SetDefaultCurrency extends BaseJavaMigration
 
import org.flywaydb.core.api.migration.BaseJavaMigration
// => Flyway base class; provides version, description, checksum from class name
import org.flywaydb.core.api.migration.Context
// => Provides the active JDBC Connection inside the migration transaction
 
class V3__SetDefaultCurrency : BaseJavaMigration() {
    // => Kotlin class inherits from BaseJavaMigration()
    // => No "public" modifier needed in Kotlin; classes are public by default
 
    override fun migrate(context: Context) {
        // => Override migrate to supply the migration SQL
        // => Called by Flyway inside an open transaction
 
        context.connection.prepareStatement(
            "UPDATE expenses SET currency = 'IDR' WHERE currency IS NULL"
            // => Backfill: set a default currency on rows with NULL currency
        ).use { stmt ->
            // => Kotlin .use {} extension: calls stmt.close() automatically
            val rowsUpdated = stmt.executeUpdate()
            // => executeUpdate() returns Int (rows affected by UPDATE)
            // => rowsUpdated might be 0 if all existing rows already have currency set
            println("Updated $rowsUpdated expense rows with default currency IDR")
            // => Output: "Updated 42 expense rows with default currency IDR"
        }
        // => stmt.close() called here automatically by .use {}
    }
}

-- File: src/main/resources/db/migration/beforeMigrate.sql
-- => Flyway discovers callback SQL files by their exact name (no version prefix)
-- => beforeMigrate.sql runs once before ANY migration in this flyway.migrate() call
-- => Runs inside its own transaction (separate from each migration's transaction)
 
-- Log migration start to an audit table
INSERT INTO migration_log (event, occurred_at)
  VALUES ('MIGRATION_STARTED', NOW());
-- => Records timestamp when this deployment's migration batch began
-- => migration_log must already exist before this callback can insert into it

// File: src/main/java/db/callbacks/AfterMigrateCallback.java
// => Callback classes must implement Callback interface (not BaseJavaMigration)
// => Register with .callbacks(AfterMigrateCallback()) in FlywayConfiguration
 
import org.flywaydb.core.api.callback.Callback;
// => Flyway Callback interface: handle(Event, Context)
import org.flywaydb.core.api.callback.Context;
// => Context: access Event, Connection, MigrationInfo
import org.flywaydb.core.api.callback.Event;
// => Event enum: BEFORE_MIGRATE, AFTER_MIGRATE, BEFORE_EACH_MIGRATE, etc.
 
public class AfterMigrateCallback implements Callback {
 
    @Override
    public boolean supports(Event event, Context context) {
        return event == Event.AFTER_MIGRATE;
        // => Return true only for AFTER_MIGRATE: Flyway calls handle() only for supported events
    }
 
    @Override
    public boolean canHandleInTransaction(Event event, Context context) {
        return true;
        // => true = Flyway wraps handle() in the migration transaction
        // => false = Flyway calls handle() outside any transaction (for DDL-heavy operations)
    }
 
    @Override
    public void handle(Event event, Context context) {
        System.out.println("All migrations applied successfully.");
        // => Output: "All migrations applied successfully."
        // => In production: send Slack alert, increment Prometheus counter, etc.
    }
 
    @Override
    public String getCallbackName() {
        return "AfterMigrateCallback";
        // => Used in Flyway logs to identify this callback
    }
}

// Flyway configuration with custom placeholders
import org.flywaydb.core.Flyway  // => Flyway core API
 
val flyway = Flyway.configure()  // => Start fluent builder
    .dataSource(
        "jdbc:postgresql://localhost:5432/mydb",
        // => JDBC URL for PostgreSQL
        "myuser",                 // => Database username
        "mypassword"              // => Database password
    )
    .placeholders(mapOf(
        // => Map of placeholder name -> replacement value
        "defaultRole"   to "USER",
        // => ${defaultRole} in SQL will be replaced with "USER"
        "adminEmail"    to "admin@example.com",
        // => ${adminEmail} in SQL will be replaced with "admin@example.com"
        "schemaVersion" to "2"
        // => ${schemaVersion} in SQL will be replaced with "2"
    ))
    .load()                       // => Build Flyway instance with placeholders configured
 
flyway.migrate()                  // => Execute migrations; all ${...} tokens replaced before SQL execution

-- File: src/main/resources/db/migration/V4__seed_roles.sql
-- => ${defaultRole} and ${adminEmail} are replaced by Flyway before execution
-- => Useful for environment-specific values (dev vs prod default data)
 
INSERT INTO roles (name, is_default)
  VALUES ('${defaultRole}', true);
-- => After substitution: VALUES ('USER', true)
-- => Allows the same migration file to work across environments with different defaults
 
INSERT INTO users (username, email, display_name, password_hash, role)
  VALUES ('admin', '${adminEmail}', 'Administrator', 'placeholder_hash', 'ADMIN');
-- => After substitution: VALUES ('admin', 'admin@example.com', ...)
-- => Different admin email per environment without separate migration files

import org.flywaydb.core.Flyway  // => Flyway core API
 
val flyway = Flyway.configure()  // => Fluent builder
    .dataSource(
        "jdbc:postgresql://localhost:5432/mydb",
        "myuser",
        "mypassword"
    )
    .schemas("app", "audit", "reporting")
    // => schemas() sets the list of schemas Flyway manages
    // => "app" is the default schema (first listed): flyway_schema_history is created here
    // => Flyway creates "audit" and "reporting" schemas if they do not exist
    // => search_path is set to these schemas for all migrations
    .load()                       // => Build Flyway instance
 
flyway.migrate()                  // => Apply pending migrations in version order
// => Migrations can CREATE TABLE in any of the listed schemas
// => Example: CREATE TABLE audit.events (...) or CREATE TABLE reporting.summaries (...)

-- File: src/main/resources/db/migration/V5__create_audit_events.sql
-- => Create a table in the "audit" schema (separate from the "app" schema)
-- => Schema prefix "audit." makes the target explicit regardless of search_path
 
CREATE TABLE audit.events (                  -- => Table in "audit" schema
  id          UUID         NOT NULL DEFAULT gen_random_uuid(),
  -- => UUID primary key; auto-generated
  table_name  VARCHAR(100) NOT NULL,         -- => Which table was modified
  operation   VARCHAR(10)  NOT NULL,         -- => INSERT, UPDATE, or DELETE
  row_id      UUID         NOT NULL,         -- => Primary key of the affected row
  changed_at  TIMESTAMPTZ  NOT NULL DEFAULT NOW(),
  -- => Timestamp with timezone for the modification
  CONSTRAINT pk_audit_events PRIMARY KEY (id)
  -- => Named primary key for future reference
);

import org.flywaydb.core.Flyway  // => Flyway core API
 
val flyway = Flyway.configure()  // => Fluent builder
    .dataSource(
        "jdbc:postgresql://localhost:5432/legacy_db",
        // => Existing database with tables but no flyway_schema_history
        "myuser",
        "mypassword"
    )
    .baselineVersion("1")
    // => Set the baseline version to "1"
    // => flyway.baseline() will stamp version "1" as already applied
    .baselineDescription("Initial schema before Flyway adoption")
    // => Human-readable description stored in flyway_schema_history
    .baselineOnMigrate(true)
    // => Automatically baseline on migrate() if flyway_schema_history is missing
    // => Equivalent to calling flyway.baseline() manually before flyway.migrate()
    .load()                       // => Build Flyway instance
 
flyway.migrate()
// => First call: creates flyway_schema_history, inserts baseline row for version "1"
// => Then applies V2__add_audit_columns.sql, V3__create_tokens.sql, etc.
// => V1__initial_schema.sql is permanently skipped (already baselined)

import org.flywaydb.core.Flyway  // => Flyway core API
 
val flyway = Flyway.configure()  // => Fluent builder
    .dataSource(
        "jdbc:postgresql://localhost:5432/mydb",
        "myuser",
        "mypassword"
    )
    .target("3")
    // => Migrate only up to and including version "3"
    // => V4, V5, V6 exist but will NOT be applied in this call
    // => Useful for testing: "does the application work with only V1-V3?"
    .load()                       // => Build Flyway instance
 
val result = flyway.migrate()     // => Apply V1, V2, V3 only (V4+ skipped)
println(result.migrationsExecuted)
// => Output: 3 (assuming V1-V3 were all pending)
 
// Check what remains pending after targeted migrate
val flyway2 = Flyway.configure()  // => New builder without target restriction
    .dataSource(
        "jdbc:postgresql://localhost:5432/mydb",
        "myuser",
        "mypassword"
    )
    .load()
val info = flyway2.info()         // => Returns MigrationInfoService
info.pending().forEach { m ->
    println("Pending: ${m.version} - ${m.description}")
    // => Output: "Pending: 4 - add audit columns"
    // => Output: "Pending: 5 - standardize schema"
}

import org.flywaydb.core.Flyway  // => Flyway core API
 
// Scenario: V1, V2, V4 are already applied. Developer A created V3 on a long-running branch.
// Without outOfOrder: flyway.migrate() would FAIL with "detected resolved migration not applied to DB"
// With outOfOrder: flyway.migrate() runs V3 even though V4 is already applied
 
val flyway = Flyway.configure()  // => Fluent builder
    .dataSource(
        "jdbc:postgresql://localhost:5432/mydb",
        "myuser",
        "mypassword"
    )
    .outOfOrder(true)
    // => Allow migrations with lower versions than the current highest applied version
    // => V3 can run even if V4 is already in flyway_schema_history
    // => Flyway marks V3 in flyway_schema_history with out_of_order=true
    .load()                       // => Build Flyway instance
 
val result = flyway.migrate()     // => Applies V3 (out-of-order)
println(result.migrationsExecuted)
// => Output: 1 (V3 was the only pending migration)
 
val info = flyway.info()
info.applied().forEach { m ->
    println("${m.version}: outOfOrder=${m.isOutOfOrder}")
    // => "1: outOfOrder=false"
    // => "2: outOfOrder=false"
    // => "3: outOfOrder=true"   -- the late migration is flagged
    // => "4: outOfOrder=false"
}

import org.flywaydb.core.Flyway  // => Flyway core API
 
val flyway = Flyway.configure()  // => Fluent builder
    .dataSource(
        "jdbc:postgresql://localhost:5432/mydb",
        "myuser",
        "mypassword"
    )
    .group(true)
    // => Run all pending migrations inside a SINGLE wrapping transaction
    // => Default (false): each migration has its own transaction
    // => group(true): V2, V3, V4 all succeed or all roll back together
    // => WARNING: DDL statements (CREATE TABLE, ALTER TABLE) in PostgreSQL commit immediately
    // =>   and cannot be rolled back even inside a group transaction
    // => Safe for DML-only migration batches (INSERT, UPDATE, DELETE)
    .mixed(true)
    // => mixed(true): allow mixing DML and DDL in the same migration
    // => Required when using group(true) with SQL that contains both DDL and DML
    .load()                       // => Build Flyway instance
 
val result = flyway.migrate()     // => Apply all pending migrations in one transaction
println(result.success)
// => Output: true (all migrations succeeded)
// => If any single migration fails: entire group rolls back (DML only)
// => flyway_schema_history shows no new entries on rollback

// File: src/main/java/db/migration/V6__AddConcurrentIndex.java
// => Implements JavaMigration directly (not BaseJavaMigration) to override canExecuteInTransaction()
 
import org.flywaydb.core.api.migration.BaseJavaMigration;
// => BaseJavaMigration.canExecuteInTransaction() returns true by default
import org.flywaydb.core.api.migration.Context;
// => Context: provides JDBC Connection
 
import java.sql.Statement;  // => Statement for non-parameterized DDL
 
public class V6__AddConcurrentIndex extends BaseJavaMigration {
    // => Inherits BaseJavaMigration but overrides canExecuteInTransaction
 
    @Override
    public boolean canExecuteInTransaction() {
        return false;
        // => Return false: Flyway does NOT wrap this migration in a transaction
        // => Required for CREATE INDEX CONCURRENTLY (PostgreSQL blocks concurrent index builds inside transactions)
        // => Required for VACUUM, CLUSTER, and some ALTER TYPE operations
        // => WARNING: no transaction = no automatic rollback on failure; migration must be idempotent
    }
 
    @Override
    public void migrate(Context context) throws Exception {
        try (Statement stmt = context.getConnection().createStatement()) {
            stmt.execute(
                "CREATE INDEX CONCURRENTLY IF NOT EXISTS " +
                "idx_expenses_user_id ON expenses (user_id)"
            );
            // => CREATE INDEX CONCURRENTLY builds index without locking the table for writes
            // => Takes longer than non-concurrent index builds but safe for production tables
            // => IF NOT EXISTS: idempotent — safe to rerun if migration partially executed before
        }
    }
}

import org.flywaydb.core.Flyway                          // => Flyway core API
import org.flywaydb.core.api.exception.FlywayException  // => Base exception for all Flyway errors
 
val flyway = Flyway.configure()  // => Fluent builder
    .dataSource(
        "jdbc:postgresql://localhost:5432/mydb",
        "myuser",
        "mypassword"
    )
    .load()                       // => Build Flyway instance
 
fun runMigrationsWithRecovery(flyway: Flyway) {
    // => Helper function: attempts migration, repairs on failure, retries
    try {
        val result = flyway.migrate()            // => Attempt to apply pending migrations
        println("Success: ${result.migrationsExecuted} migrations applied")
        // => Output: "Success: 3 migrations applied"
    } catch (e: FlywayException) {
        // => FlywayException covers: SQL errors, checksum mismatches, out-of-order violations
        println("Migration failed: ${e.message}")
        // => Output: "Migration failed: Migration V5 failed! Changes successfully rolled back."
 
        // Check if the failure was recorded (non-transactional migrations leave a failed entry)
        val failedMigrations = flyway.info().failed()
        // => info().failed(): returns array of MigrationInfo with state = FAILED
 
        if (failedMigrations.isNotEmpty()) {
            println("Repairing flyway_schema_history...")
            flyway.repair()
            // => repair() removes failed entries from flyway_schema_history
            // => repair() also recalculates checksums for migrations whose files changed
            // => After repair(), flyway.migrate() can retry from the failed version
            println("Repair complete. Fix the migration SQL, then call migrate() again.")
        }
    }
}

import org.flywaydb.core.Flyway  // => Flyway core API
 
val flyway = Flyway.configure()  // => Fluent builder
    .dataSource(
        "jdbc:postgresql://localhost:5432/mydb",
        "myuser",
        "mypassword"
    )
    .load()                       // => Build Flyway instance
 
// Community edition: use info() to preview what would run without executing
fun previewPendingMigrations(flyway: Flyway) {
    val infoService = flyway.info()  // => Returns MigrationInfoService; no SQL executed
    val pending = infoService.pending()
    // => pending(): array of MigrationInfo for migrations not yet in flyway_schema_history
 
    println("Pending migrations preview:")
    pending.forEach { migration ->
        println("  Version: ${migration.version}")
        // => Output: "  Version: 4"
        println("  Description: ${migration.description}")
        // => Output: "  Description: add audit columns"
        println("  Script: ${migration.script}")
        // => Output: "  Script: V4__add_audit_columns.sql"
        println("  Type: ${migration.type}")
        // => Output: "  Type: SQL" or "  Type: JDBC" (for Java/Kotlin migrations)
        println("---")
    }
    println("Total pending: ${pending.size}")
    // => Output: "Total pending: 2"
}
 
previewPendingMigrations(flyway)
// => Shows all pending migrations without applying any
// => Use this in deployment pipelines to validate what will run before approving

-- File: src/main/resources/db/migration/V7__migrate_legacy_categories.sql
-- => Data migration: copy rows from old "product_categories" to new "categories" table
-- => INSERT...SELECT is atomic: either all rows migrate or none do (within the transaction)
 
-- Assume new "categories" table already exists (created in V6)
INSERT INTO categories (id, name, slug, created_at)
-- => Target table columns listed explicitly to avoid position-dependent errors
SELECT
    gen_random_uuid(),            -- => Generate a new UUID for each migrated row
    pc.name,                      -- => Copy name column directly
    LOWER(REPLACE(pc.name, ' ', '-')),
    -- => Derive slug from name: lowercase, spaces replaced with hyphens
    -- => Example: "Sports Equipment" -> "sports-equipment"
    pc.created_at                 -- => Preserve original creation timestamp
FROM product_categories pc        -- => Source table (legacy schema)
WHERE pc.active = true;           -- => Only migrate active categories; skip archived ones
-- => SQL: INSERT INTO categories SELECT ... FROM product_categories WHERE active = true
-- => If product_categories has 500 rows, this creates 500 rows in categories in one statement
 
-- Verify migration (optional, informational)
-- SELECT COUNT(*) FROM categories;
-- => Should equal the count of active rows in product_categories

-- File: src/main/resources/db/migration/R__seed_expense_categories.sql
-- => "R__" prefix = repeatable migration (no version number)
-- => Flyway recalculates checksum on every migrate() call
-- => Re-executes this file whenever its content changes
-- => Always runs AFTER all versioned (V__) migrations complete
 
-- Use INSERT ... ON CONFLICT (upsert) for idempotent seed data
-- => Idempotency is critical: repeatable migrations may run many times
INSERT INTO expense_categories (code, label, is_active)
VALUES
    ('FOOD',       'Food & Dining',    true),
    -- => Category code=FOOD; label visible in UI; active=true
    ('TRANSPORT',  'Transportation',   true),
    -- => Category code=TRANSPORT
    ('HEALTH',     'Healthcare',       true),
    -- => Category code=HEALTH
    ('EDUCATION',  'Education',        true),
    -- => Category code=EDUCATION
    ('UTILITIES',  'Utilities',        true),
    -- => Category code=UTILITIES
    ('OTHER',      'Other',            true)
    -- => Catch-all category for uncategorized expenses
ON CONFLICT (code)
-- => ON CONFLICT: if a row with this code already exists...
DO UPDATE SET
    label     = EXCLUDED.label,
    -- => EXCLUDED refers to the row that would have been inserted
    -- => Update label if it changed in the migration file
    is_active = EXCLUDED.is_active;
    -- => Update is_active status to match the migration file's current value
-- => Full upsert: safe to run on empty table (INSERT) or populated table (UPDATE)

-- File: src/main/resources/db/migration/V8__create_expense_attachments.sql
-- => Creates attachments table with a foreign key that cascades updates and deletes
 
CREATE TABLE expense_attachments (
  id          UUID          NOT NULL DEFAULT gen_random_uuid(),
  -- => UUID primary key for the attachment itself
  expense_id  UUID          NOT NULL,
  -- => Foreign key referencing the expenses table
  filename    VARCHAR(255)  NOT NULL,
  -- => Original filename as uploaded
  storage_key VARCHAR(500)  NOT NULL,
  -- => Object storage key (e.g., S3 path); longer than filename
  mime_type   VARCHAR(100)  NOT NULL,
  -- => MIME type: "image/jpeg", "application/pdf", etc.
  file_size   BIGINT        NOT NULL,
  -- => File size in bytes; BIGINT handles files up to ~9 exabytes
  created_at  TIMESTAMPTZ   NOT NULL DEFAULT NOW(),
  -- => Upload timestamp with timezone
  CONSTRAINT pk_expense_attachments
    PRIMARY KEY (id),
  -- => Named primary key on id
  CONSTRAINT fk_attachments_expense
    FOREIGN KEY (expense_id)
    REFERENCES expenses (id)
    ON DELETE CASCADE
    -- => DELETE CASCADE: deleting an expense deletes its attachments automatically
    ON UPDATE CASCADE
    -- => UPDATE CASCADE: if expenses.id changes, expense_attachments.expense_id updates automatically
    -- => Rarely triggered for UUID PKs; important for natural-key schemas
);
 
CREATE INDEX idx_expense_attachments_expense_id
    ON expense_attachments (expense_id);
-- => Index on foreign key column: prevents full table scan when fetching expense's attachments
-- => PostgreSQL does not automatically index foreign key columns (unlike MySQL InnoDB)

-- File: src/main/resources/db/migration/V9__create_user_permissions.sql
-- => user_permissions uses a composite primary key: (user_id, permission_code)
-- => This prevents a user from being granted the same permission twice at the DB level
 
CREATE TABLE user_permissions (
  user_id         UUID         NOT NULL,
  -- => References users.id; part of composite PK
  permission_code VARCHAR(50)  NOT NULL,
  -- => Permission identifier: "READ_EXPENSES", "WRITE_EXPENSES", "ADMIN"
  granted_at      TIMESTAMPTZ  NOT NULL DEFAULT NOW(),
  -- => When this permission was granted
  granted_by      VARCHAR(255) NOT NULL,
  -- => Who granted the permission (username or system)
  CONSTRAINT pk_user_permissions
    PRIMARY KEY (user_id, permission_code),
    -- => Composite PK: the pair (user_id, permission_code) must be unique
    -- => Prevents: INSERT (user_id=X, 'READ_EXPENSES') twice for same user
  CONSTRAINT fk_user_permissions_user
    FOREIGN KEY (user_id)
    REFERENCES users (id)
    ON DELETE CASCADE
    -- => Deleting a user removes all their permissions automatically
);
 
-- Index on permission_code alone (user_id already indexed as leading PK column)
CREATE INDEX idx_user_permissions_code
    ON user_permissions (permission_code);
-- => Supports queries like "find all users with ADMIN permission"
-- => user_id is already the leading column of the composite PK index

-- File: src/main/resources/db/migration/V10__add_partial_indexes.sql
-- => Partial indexes: index only the rows that queries actually filter on
 
-- Partial index: only active (non-deleted) users
CREATE UNIQUE INDEX idx_users_email_active
    ON users (email)
    WHERE deleted_at IS NULL;
-- => Only indexes rows where deleted_at IS NULL (active users)
-- => Size: proportional to active users, not total users
-- => Enforces email uniqueness among active users only
-- => A deleted user's email can be reused by a new registration
-- => Regular UNIQUE constraint would prevent email reuse after soft-delete
 
-- Partial index for pending expenses (recent data, frequently queried)
CREATE INDEX idx_expenses_pending_by_user
    ON expenses (user_id, date DESC)
    WHERE date >= CURRENT_DATE - INTERVAL '90 days';
-- => Only indexes expenses from the last 90 days
-- => Supports "show recent expenses" queries with user_id filter + date ordering
-- => Old expenses are excluded: they rarely appear in active user dashboards
-- => Smaller index = faster writes and faster scans for the common case
 
-- Index on non-null optional column (only index rows where column has a value)
CREATE INDEX idx_attachments_storage_key
    ON expense_attachments (storage_key)
    WHERE storage_key IS NOT NULL;
-- => Only indexes rows with a storage_key value
-- => Avoids indexing NULL entries which are never searched by storage key

-- File: src/main/resources/db/migration/V11__add_fulltext_search.sql
-- => Full-text search: tsvector computed column + GIN index
-- => Enables searches like "find all expenses mentioning 'coffee' in description"
 
-- Add a computed tsvector column for full-text search on expenses
ALTER TABLE expenses
    ADD COLUMN search_vector tsvector
    GENERATED ALWAYS AS (
        to_tsvector('english',
            COALESCE(description, '') || ' ' || COALESCE(category, '')
        )
    ) STORED;
-- => tsvector: PostgreSQL text search document type
-- => GENERATED ALWAYS AS ... STORED: computed column stored on disk (PostgreSQL 12+)
-- => to_tsvector('english', ...): parse text into lexemes using English dictionary
-- => Lexemes: normalized word forms ("running" -> "run", "expenses" -> "expens")
-- => COALESCE: replace NULL with empty string to avoid NULL concatenation issues
-- => Combines description + category into one searchable document
 
-- GIN index on the tsvector column for fast text search
CREATE INDEX idx_expenses_search_vector
    ON expenses USING GIN (search_vector);
-- => GIN (Generalized Inverted Index): optimal index type for tsvector columns
-- => Maps each lexeme to the rows containing it (inverted index)
-- => Supports @@ operator: search_vector @@ to_tsquery('english', 'coffee')
-- => Much faster than LIKE '%coffee%' which requires full table scan
 
-- Example query (documentation only; not executed by migration):
-- SELECT * FROM expenses WHERE search_vector @@ to_tsquery('english', 'coffee & food');
-- => Finds expenses whose description/category contains both "coffee" and "food" lexemes

-- File: src/main/resources/db/migration/V12__create_expense_summary_view.sql
-- => View: encapsulates a complex aggregation query behind a simple name
-- => Applications can SELECT FROM expense_summary as if it were a table
 
CREATE VIEW expense_summary AS
-- => CREATE VIEW: defines a named query stored in the database catalog
-- => Does NOT store data; executes the SELECT each time the view is queried
SELECT
    e.user_id,                           -- => The user who owns these expenses
    e.currency,                          -- => Currency of the expenses
    e.category,                          -- => Expense category (FOOD, TRANSPORT, etc.)
    DATE_TRUNC('month', e.date) AS month,
    -- => DATE_TRUNC('month'): truncate date to first day of its month
    -- => Example: 2026-03-15 -> 2026-03-01
    -- => Enables GROUP BY month without a separate month column
    COUNT(*)              AS total_count,
    -- => Number of expenses in this (user, currency, category, month) group
    SUM(e.amount)         AS total_amount,
    -- => Sum of all expense amounts in this group
    AVG(e.amount)         AS avg_amount,
    -- => Average expense amount in this group
    MIN(e.date)           AS earliest_date,
    -- => Earliest expense date in this period
    MAX(e.date)           AS latest_date
    -- => Most recent expense date in this period
FROM expenses e
WHERE e.deleted_at IS NULL
-- => Exclude soft-deleted expenses from the summary
GROUP BY e.user_id, e.currency, e.category, DATE_TRUNC('month', e.date);
-- => One row per (user, currency, category, month) combination

-- File: src/main/resources/db/migration/V13__create_monthly_totals_materialized_view.sql
-- => Materialized view: stores aggregation results on disk for fast reads
-- => Trades fresh data for query speed; must be refreshed explicitly
 
CREATE MATERIALIZED VIEW monthly_expense_totals AS
-- => MATERIALIZED VIEW: stores results in a physical table-like structure
-- => Query executes once at CREATE time (and again at each REFRESH)
-- => Reads from this view are instant (no computation): SELECT * FROM monthly_expense_totals
SELECT
    user_id,                                -- => User identity
    currency,                               -- => Currency of expenses
    DATE_TRUNC('month', date) AS month,    -- => Month bucket for aggregation
    SUM(amount)  AS total_amount,           -- => Total spending this month
    COUNT(*)     AS expense_count           -- => Number of expenses this month
FROM expenses
WHERE deleted_at IS NULL                    -- => Only non-deleted expenses
GROUP BY user_id, currency, DATE_TRUNC('month', date)
WITH DATA;
-- => WITH DATA: populate the view immediately during creation
-- => Alternative: WITH NO DATA creates empty view, populate later with REFRESH
 
-- Index the materialized view for fast lookups by user
CREATE INDEX idx_monthly_totals_user
    ON monthly_expense_totals (user_id, month DESC);
-- => B-tree index: fast lookup for "all months for user X sorted newest first"
-- => Materialized views support indexes; regular views do not
 
-- Note: Refresh command (run outside migrations, e.g., nightly cron):
-- REFRESH MATERIALIZED VIEW CONCURRENTLY monthly_expense_totals;
-- => CONCURRENTLY: refresh without locking the view for reads (requires unique index)

-- File: src/main/resources/db/migration/V14__create_updated_at_trigger.sql
-- => Trigger: automatically update "updated_at" timestamp on every UPDATE
 
-- Step 1: Create the trigger function
CREATE OR REPLACE FUNCTION set_updated_at()
-- => CREATE OR REPLACE FUNCTION: safe to rerun; replaces existing function
-- => Returns TRIGGER: special return type required for trigger functions
RETURNS TRIGGER AS $$
-- => $$ ... $$: dollar-quoting; avoids escaping single quotes inside function body
BEGIN
    NEW.updated_at = NOW();
    -- => NEW: the row being inserted or updated (available in BEFORE triggers)
    -- => Set updated_at to current timestamp on every update
    -- => NOW(): current transaction timestamp (constant within one transaction)
    RETURN NEW;
    -- => RETURN NEW: return the modified row; required for BEFORE triggers
    -- => BEFORE trigger with RETURN NULL would cancel the operation
END;
$$ LANGUAGE plpgsql;
-- => plpgsql: PostgreSQL's procedural language for trigger functions
 
-- Step 2: Attach the trigger to the expenses table
CREATE TRIGGER trg_expenses_updated_at
-- => Trigger name: trg_<table>_<purpose> naming convention
BEFORE UPDATE ON expenses
-- => BEFORE UPDATE: fires before the UPDATE commits, allowing us to modify NEW
FOR EACH ROW                        -- => Fires once per updated row (not once per statement)
EXECUTE FUNCTION set_updated_at();
-- => Call the trigger function defined above
 
-- Attach the same trigger function to users table
CREATE TRIGGER trg_users_updated_at
BEFORE UPDATE ON users
FOR EACH ROW
EXECUTE FUNCTION set_updated_at();
-- => Reuse the same function across multiple tables

-- File: src/main/resources/db/migration/V15__create_archive_expenses_procedure.sql
-- => Stored procedure: server-side logic for archiving old expenses
-- => Procedures (CREATE PROCEDURE) support COMMIT/ROLLBACK; functions do not
 
CREATE OR REPLACE PROCEDURE archive_old_expenses(cutoff_date DATE)
-- => CREATE PROCEDURE: server-side subroutine callable from application or SQL
-- => cutoff_date: parameter specifying which expenses to archive (older than this date)
LANGUAGE plpgsql AS $$
-- => plpgsql: PostgreSQL procedural language for control flow
DECLARE
    archived_count INTEGER := 0;  -- => Local variable to track archived rows
BEGIN
    -- Insert old expenses into archive table
    INSERT INTO expenses_archive
        SELECT * FROM expenses
        WHERE date < cutoff_date
          AND deleted_at IS NULL;
    -- => Copy qualifying rows to archive table before deleting from main table
    -- => deleted_at IS NULL: only archive non-deleted rows
 
    GET DIAGNOSTICS archived_count = ROW_COUNT;
    -- => ROW_COUNT: number of rows affected by the last SQL statement
    -- => Capture count for the RAISE NOTICE below
 
    -- Delete archived expenses from main table
    DELETE FROM expenses
    WHERE date < cutoff_date
      AND deleted_at IS NULL;
    -- => Remove the archived rows from the main expenses table
 
    RAISE NOTICE 'Archived % expenses older than %', archived_count, cutoff_date;
    -- => RAISE NOTICE: prints to PostgreSQL log (visible in application logs)
    -- => Output: "Archived 1523 expenses older than 2025-01-01"
 
    COMMIT;
    -- => COMMIT inside a procedure: commits the INSERT and DELETE together
    -- => Functions cannot COMMIT; procedures can (PostgreSQL 11+)
END;
$$;
 
-- Call with: CALL archive_old_expenses('2025-01-01');

-- File: src/main/resources/db/migration/V16__add_metadata_columns.sql
-- => Add JSONB columns for flexible, schema-free metadata storage
 
ALTER TABLE expenses
    ADD COLUMN metadata JSONB DEFAULT '{}' NOT NULL;
-- => JSONB: binary-parsed JSON; supports operators (->>, @>, ?, etc.)
-- => DEFAULT '{}': empty JSON object as default (not NULL)
-- => NOT NULL: ensures metadata always has a value (empty object {} is valid)
-- => JSON vs JSONB: JSON stores original text; JSONB parses and stores binary
-- =>   JSONB: faster reads, supports indexes, normalizes key order
-- =>   JSON: faster writes, preserves original formatting and key order
-- => Production preference: JSONB for data you will query; JSON for raw storage only
 
ALTER TABLE users
    ADD COLUMN preferences JSONB DEFAULT '{
      "theme": "light",
      "currency": "IDR",
      "notifications": true
    }' NOT NULL;
-- => Default preferences for all existing users when this migration runs
-- => Each key-value pair in the default is a user preference
-- => New user registrations should explicitly set preferences in application code
 
-- Example JSONB queries (documentation, not executed by migration):
-- SELECT id, metadata->>'receipt_url' FROM expenses WHERE metadata ? 'receipt_url';
-- => ->>: extract text value for key
-- => ?: check if key exists
 
-- SELECT id FROM users WHERE preferences @> '{"theme": "dark"}';
-- => @>: containment operator: does preferences contain {"theme": "dark"}?

-- File: src/main/resources/db/migration/V17__add_tags_to_expenses.sql
-- => Add a text array column for expense tags
-- => Arrays: store multiple values in one column without a join table
 
ALTER TABLE expenses
    ADD COLUMN tags TEXT[] DEFAULT '{}' NOT NULL;
-- => TEXT[]: array of text values
-- => DEFAULT '{}': empty array (not NULL) for existing rows
-- => NOT NULL: ensures tags always has a value; use empty array instead of NULL
 
-- Example array queries (documentation, not executed by migration):
-- INSERT INTO expenses (description, tags) VALUES ('Lunch', ARRAY['food', 'team-outing']);
-- => ARRAY['food', 'team-outing']: array literal syntax
 
-- SELECT * FROM expenses WHERE 'food' = ANY(tags);
-- => ANY(tags): check if 'food' appears anywhere in the tags array
-- => Equivalent to: WHERE tags @> ARRAY['food']
 
-- SELECT * FROM expenses WHERE tags @> ARRAY['food', 'team'];
-- => @>: containment: expenses tagged with BOTH 'food' AND 'team'
 
-- UPDATE expenses SET tags = tags || ARRAY['reviewed'] WHERE id = $1;
-- => ||: array concatenation operator; appends 'reviewed' to existing tags
 
-- GIN index for efficient array containment queries
CREATE INDEX idx_expenses_tags
    ON expenses USING GIN (tags);
-- => GIN index: supports @>, <@, &&, and = ANY() operators on arrays
-- => Without this index: 'food' = ANY(tags) requires full table scan

-- File: src/main/resources/db/migration/V18__add_gin_indexes.sql
-- => GIN indexes for JSONB columns: enable fast containment and key-existence queries
 
-- GIN index on expenses.metadata for JSONB operators
CREATE INDEX idx_expenses_metadata_gin
    ON expenses USING GIN (metadata);
-- => GIN USING GIN: required index type for JSONB operators @>, ?, ?|, ?&
-- => Supports: metadata @> '{"category": "food"}' (containment)
-- =>           metadata ? 'receipt_url' (key existence)
-- =>           metadata ?| ARRAY['receipt_url', 'merchant'] (any key exists)
-- =>           metadata ?& ARRAY['receipt_url', 'merchant'] (all keys exist)
-- => Does NOT support: metadata->>'amount' > '100' (expression, needs separate index)
 
-- GIN index on users.preferences for containment queries
CREATE INDEX idx_users_preferences_gin
    ON users USING GIN (preferences);
-- => Supports: preferences @> '{"theme": "dark"}' (user preference lookup)
-- => Useful for analytics: "how many users prefer dark theme?"
 
-- Expression index for extracting a specific JSONB key as text (for equality/range queries)
CREATE INDEX idx_expenses_metadata_receipt
    ON expenses ((metadata->>'receipt_url'))
    WHERE metadata ? 'receipt_url';
-- => Expression index: indexes the result of metadata->>'receipt_url'
-- => Partial: only indexes rows where receipt_url key exists
-- => Supports: WHERE metadata->>'receipt_url' = 'https://...' (equality)
-- => More selective than full GIN index for single-key lookups

-- File: src/main/resources/db/migration/V19__create_partitioned_audit_log.sql
-- => Range-partitioned table: one partition per year for audit_log
-- => Queries filtering by year touch only one partition, not the full table
 
CREATE TABLE audit_log (
  id          BIGSERIAL    NOT NULL,
  -- => BIGSERIAL: auto-incrementing BIGINT; use BIGSERIAL for high-volume audit tables
  table_name  VARCHAR(100) NOT NULL,   -- => Which table was audited
  operation   VARCHAR(10)  NOT NULL,   -- => INSERT, UPDATE, DELETE
  row_id      UUID         NOT NULL,   -- => Primary key of the audited row
  changed_by  VARCHAR(255) NOT NULL,   -- => Who made the change
  changed_at  TIMESTAMPTZ  NOT NULL DEFAULT NOW(),
  -- => Timestamp: the partition key column
  old_values  JSONB,                  -- => Previous row values (NULL for INSERT)
  new_values  JSONB                   -- => New row values (NULL for DELETE)
) PARTITION BY RANGE (changed_at);
-- => PARTITION BY RANGE (changed_at): partition on the changed_at timestamp column
-- => Range partitioning: each partition covers a contiguous range of values
 
-- Create initial year partitions
CREATE TABLE audit_log_2025
    PARTITION OF audit_log
    FOR VALUES FROM ('2025-01-01') TO ('2026-01-01');
-- => Partition for 2025: stores rows where changed_at >= 2025-01-01 AND < 2026-01-01
 
CREATE TABLE audit_log_2026
    PARTITION OF audit_log
    FOR VALUES FROM ('2026-01-01') TO ('2027-01-01');
-- => Partition for 2026: new rows for 2026 go here automatically (partition pruning)
 
-- Index on each partition (partitioned tables do not auto-inherit parent indexes)
CREATE INDEX idx_audit_log_2025_changed_at ON audit_log_2025 (changed_at DESC);
CREATE INDEX idx_audit_log_2026_changed_at ON audit_log_2026 (changed_at DESC);
-- => Each partition needs its own index for efficient range queries within the partition

-- File: src/main/resources/db/migration/V20__add_generated_columns.sql
-- => Generated columns: automatically computed from other columns
-- => GENERATED ALWAYS AS ... STORED: value persisted on disk; updated on every row change
 
ALTER TABLE expenses
    ADD COLUMN amount_idr NUMERIC(20, 2)
    GENERATED ALWAYS AS (
        CASE currency
            WHEN 'IDR' THEN amount
            -- => IDR to IDR: no conversion needed; return amount as-is
            WHEN 'USD' THEN amount * 15000
            -- => USD to IDR: approximate exchange rate (use live rate in application logic)
            WHEN 'EUR' THEN amount * 16500
            -- => EUR to IDR: approximate exchange rate
            ELSE amount
            -- => Unknown currency: return amount unchanged
        END
    ) STORED;
-- => STORED: computed value persisted in the row; costs storage but reads are instant
-- => VIRTUAL (not yet supported in PostgreSQL): computed at query time, no storage cost
-- => Application cannot INSERT or UPDATE amount_idr directly; PostgreSQL rejects it
-- => Updated automatically whenever amount or currency changes
 
-- Generated column for full name
ALTER TABLE users
    ADD COLUMN full_name VARCHAR(355)
    GENERATED ALWAYS AS (
        display_name
        -- => For this schema: full_name is an alias for display_name
        -- => In schemas with first_name + last_name: (first_name || ' ' || last_name)
    ) STORED;
-- => Enables ORDER BY full_name, WHERE full_name ILIKE '%search%'
-- => Without generated column: every query would repeat the concatenation expression

// File: src/test/kotlin/db/migration/MigrationTest.kt
// => JUnit 5 test verifying Flyway migrations run successfully
 
import org.flywaydb.core.Flyway       // => Flyway core API
import org.junit.jupiter.api.Test     // => JUnit 5 @Test annotation
import org.junit.jupiter.api.Assertions.*
// => JUnit 5 assertion functions: assertEquals, assertTrue, etc.
 
import java.sql.DriverManager         // => JDBC connection management
 
class MigrationTest {
    // => Test class: verifies Flyway migrations apply cleanly
 
    @Test
    fun `all migrations apply cleanly`() {
        // => Test: run all migrations against H2 in-memory database
        // => H2 PostgreSQL mode: emulates PostgreSQL syntax for most DDL
 
        val flyway = Flyway.configure()   // => Fluent builder
            .dataSource(
                "jdbc:h2:mem:testdb;MODE=PostgreSQL;DB_CLOSE_DELAY=-1",
                // => H2 in-memory URL; MODE=PostgreSQL: enable PostgreSQL compatibility
                // => DB_CLOSE_DELAY=-1: keep database alive as long as JVM runs
                "sa",                     // => H2 default username
                ""                        // => H2 default password (empty)
            )
            .locations("classpath:db/migration")
            // => Scan this classpath location for migration files
            // => Same location used in production configuration
            .load()
 
        val result = flyway.migrate()     // => Apply all migrations
        assertTrue(result.success, "All migrations should succeed")
        // => Assertion: result.success must be true; fails test if any migration failed
        assertTrue(result.migrationsExecuted > 0,
            "At least one migration should have run")
        // => Assertion: at least one migration applied (catches empty migration directory)
    }
 
    @Test
    fun `schema history table is populated`() {
        val flyway = Flyway.configure()
            .dataSource(
                "jdbc:h2:mem:historydb;MODE=PostgreSQL;DB_CLOSE_DELAY=-1",
                "sa", ""
            )
            .locations("classpath:db/migration")
            .load()
 
        flyway.migrate()                  // => Apply all migrations
 
        val connection = DriverManager.getConnection(
            "jdbc:h2:mem:historydb", "sa", ""
        )
        // => Get JDBC connection to the same in-memory database
        val stmt = connection.createStatement()
        val rs = stmt.executeQuery(
            "SELECT COUNT(*) FROM flyway_schema_history WHERE success = TRUE"
        )
        // => Count successful migrations in flyway_schema_history
        rs.next()
        val successCount = rs.getInt(1)   // => Get count value
        assertTrue(successCount > 0, "Schema history should have successful entries")
        // => Assertion: at least one successful migration recorded
        connection.close()                // => Close connection when done
    }
}

// File: src/test/kotlin/db/migration/PostgresMigrationTest.kt
// => Integration test using Testcontainers PostgreSQL for production-identical testing
 
import org.flywaydb.core.Flyway         // => Flyway core API
import org.junit.jupiter.api.Test       // => JUnit 5 @Test annotation
import org.junit.jupiter.api.Assertions.*
import org.testcontainers.containers.PostgreSQLContainer
// => Testcontainers PostgreSQL: starts a real PostgreSQL Docker container
import org.testcontainers.junit.jupiter.Container
// => @Container: Testcontainers JUnit 5 integration; manages container lifecycle
import org.testcontainers.junit.jupiter.Testcontainers
// => @Testcontainers: activates Testcontainers JUnit 5 extension
 
@Testcontainers
// => Activates Testcontainers extension: starts @Container fields before tests
class PostgresMigrationTest {
 
    companion object {
        @Container
        val postgres = PostgreSQLContainer("postgres:15")
            // => Start PostgreSQL 15 container; matches production PostgreSQL version
            .withDatabaseName("testdb")   // => Database name inside container
            .withUsername("testuser")     // => Container username
            .withPassword("testpass")     // => Container password
        // => Container starts once per class (companion object @Container is static)
        // => JDBC URL available via postgres.jdbcUrl after container starts
    }
 
    @Test
    fun `all migrations apply on real PostgreSQL`() {
        val flyway = Flyway.configure()    // => Fluent builder
            .dataSource(
                postgres.jdbcUrl,          // => Real PostgreSQL JDBC URL from container
                // => Example: jdbc:postgresql://localhost:32768/testdb
                postgres.username,         // => "testuser"
                postgres.password          // => "testpass"
            )
            .locations("classpath:db/migration")
            // => Same migration location as production
            .load()
 
        val result = flyway.migrate()      // => Apply all migrations to real PostgreSQL
        assertTrue(result.success, "All migrations must succeed on PostgreSQL")
        // => Validates real PostgreSQL syntax: gen_random_uuid(), tsvector, GIN indexes
        println("Applied ${result.migrationsExecuted} migrations successfully")
        // => Output: "Applied 20 migrations successfully"
    }
 
    @Test
    fun `expenses table has correct structure`() {
        val flyway = Flyway.configure()
            .dataSource(postgres.jdbcUrl, postgres.username, postgres.password)
            .locations("classpath:db/migration")
            .load()
        flyway.migrate()                   // => Apply all migrations first
 
        val conn = postgres.createConnection("")
        // => createConnection: Testcontainers helper to get a JDBC Connection
        val rs = conn.metaData.getColumns(null, null, "expenses", null)
        // => getColumns(): returns metadata for all columns in "expenses" table
        val columns = mutableSetOf<String>()
        while (rs.next()) {
            columns.add(rs.getString("COLUMN_NAME"))
            // => Collect all column names into a Set
        }
        assertTrue(columns.contains("amount_idr"),
            "expenses should have generated column amount_idr")
        // => Verifies Example 57's generated column was created
        assertTrue(columns.contains("metadata"),
            "expenses should have JSONB metadata column")
        // => Verifies Example 53's metadata column was created
        conn.close()
    }
}

// File: src/main/kotlin/com/example/database/DatabaseFactory.kt
// => DatabaseFactory: initializes HikariCP connection pool, runs Flyway, configures Exposed
 
import com.zaxxer.hikari.HikariConfig    // => HikariCP connection pool configuration
import com.zaxxer.hikari.HikariDataSource// => HikariCP connection pool DataSource
import org.flywaydb.core.Flyway          // => Flyway core API
import org.jetbrains.exposed.sql.Database// => Exposed: connect to JDBC DataSource
import org.jetbrains.exposed.sql.transactions.transaction
// => Exposed transaction: wraps SQL in a database transaction
 
object DatabaseFactory {
    // => Kotlin object: singleton; DatabaseFactory.init() called once at startup
 
    fun init(jdbcUrl: String, user: String, password: String) {
        // => Called from Application.kt module initialization
 
        // Step 1: Configure HikariCP connection pool
        val hikariConfig = HikariConfig().apply {
            this.jdbcUrl = jdbcUrl          // => JDBC URL for PostgreSQL connection
            this.username = user            // => Database username
            this.password = password        // => Database password
            maximumPoolSize = 10            // => Max concurrent connections in pool
            isAutoCommit = false            // => Exposed manages transactions manually
            transactionIsolation = "TRANSACTION_REPEATABLE_READ"
            // => Isolation level: repeatable reads prevent phantom reads in transactions
            validate()                      // => Validate HikariCP config before creating pool
        }
        val dataSource = HikariDataSource(hikariConfig)
        // => dataSource: the configured connection pool; reused for all database operations
 
        // Step 2: Run Flyway migrations against the same DataSource
        val flyway = Flyway.configure()    // => Flyway fluent builder
            .dataSource(dataSource)        // => Reuse HikariCP DataSource (not a separate connection)
            .locations("classpath:db/migration")
            // => Scan classpath for migration files
            .outOfOrder(false)             // => Enforce strict version ordering in production
            .validateOnMigrate(true)       // => Validate checksums before applying (detects file tampering)
            .load()
 
        flyway.migrate()                   // => Apply all pending migrations
        // => After migrate(): schema is in the latest state; safe for Exposed to use
 
        // Step 3: Connect Exposed to the same DataSource
        Database.connect(dataSource)       // => Exposed uses HikariCP connections for all queries
        // => Exposed table objects now map to the schema Flyway just created/updated
    }
}

// File: src/main/kotlin/com/example/database/tables/ExpensesTable.kt
// => Exposed table object: mirrors the Flyway-created "expenses" schema
 
import org.jetbrains.exposed.dao.id.UUIDTable
// => UUIDTable: Exposed DSL base for tables with UUID primary key
import org.jetbrains.exposed.sql.javatime.timestampWithTimeZone
// => timestampWithTimeZone: maps to TIMESTAMPTZ column in PostgreSQL
 
object ExpensesTable : UUIDTable("expenses") {
    // => UUIDTable("expenses"): maps to "expenses" table; id column is UUID PK (from Flyway V1)
    // => Column definitions must match the Flyway-migrated schema exactly
 
    val userId      = uuid("user_id")
    // => Maps to expenses.user_id (UUID FK to users.id from Flyway migration)
    val type        = varchar("type", 10)
    // => Maps to expenses.type VARCHAR(10); max length must match migration
    val amount      = decimal("amount", 20, 8)
    // => Maps to expenses.amount DECIMAL(20,8) from migration
    val currency    = varchar("currency", 10)
    // => Maps to expenses.currency VARCHAR(10)
    val category    = varchar("category", 100)
    // => Maps to expenses.category VARCHAR(100)
    val description = varchar("description", 500)
    // => Maps to expenses.description VARCHAR(500)
    val date        = org.jetbrains.exposed.sql.javatime.date("date")
    // => Maps to expenses.date DATE column
    val createdAt   = timestampWithTimeZone("created_at")
    // => Maps to expenses.created_at TIMESTAMPTZ; auto-set by trigger (Example 51)
    val updatedAt   = timestampWithTimeZone("updated_at")
    // => Maps to expenses.updated_at TIMESTAMPTZ; auto-updated by trigger (Example 51)
}