Intermediate

-- liquibase formatted sql
-- => File contains three changesets: one context-free, two context-specific
 
-- changeset demo-be:010-create-config-table dbms:postgresql
-- => No context attribute: runs in every environment unconditionally
CREATE TABLE app_config (
    key   VARCHAR(100) NOT NULL,
    -- => Configuration key, e.g. "feature.dark-mode"
    value VARCHAR(500) NOT NULL,
    -- => Configuration value stored as string; parse in application layer
    CONSTRAINT pk_app_config PRIMARY KEY (key)
    -- => key is the primary key; no surrogate id needed for lookup-by-key tables
);
-- rollback DROP TABLE app_config;
 
-- changeset demo-be:011-seed-dev-config dbms:postgresql context:dev
-- => context:dev means this changeset only runs when "dev" context is active
-- => Spring Boot: spring.liquibase.contexts=dev
-- => CLI: liquibase update --contexts=dev
-- => This changeset is invisible to prod runs (never enters DATABASECHANGELOG in prod)
INSERT INTO app_config (key, value) VALUES
    ('feature.dark-mode', 'true'),
    -- => Development seed: enable dark mode by default for local testing
    ('feature.rate-limit', '1000'),
    -- => High rate limit for dev to avoid friction during local development
    ('debug.verbose-sql', 'true');
    -- => Verbose SQL logging enabled only in dev environment
-- rollback DELETE FROM app_config WHERE key IN ('feature.dark-mode','feature.rate-limit','debug.verbose-sql');
 
-- changeset demo-be:012-seed-prod-config dbms:postgresql context:prod
-- => context:prod runs only in production; same changeset ID space as dev seed
-- => Two separate changesets cover each environment without runtime branching
INSERT INTO app_config (key, value) VALUES
    ('feature.dark-mode', 'false'),
    -- => Production default: dark mode off until user toggles
    ('feature.rate-limit', '100'),
    -- => Conservative rate limit for production traffic protection
    ('debug.verbose-sql', 'false');
    -- => No verbose SQL in production; reduces log storage costs
-- rollback DELETE FROM app_config WHERE key IN ('feature.dark-mode','feature.rate-limit','debug.verbose-sql');

# File: src/main/resources/db/changelog/changes/013-label-examples.yaml
 
databaseChangeLog:
  - changeSet:
      id: 013-security-columns
      # => This changeset is tagged with two labels
      author: demo-be
      labels: security,compliance
      # => labels: comma-separated list of tags on this changeset
      # => Active filter "security" matches because "security" is in the label list
      # => Active filter "security AND compliance" also matches
      # => Active filter "security AND NOT compliance" does NOT match
      changes:
        - addColumn:
            tableName: users
            columns:
              - column:
                  name: last_password_change
                  type: TIMESTAMPTZ
                  # => Track when password was last updated for compliance reporting
                  constraints:
                    nullable: true
                    # => Nullable: existing users have no recorded change date
              - column:
                  name: mfa_enabled
                  type: BOOLEAN
                  # => Multi-factor authentication flag
                  constraints:
                    nullable: false
                  defaultValueBoolean: false
                  # => defaultValueBoolean for boolean columns; false = MFA optional at signup
 
  - changeSet:
      id: 014-performance-indexes
      author: demo-be
      labels: performance
      # => Single label; only runs when "performance" filter is active
      # => Example: liquibase update --label-filter="performance"
      # => Spring Boot: spring.liquibase.label-filter=performance
      changes:
        - createIndex:
            tableName: expenses
            indexName: idx_expenses_user_date
            # => Composite index name follows convention: idx_{table}_{columns}
            columns:
              - column:
                  name: user_id
                  # => Leading column: high-cardinality FK for equality filters
              - column:
                  name: date
                  # => Trailing column: range queries on date after user_id equality

# File: src/main/resources/db/changelog/changes/015-guarded-migration.yaml
 
databaseChangeLog:
  - changeSet:
      id: 015-add-email-to-users
      author: demo-be
      preConditions:
        # => Preconditions evaluated BEFORE the changeset SQL executes
        # => If the check fails, onFail controls behavior (see Example 34)
        - onFail: MARK_RAN
          # => MARK_RAN: record changeset in DATABASECHANGELOG without executing SQL
          # => Use when column may already exist (e.g., manually created in hotfix)
          onError: HALT
          # => HALT: stop Liquibase entirely on unexpected errors
        - tableExists:
            tableName: users
            # => Verify "users" table exists before attempting to add a column
            # => Fails if a previous migration was rolled back and users table was dropped
        - not:
            # => Negate a condition: the enclosed check must be FALSE to proceed
            - columnExists:
                tableName: users
                columnName: email
                # => Verify "email" column does NOT exist
                # => Guards against re-running a hotfix that already added the column
      changes:
        - addColumn:
            tableName: users
            columns:
              - column:
                  name: email
                  type: VARCHAR(255)
                  # => Email column added only when preconditions pass
                  constraints:
                    nullable: true
                    # => Nullable initially; subsequent changeset can add NOT NULL constraint
                    unique: true
                    # => Unique constraint prevents duplicate emails at DB level

# File: src/main/resources/db/changelog/changes/016-precondition-strategies.yaml
 
databaseChangeLog:
  - changeSet:
      id: 016-create-audit-log
      author: demo-be
      preConditions:
        - onFail: CONTINUE
          # => CONTINUE: skip this changeset silently (not recorded in DATABASECHANGELOG)
          # => Use when the changeset is optional and absence is expected in some envs
          onError: HALT
          # => HALT on unexpected errors (e.g., database connection issues)
          # => Distinguishes "expected absence" (CONTINUE) from "unexpected problem" (HALT)
        - tableExists:
            tableName: users
            # => Only create audit_log if users table already exists
            # => In a fresh install, users may not exist yet; CONTINUE skips gracefully
      changes:
        - createTable:
            tableName: audit_log
            columns:
              - column:
                  name: id
                  type: BIGSERIAL
                  # => BIGSERIAL: auto-incrementing BIGINT; audit tables grow large
                  constraints:
                    primaryKey: true
                    nullable: false
              - column:
                  name: table_name
                  type: VARCHAR(100)
                  # => Which table was affected
                  constraints:
                    nullable: false
              - column:
                  name: operation
                  type: VARCHAR(10)
                  # => INSERT, UPDATE, DELETE
                  constraints:
                    nullable: false
              - column:
                  name: performed_at
                  type: TIMESTAMPTZ
                  defaultValueComputed: NOW()
                  constraints:
                    nullable: false
 
  - changeSet:
      id: 017-drop-legacy-table
      author: demo-be
      preConditions:
        - onFail: MARK_RAN
          # => MARK_RAN: record changeset even if precondition fails
          # => Safe for "drop if exists" patterns: table may already be gone
          onError: WARN
          # => WARN: log warning but continue migration pipeline
          # => Useful when "error" is non-fatal (e.g., checking a view that may not exist)
        - tableExists:
            tableName: legacy_sessions
            # => Only run DROP TABLE if legacy_sessions actually exists
      changes:
        - dropTable:
            tableName: legacy_sessions
            # => Remove legacy table after data migration to new sessions table
            cascadeConstraints: true
            # => Drop dependent foreign keys before dropping table

# File: src/main/resources/db/changelog/changes/018-seed-roles.yaml
 
databaseChangeLog:
  - changeSet:
      id: 018-seed-roles
      author: demo-be
      changes:
        - insert:
            tableName: roles
            # => Target table; must already exist (use preconditions if uncertain)
            columns:
              - column:
                  name: id
                  valueNumeric: 1
                  # => valueNumeric for integer literals
              - column:
                  name: name
                  value: ADMIN
                  # => value for string literals
              - column:
                  name: description
                  value: Full system administrator access
                  # => value accepts any string literal
        - insert:
            tableName: roles
            # => Each insert change inserts exactly one row
            # => Multiple insert changes in one changeset run in a single transaction
            columns:
              - column:
                  name: id
                  valueNumeric: 2
              - column:
                  name: name
                  value: USER
              - column:
                  name: description
                  value: Standard user access
        - insert:
            tableName: roles
            columns:
              - column:
                  name: id
                  valueNumeric: 3
              - column:
                  name: name
                  value: VIEWER
              - column:
                  name: description
                  value: Read-only access
      rollback:
        # => Explicit rollback: delete the inserted rows by known IDs
        - delete:
            tableName: roles
            where: id IN (1, 2, 3)
            # => where clause removes only the rows inserted by this changeset
            # => Avoids deleting user-added roles that may exist in the same table

# File: src/main/resources/db/changelog/changes/019-backfill-user-roles.yaml
 
databaseChangeLog:
  - changeSet:
      id: 019-backfill-user-roles
      author: demo-be
      changes:
        - update:
            tableName: users
            # => Target table containing rows to update
            columns:
              - column:
                  name: role
                  value: USER
                  # => Set role column to literal "USER" for all matched rows
                  # => valueComputed: "UPPER(legacy_role)" evaluates a SQL expression
            where: role IS NULL
            # => where clause restricts which rows are updated
            # => Without where: all rows in the table are updated (full-table migration)
            # => Always include where to limit scope and avoid accidental mass updates
        - update:
            tableName: users
            columns:
              - column:
                  name: role
                  value: ADMIN
                  # => Promote users with admin flag to ADMIN role
            where: is_admin = true AND role = 'USER'
            # => Compound where: target only rows that need promotion
            # => Runs AFTER the first update (NULL->USER), so is_admin=true rows are now USER
      rollback:
        # => Rollback reverses the data migration; difficult to make perfectly reversible
        # => Best practice: store original values in a backup column before migrating
        - update:
            tableName: users
            columns:
              - column:
                  name: role
                  valueComputed: "NULL"
                  # => Restore NULL for the first update (backfill was from NULL)
            where: role = 'USER' AND is_admin = false

# File: src/main/resources/db/changelog/changes/020-purge-expired-sessions.yaml
 
databaseChangeLog:
  - changeSet:
      id: 020-purge-expired-sessions
      author: demo-be
      changes:
        - delete:
            tableName: refresh_tokens
            # => Target table; rows matching where are permanently removed
            where: expires_at < NOW() - INTERVAL '90 days'
            # => Delete tokens expired more than 90 days ago
            # => NOW() is evaluated at migration execution time, not changeset authoring time
            # => INTERVAL '90 days' is PostgreSQL syntax; use dbms:postgresql attribute for multi-DB changelogs
      rollback:
        # => Deleted rows cannot be restored by Liquibase
        # => If rollback is required, restore from backup or use a staging table approach
        - sql:
            sql: SELECT 'Rollback not available - restore from backup if needed'
            # => Placeholder rollback that does nothing harmful
            # => Acceptable for irreversible operations; document the limitation explicitly
 
  - changeSet:
      id: 021-archive-soft-deleted-users
      author: demo-be
      dbms: postgresql
      # => dbms attribute at changeset level: only executes against PostgreSQL
      changes:
        - sql:
            sql: |
              DELETE FROM users
              WHERE deleted_at IS NOT NULL
                AND deleted_at < NOW() - INTERVAL '365 days';
            splitStatements: false
            # => splitStatements:false treats the entire sql block as one statement
            # => Required when SQL contains semicolons inside strings or CTEs

# File: src/main/resources/db/changelog/changes/022-load-countries.yaml
 
databaseChangeLog:
  - changeSet:
      id: 022-load-countries
      author: demo-be
      changes:
        - loadData:
            tableName: countries
            # => Target table; must already exist
            file: db/changelog/data/countries.csv
            # => Path relative to the classpath root (src/main/resources/)
            # => The CSV file is bundled in the JAR at runtime
            separator: ","
            # => Default separator; change to ";" for European locale CSVs
            encoding: UTF-8
            # => Encoding for special characters (accents in country names)
            columns:
              - column:
                  name: iso_code
                  # => CSV column header must match exactly
                  type: STRING
                  # => Liquibase type hint: STRING maps to VARCHAR in PostgreSQL
              - column:
                  name: name
                  type: STRING
              - column:
                  name: phone_prefix
                  type: NUMERIC
                  # => NUMERIC: Liquibase parses CSV value as number before inserting
              - column:
                  name: active
                  type: BOOLEAN
                  # => BOOLEAN: Liquibase accepts "true"/"false"/"1"/"0" in CSV
      rollback:
        - delete:
            tableName: countries
            where: 1=1
            # => Delete all rows loaded by this changeset
            # => Safe when countries table is populated solely by this changeset
            # => If the table has other rows, store original IDs and delete by them

iso_code,name,phone_prefix,active
ID,Indonesia,62,true
US,United States,1,true
GB,United Kingdom,44,true
SG,Singapore,65,true
MY,Malaysia,60,true

-- liquibase formatted sql
 
-- changeset demo-be:023-migrate-amount-precision dbms:postgresql
-- => Raw SQL migration: change DECIMAL precision and backfill computed column
-- => No built-in Liquibase change type handles computed column backfills
 
-- Step 1: Add temporary staging column
ALTER TABLE expenses ADD COLUMN amount_v2 DOUBLE PRECISION;
-- => DOUBLE PRECISION replaces DECIMAL(19,4) to match application model (f64/double)
-- => Temporary column avoids locking: populate first, then swap with original
 
-- Step 2: Backfill from original column using CTE for auditability
WITH migration AS (
    SELECT id, amount::DOUBLE PRECISION AS amount_migrated
    -- => Cast DECIMAL to DOUBLE PRECISION; precision loss is acceptable for display amounts
    FROM expenses
    WHERE amount_v2 IS NULL
    -- => Only update rows not yet migrated (idempotent if migration is interrupted)
)
UPDATE expenses e
SET amount_v2 = m.amount_migrated
FROM migration m
WHERE e.id = m.id;
-- => Batch update via CTE: single statement, single lock acquisition
 
-- Step 3: Swap columns atomically
ALTER TABLE expenses ALTER COLUMN amount_v2 SET NOT NULL;
-- => Enforce NOT NULL after backfill (would fail if any row has NULL amount)
ALTER TABLE expenses DROP COLUMN amount;
-- => Remove original column after successful backfill
ALTER TABLE expenses RENAME COLUMN amount_v2 TO amount;
-- => Rename temp column to canonical name; application code unchanged
 
-- rollback ALTER TABLE expenses RENAME COLUMN amount TO amount_v2;
-- rollback ALTER TABLE expenses ADD COLUMN amount DECIMAL(19,4);
-- rollback UPDATE expenses SET amount = amount_v2::DECIMAL(19,4);
-- rollback ALTER TABLE expenses DROP COLUMN amount_v2;

-- liquibase formatted sql
 
-- changeset demo-be:024-normalize-phone-numbers dbms:postgresql
-- => Data normalization: strip non-digit characters from phone_number column
-- => Cannot be auto-rolled back; explicit rollback required
 
-- Backup original values to a shadow column before modifying
ALTER TABLE users ADD COLUMN phone_number_original VARCHAR(50);
-- => Shadow column preserves pre-migration state for rollback
-- => Rollback restores from this column; then drops it
 
UPDATE users
SET
    phone_number_original = phone_number,
    -- => Capture original value before normalization
    phone_number = REGEXP_REPLACE(phone_number, '[^0-9+]', '', 'g')
    -- => Remove all non-digit, non-plus characters
    -- => 'g' flag: replace all occurrences (not just first)
    -- => Result: "+6281234567890" from "(+62) 812-3456-7890"
WHERE phone_number IS NOT NULL;
-- => Scoped to non-null rows; NULL phone_number rows are unchanged
 
-- rollback UPDATE users SET phone_number = phone_number_original WHERE phone_number_original IS NOT NULL;
-- rollback ALTER TABLE users DROP COLUMN phone_number_original;
-- => Rollback order: restore first, then drop shadow column
-- => Cannot drop shadow column before restoring or data is lost permanently

# File: src/main/resources/db/changelog/changes/025-parameterized-tablespace.yaml
 
databaseChangeLog:
  - property:
      name: defaultSchemaName
      value: public
      # => Define a property with a default value
      # => Overridden by: --changelog-parameters=defaultSchemaName=custom_schema (CLI)
      # => Or: spring.liquibase.parameters.defaultSchemaName=custom_schema (Spring Boot)
  - property:
      name: indexTablespace
      value: pg_default
      # => Default tablespace for indexes; override per environment for tablespace isolation
  - property:
      name: textSearchConfig
      value: english
      # => Full-text search configuration; override to "indonesian" for Indonesian content
 
  - changeSet:
      id: 025-create-search-index
      author: demo-be
      dbms: postgresql
      changes:
        - sql:
            sql: |
              CREATE INDEX idx_expenses_description_fts
              ON ${defaultSchemaName}.expenses
              USING GIN (to_tsvector('${textSearchConfig}', description))
              -- => ${defaultSchemaName} and ${textSearchConfig} are replaced at runtime
              -- => CLI override: --changelog-parameters=defaultSchemaName=analytics
              TABLESPACE ${indexTablespace};
              -- => ${indexTablespace} allows tablespace to vary per environment
            splitStatements: false
      rollback:
        - sql:
            sql: DROP INDEX IF EXISTS ${defaultSchemaName}.idx_expenses_description_fts;
            # => Rollback also uses property substitution; values must match forward migration

# File: liquibase.properties (CLI configuration file)
 
url: jdbc:postgresql://localhost:5432/mydb
# => JDBC connection URL; matches Spring Boot's spring.datasource.url
 
username: myuser
# => Database username
 
password: mypassword
# => Database password; prefer environment variable substitution in production
# => Use: password: ${DB_PASSWORD} and set DB_PASSWORD in shell
 
changeLogFile: src/main/resources/db/changelog/db.changelog-master.yaml
# => Path to master changelog; same file used by Spring Boot integration

# Scenario: database already exists with correct schema, but DATABASECHANGELOG is empty
# Running liquibase update would attempt to re-create all tables -> ERROR
 
# Step 1: Sync all changesets without executing SQL
liquibase changelogSync
# => Reads all changesets from changelog
# => Inserts a row into DATABASECHANGELOG for each changeset
# => Does NOT execute any DDL or DML
# => After sync: DATABASECHANGELOG has 25 rows (one per changeset)
 
# Step 2: Verify sync was correct
liquibase status
# => Reports: "All change sets have been run against the database"
# => If any changesets are "not yet run", manually apply them or sync selectively
 
# Step 3: Future updates work normally
liquibase update
# => Only executes changesets NOT already in DATABASECHANGELOG
# => All pre-sync changesets are skipped; only new changesets run

# Compare staging database (target) against production (reference)
liquibase \
  --url=jdbc:postgresql://staging-db:5432/mydb \
  --username=myuser \
  --password=mypassword \
  --referenceUrl=jdbc:postgresql://prod-db:5432/mydb \
  --referenceUsername=myuser \
  --referencePassword=myprodpassword \
  diff
# => Compares staging (url) against production (referenceUrl)
# => Reports: tables in reference but not in target (missing tables)
# =>          tables in target but not in reference (extra tables)
# =>          column type mismatches between matched tables
# =>          missing or extra indexes, constraints, sequences
 
# Example output when staging is missing a column added by a prod hotfix:
# Missing Column(s): users.emergency_contact (VARCHAR(255))
# => Staging is behind production; apply the missing migration
 
# Diff between local dev and CI database to catch schema drift
liquibase \
  --url=jdbc:postgresql://localhost:5432/mydb_dev \
  --referenceUrl=jdbc:postgresql://ci-db:5432/mydb_ci \
  diff \
  --diffTypes=tables,columns,indexes
  # => --diffTypes filters which object types to compare
  # => Omit views, sequences, and procedures for a focused column-level check

# Generate a changelog to bring staging in sync with production
liquibase \
  --url=jdbc:postgresql://staging-db:5432/mydb \
  --referenceUrl=jdbc:postgresql://prod-db:5432/mydb \
  --username=myuser \
  --password=mypassword \
  diffChangeLog \
  --changelog-file=diff-staging-to-prod.yaml
  # => Writes generated changesets to diff-staging-to-prod.yaml
  # => Each structural difference becomes a changeset in the output file
 
# Example of a generated changeset in diff-staging-to-prod.yaml:
# databaseChangeLog:
#   - changeSet:
#       id: 1711234567890-1       # => Auto-generated ID from timestamp
#       author: liquibase-diff   # => Author set to "liquibase-diff" for generated sets
#       changes:
#         - addColumn:
#             tableName: users
#             columns:
#               - column:
#                   name: emergency_contact
#                   type: VARCHAR(255)
#                   # => Missing column from production hotfix
 
# Review the generated file before applying
liquibase \
  --url=jdbc:postgresql://staging-db:5432/mydb \
  --changelog-file=diff-staging-to-prod.yaml \
  update
  # => Apply the generated changesets to bring staging in sync
  # => ALWAYS review generated changelogs; auto-generated IDs and rollbacks may be incomplete

# Generate a full changelog from the current database schema
liquibase \
  --url=jdbc:postgresql://localhost:5432/mydb \
  --username=myuser \
  --password=mypassword \
  generateChangeLog \
  --changelog-file=generated-schema.yaml
  # => Reads all tables, columns, indexes, constraints, sequences from the database
  # => Generates a YAML changelog that recreates the entire schema from scratch
  # => Does NOT include data (rows); only DDL structure
 
# Generated output structure:
# databaseChangeLog:
#   - changeSet:
#       id: 1711234567890-1
#       author: myuser (generated)
#       # => One changeset per table, index, or constraint
#       changes:
#         - createTable:
#             tableName: users
#             columns: [...]   # => All columns with types and constraints
 
# Include data in generated changelog (use cautiously on large tables)
liquibase \
  --url=jdbc:postgresql://localhost:5432/mydb \
  generateChangeLog \
  --changelog-file=generated-with-data.yaml \
  --dataOutputDirectory=data/
  # => Generates CSV files in data/ directory for each table's rows
  # => Adds loadData change types referencing the CSV files
  # => NOT suitable for tables with millions of rows
 
# Filter generated schema with common flags
liquibase \
  --url=jdbc:postgresql://localhost:5432/mydb \
  generateChangeLog \
  --changelog-file=generated-core-tables.yaml \
  --includeCatalog=false \
  --includeSchema=true \
  --includeTablespace=false
  # => --includeSchema: include schema name in DDL (useful for multi-schema databases)
  # => --includeTablespace: omit tablespace (portable across environments)

# File: src/main/resources/db/changelog/changes/026-multi-schema.yaml
 
databaseChangeLog:
  - changeSet:
      id: 026-create-audit-schema
      author: demo-be
      changes:
        - sql:
            sql: CREATE SCHEMA IF NOT EXISTS audit;
            # => Create the audit schema if it does not already exist
            # => IF NOT EXISTS: idempotent; safe to re-run without precondition guard
            # => PostgreSQL schemas are namespaces; same table name can exist in multiple schemas
      rollback:
        - sql:
            sql: DROP SCHEMA IF EXISTS audit CASCADE;
            # => CASCADE drops all tables, views, and sequences in the schema
            # => Use only when schema is owned entirely by this changelog
 
  - changeSet:
      id: 027-create-audit-log-table
      author: demo-be
      changes:
        - createTable:
            schemaName: audit
            # => schemaName attribute targets a specific schema
            # => Without schemaName: Liquibase uses the defaultSchemaName setting
            tableName: schema_changes
            columns:
              - column:
                  name: id
                  type: BIGSERIAL
                  constraints:
                    primaryKey: true
                    nullable: false
              - column:
                  name: schema_name
                  type: VARCHAR(100)
                  # => Which schema was changed
                  constraints:
                    nullable: false
              - column:
                  name: table_name
                  type: VARCHAR(100)
                  # => Which table within the schema was changed
                  constraints:
                    nullable: false
              - column:
                  name: change_type
                  type: VARCHAR(50)
                  # => DDL operation: CREATE, ALTER, DROP
                  constraints:
                    nullable: false
              - column:
                  name: changed_at
                  type: TIMESTAMPTZ
                  defaultValueComputed: NOW()
                  constraints:
                    nullable: false
      rollback:
        - dropTable:
            schemaName: audit
            # => schemaName must match the createTable changeset
            tableName: schema_changes

# File: src/main/resources/db/changelog/changes/028-create-views.yaml
 
databaseChangeLog:
  - changeSet:
      id: 028-create-expense-summary-view
      author: demo-be
      dbms: postgresql
      changes:
        - createView:
            viewName: expense_summary
            # => View name; must be unique within the schema
            schemaName: public
            # => Target schema; defaults to defaultSchemaName if omitted
            replaceIfExists: true
            # => replaceIfExists: true uses CREATE OR REPLACE VIEW
            # => Allows updating view definition without a prior dropView changeset
            # => Safe for iterative view development
            selectQuery: |
              SELECT
                u.id          AS user_id,
                u.username    AS username,
                COUNT(e.id)   AS total_expenses,
                -- => COUNT per user, not total rows (user_id grouped)
                SUM(e.amount) AS total_amount,
                -- => SUM respects GROUP BY; NULL amounts are excluded by SUM
                AVG(e.amount) AS avg_amount,
                -- => Average per expense entry for this user
                MIN(e.date)   AS first_expense_date,
                -- => Earliest recorded expense date
                MAX(e.date)   AS last_expense_date
                -- => Most recent recorded expense date
              FROM users u
              LEFT JOIN expenses e ON e.user_id = u.id
              -- => LEFT JOIN includes users with zero expenses (COUNT=0, SUM=NULL)
              WHERE u.deleted_at IS NULL
              -- => Exclude soft-deleted users from the summary view
              GROUP BY u.id, u.username
              -- => One row per user; GROUP BY required for aggregate functions
      rollback:
        - dropView:
            viewName: expense_summary
            schemaName: public

-- liquibase formatted sql
 
-- changeset demo-be:029-create-materialized-view dbms:postgresql
-- => Materialized views store query results; Liquibase createView does not support MATERIALIZED
-- => Use raw SQL changeset for PostgreSQL-specific materialized view syntax
 
CREATE MATERIALIZED VIEW expense_monthly_summary AS
SELECT
    user_id,
    -- => User identifier; join to users table for display name
    DATE_TRUNC('month', date) AS month,
    -- => Truncate to month start: '2024-03-15' becomes '2024-03-01'
    -- => Enables grouping by calendar month
    category,
    -- => Expense category for category-level monthly breakdown
    COUNT(*)       AS expense_count,
    -- => Number of expenses in this category/month for this user
    SUM(amount)    AS total_amount,
    -- => Total spending in category/month/user combination
    AVG(amount)    AS avg_amount
    -- => Average expense size for detecting anomalies
FROM expenses
WHERE deleted_at IS NULL
-- => Exclude soft-deleted expenses from materialized view
GROUP BY user_id, DATE_TRUNC('month', date), category
-- => Three-level grouping: each row represents one user+month+category combination
WITH DATA;
-- => WITH DATA populates the view immediately on creation
-- => WITH NO DATA creates an empty view; use REFRESH MATERIALIZED VIEW to populate later
 
-- Create unique index required for CONCURRENT refresh
CREATE UNIQUE INDEX idx_expense_monthly_summary_pk
    ON expense_monthly_summary (user_id, month, category);
-- => UNIQUE INDEX enables REFRESH MATERIALIZED VIEW CONCURRENTLY
-- => CONCURRENTLY refresh does not lock reads; required for production use
-- => Without UNIQUE INDEX, CONCURRENTLY option is unavailable
 
-- rollback DROP MATERIALIZED VIEW IF EXISTS expense_monthly_summary;
-- => Dropping the view automatically drops its indexes

// Application code to refresh the materialized view (Spring Boot JdbcTemplate)
@Scheduled(cron = "0 0 * * * *")
// => Run every hour; refresh frequency depends on data freshness requirements
public void refreshExpenseSummary() {
    jdbcTemplate.execute("REFRESH MATERIALIZED VIEW CONCURRENTLY expense_monthly_summary");
    // => CONCURRENTLY: allows reads during refresh (no exclusive lock)
    // => Requires the UNIQUE INDEX created in the migration
    // => Without CONCURRENTLY: exclusive lock blocks all reads during refresh
}

-- liquibase formatted sql
 
-- changeset demo-be:030-create-updated-at-trigger dbms:postgresql
-- => Trigger to automatically maintain updated_at column on every UPDATE
-- => Alternative to application-layer updated_at management
 
-- Step 1: Create the trigger function
CREATE OR REPLACE FUNCTION update_updated_at_column()
RETURNS TRIGGER AS $$
-- => $$ delimiter marks the function body (dollar quoting avoids nested quote escaping)
BEGIN
    NEW.updated_at = NOW();
    -- => NEW: the row being inserted/updated (vs OLD for previous values)
    -- => Directly modify NEW before the row is written to disk
    RETURN NEW;
    -- => RETURN NEW: required for BEFORE triggers to proceed with the modified row
    -- => RETURN NULL: cancels the operation (used in BEFORE triggers for validation)
END;
$$ LANGUAGE plpgsql;
-- => LANGUAGE plpgsql: PostgreSQL's procedural language; required for triggers
 
-- Step 2: Attach trigger to the expenses table
CREATE TRIGGER trg_expenses_updated_at
    BEFORE UPDATE ON expenses
    -- => BEFORE UPDATE: fires before the row is written
    -- => AFTER UPDATE: fires after the row is written (cannot modify row data)
    FOR EACH ROW
    -- => FOR EACH ROW: fires once per affected row (vs FOR EACH STATEMENT)
    EXECUTE FUNCTION update_updated_at_column();
    -- => Calls the function created above; NEW.updated_at is set automatically
 
-- Attach same trigger to users table (reuse the same function)
CREATE TRIGGER trg_users_updated_at
    BEFORE UPDATE ON users
    FOR EACH ROW
    EXECUTE FUNCTION update_updated_at_column();
    -- => One function, multiple triggers; DRY principle applied to trigger logic
 
-- rollback DROP TRIGGER IF EXISTS trg_users_updated_at ON users;
-- rollback DROP TRIGGER IF EXISTS trg_expenses_updated_at ON expenses;
-- rollback DROP FUNCTION IF EXISTS update_updated_at_column();
-- => Rollback order: drop triggers first, then drop the function they reference

-- liquibase formatted sql
 
-- changeset demo-be:031-create-archive-procedure dbms:postgresql
-- => Stored procedure for archiving soft-deleted expenses older than a retention period
-- => Runs as a database procedure, callable from application code or scheduled jobs
 
CREATE OR REPLACE PROCEDURE archive_deleted_expenses(
    retention_days INT DEFAULT 365
    -- => Parameter with default: archive expenses deleted more than N days ago
    -- => Default 365 enables: CALL archive_deleted_expenses(); (uses 365)
    -- => Override: CALL archive_deleted_expenses(180); (uses 180)
)
LANGUAGE plpgsql AS $$
DECLARE
    archived_count INT := 0;
    -- => DECLARE block: local variables for the procedure
    -- => archived_count tracks how many rows were archived for logging
BEGIN
    INSERT INTO expenses_archive
    -- => expenses_archive must exist (created by a preceding changeset)
    SELECT *
    FROM expenses
    WHERE deleted_at IS NOT NULL
      AND deleted_at < NOW() - (retention_days || ' days')::INTERVAL
    -- => Dynamic interval: concatenate retention_days with ' days' before casting
    -- => (365 || ' days')::INTERVAL equals INTERVAL '365 days'
    ON CONFLICT (id) DO NOTHING;
    -- => ON CONFLICT: skip already-archived rows (idempotent re-runs)
 
    GET DIAGNOSTICS archived_count = ROW_COUNT;
    -- => GET DIAGNOSTICS: capture row count from the most recent SQL statement
    -- => archived_count holds the number of rows inserted into expenses_archive
 
    DELETE FROM expenses
    WHERE deleted_at IS NOT NULL
      AND deleted_at < NOW() - (retention_days || ' days')::INTERVAL;
    -- => Delete from source table only after successful archive insert
    -- => Same WHERE condition ensures consistency between INSERT and DELETE
 
    RAISE NOTICE 'Archived % expenses older than % days', archived_count, retention_days;
    -- => RAISE NOTICE: logs to PostgreSQL notice channel; visible in application logs
    -- => Useful for monitoring procedure execution without a separate audit table
END;
$$;
 
-- rollback DROP PROCEDURE IF EXISTS archive_deleted_expenses(INT);
-- => Include parameter type signature to disambiguate overloaded procedures

# File: src/main/resources/db/changelog/changes/032-portable-changelog.yaml
 
databaseChangeLog:
  - changeSet:
      id: 032-create-expenses-postgresql
      author: demo-be
      dbms: postgresql
      # => Only executes against PostgreSQL databases
      # => Spring Boot test with H2: this changeset is skipped automatically
      changes:
        - sql:
            sql: |
              CREATE TABLE expenses_pg (
                id          UUID NOT NULL DEFAULT gen_random_uuid(),
                amount      NUMERIC(19,4) NOT NULL,
                created_at  TIMESTAMPTZ NOT NULL DEFAULT NOW()
              );
            -- => gen_random_uuid(): PostgreSQL 13+ built-in; requires pgcrypto on older versions
            -- => TIMESTAMPTZ: PostgreSQL-specific timezone-aware timestamp type
 
  - changeSet:
      id: 032-create-expenses-h2
      author: demo-be
      dbms: h2
      # => Only executes against H2 (in-memory test database)
      # => Uses H2-compatible types that differ from PostgreSQL
      changes:
        - sql:
            sql: |
              CREATE TABLE expenses_pg (
                id          UUID NOT NULL DEFAULT RANDOM_UUID(),
                amount      DECIMAL(19,4) NOT NULL,
                created_at  TIMESTAMP NOT NULL DEFAULT NOW()
              );
            -- => RANDOM_UUID(): H2 built-in; replaces PostgreSQL gen_random_uuid()
            -- => TIMESTAMP: H2 does not support TIMESTAMPTZ; uses TIMESTAMP instead
 
  - changeSet:
      id: 033-create-text-search-index
      author: demo-be
      dbms: postgresql
      # => GIN indexes and tsvector are PostgreSQL-specific; skip on H2/MySQL
      changes:
        - sql:
            sql: |
              CREATE INDEX idx_expenses_description_search
              ON expenses_pg
              USING GIN (to_tsvector('english', description));
            -- => GIN + tsvector: PostgreSQL full-text search index
            -- => No equivalent in H2 or MySQL; dbms:postgresql gates this safely

-- liquibase formatted sql
 
-- changeset demo-be:034-batch-migrate-currency-codes dbms:postgresql
-- => Backfill currency_code column in batches to avoid long-running transactions
-- => Processes 1000 rows per batch; safe for tables with millions of rows
 
DO $$
-- => DO block: anonymous PL/pgSQL block; executes immediately, not reusable
DECLARE
    batch_size  INT := 1000;
    -- => Process 1000 rows per iteration; adjust based on row size and lock tolerance
    processed   INT := 0;
    -- => Counter for total rows processed (used in RAISE NOTICE)
    batch_count INT;
    -- => Rows affected in the current batch iteration
BEGIN
    LOOP
        WITH batch AS (
            SELECT id
            FROM expenses
            WHERE currency_code IS NULL
            -- => Find rows not yet migrated; WHERE condition makes loop idempotent
            LIMIT batch_size
            -- => Process only batch_size rows per iteration
            FOR UPDATE SKIP LOCKED
            -- => FOR UPDATE: row-level lock to prevent concurrent modification
            -- => SKIP LOCKED: skip rows locked by other transactions (avoid deadlocks)
        )
        UPDATE expenses e
        SET currency_code = 'USD'
        -- => Default currency for legacy rows without a currency code
        FROM batch b
        WHERE e.id = b.id;
 
        GET DIAGNOSTICS batch_count = ROW_COUNT;
        -- => How many rows were updated in this batch iteration
        processed := processed + batch_count;
        -- => Accumulate total across all iterations
 
        EXIT WHEN batch_count = 0;
        -- => No more NULL rows: exit loop
        -- => Loop terminates naturally; no explicit counter needed
 
        RAISE NOTICE 'Migrated % rows (total: %)', batch_count, processed;
        -- => Progress logging; visible in PostgreSQL log and psql output
    END LOOP;
    RAISE NOTICE 'Batch migration complete. Total rows migrated: %', processed;
END;
$$;
 
-- rollback UPDATE expenses SET currency_code = NULL WHERE currency_code = 'USD';
-- => Rollback resets the backfilled column; safe only if 'USD' was exclusively a migration default

// File: src/test/java/com/example/migration/LiquibaseMigrationTest.java
 
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.test.context.ActiveProfiles;
import static org.assertj.core.api.Assertions.assertThat;
 
@SpringBootTest
// => Loads full Spring application context; runs Liquibase migrations automatically
// => All pending changesets execute before any test method
@ActiveProfiles("test")
// => Activates "test" profile; application-test.properties uses H2 in-memory database
// => H2 is dropped and recreated for each test class (or kept between tests per config)
class LiquibaseMigrationTest {
 
    @Autowired
    private JdbcTemplate jdbcTemplate;
    // => JdbcTemplate for direct SQL queries against the test database
    // => Injected by Spring; uses the same DataSource Liquibase migrated
 
    @Test
    void usersTableExists() {
        Integer count = jdbcTemplate.queryForObject(
            "SELECT COUNT(*) FROM information_schema.tables WHERE table_name = 'users'",
            // => Query information_schema to verify table existence
            // => Works on both PostgreSQL and H2 (both support information_schema)
            Integer.class
        );
        assertThat(count).isEqualTo(1);
        // => Assertion fails if the createTable changeset did not execute
        // => Catches missing changesets, wrong dbms filters, or precondition failures
    }
 
    @Test
    void rolesTableSeededCorrectly() {
        Integer roleCount = jdbcTemplate.queryForObject(
            "SELECT COUNT(*) FROM roles",
            // => Verify seed data was inserted by the seed changeset
            Integer.class
        );
        assertThat(roleCount).isGreaterThanOrEqualTo(3);
        // => At least 3 roles: ADMIN, USER, VIEWER
        // => Fails if the insert changeset did not execute or was skipped by dbms filter
    }
 
    @Test
    void emailColumnHasUniqueConstraint() {
        jdbcTemplate.update(
            "INSERT INTO users (id, username, password_hash, email) VALUES (gen_random_uuid(), 'test1', 'hash', 'test@example.com')"
        );
        // => First insert: succeeds (email is unique at this point)
        org.junit.jupiter.api.Assertions.assertThrows(
            org.springframework.dao.DataIntegrityViolationException.class,
            () -> jdbcTemplate.update(
                "INSERT INTO users (id, username, password_hash, email) VALUES (gen_random_uuid(), 'test2', 'hash', 'test@example.com')"
                // => Second insert with same email: should throw DataIntegrityViolationException
                // => Verifies the UNIQUE constraint from the addColumn changeset is active
            )
        );
    }
}

// File: src/test/java/com/example/migration/PostgreSQLMigrationTest.java
 
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.boot.test.util.TestPropertyValues;
import org.springframework.context.ApplicationContextInitializer;
import org.springframework.context.ConfigurableApplicationContext;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.test.context.ContextConfiguration;
import org.testcontainers.containers.PostgreSQLContainer;
import org.testcontainers.junit.jupiter.Container;
import org.testcontainers.junit.jupiter.Testcontainers;
import static org.assertj.core.api.Assertions.assertThat;
 
@SpringBootTest
@Testcontainers
// => @Testcontainers: manages lifecycle of @Container fields
// => Starts container before first test, stops after last test in class
@ContextConfiguration(initializers = PostgreSQLMigrationTest.Initializer.class)
// => Initializer sets Spring datasource properties to point to the container
class PostgreSQLMigrationTest {
 
    @Container
    static PostgreSQLContainer<?> postgres = new PostgreSQLContainer<>("postgres:16-alpine")
        // => Specify exact PostgreSQL version to match production
        // => "postgres:16-alpine" is smaller than "postgres:16"; faster pull
        .withDatabaseName("testdb")
        // => Database name for the test container
        .withUsername("test")
        .withPassword("test");
        // => Test-only credentials; never use production credentials in tests
 
    static class Initializer implements ApplicationContextInitializer<ConfigurableApplicationContext> {
        @Override
        public void initialize(ConfigurableApplicationContext ctx) {
            TestPropertyValues.of(
                "spring.datasource.url=" + postgres.getJdbcUrl(),
                // => Override datasource URL to point to the container
                "spring.datasource.username=" + postgres.getUsername(),
                "spring.datasource.password=" + postgres.getPassword()
                // => Liquibase reads these same properties; migrates the container DB
            ).applyTo(ctx.getEnvironment());
        }
    }
 
    @Autowired
    private JdbcTemplate jdbcTemplate;
 
    @Test
    void uuidGenerationWorksWithPostgreSQL() {
        String uuid = jdbcTemplate.queryForObject(
            "SELECT gen_random_uuid()::TEXT",
            // => gen_random_uuid() is PostgreSQL-specific; fails on H2 without extension
            // => Confirms the PostgreSQL changeset (dbms:postgresql) ran successfully
            String.class
        );
        assertThat(uuid).matches("[0-9a-f]{8}-[0-9a-f]{4}-4[0-9a-f]{3}-[89ab][0-9a-f]{3}-[0-9a-f]{12}");
        // => UUID v4 pattern: "4" in version position, "8/9/a/b" in variant position
    }
 
    @Test
    void fullTextSearchIndexExists() {
        Integer indexCount = jdbcTemplate.queryForObject(
            "SELECT COUNT(*) FROM pg_indexes WHERE indexname = 'idx_expenses_description_search'",
            // => pg_indexes: PostgreSQL-specific system view listing all indexes
            // => Verifies the GIN index from the dbms:postgresql changeset was created
            Integer.class
        );
        assertThat(indexCount).isEqualTo(1);
        // => Fails if the PostgreSQL-specific changeset was skipped due to wrong dbms filter
    }
}

-- liquibase formatted sql
 
-- changeset demo-be:035-add-metadata-column dbms:postgresql
-- => Add JSONB column for flexible, schema-less metadata storage
-- => JSONB (binary JSON) vs JSON: JSONB is indexed and queryable; JSON is stored as text
 
ALTER TABLE expenses ADD COLUMN metadata JSONB;
-- => metadata is nullable (no NOT NULL): existing rows get NULL metadata
-- => JSONB stores JSON with duplicate key removal and whitespace normalization
-- => JSON preserves exact text (including duplicates and whitespace)
 
-- Create a GIN index for efficient JSONB containment queries
CREATE INDEX idx_expenses_metadata ON expenses USING GIN (metadata);
-- => GIN index enables: WHERE metadata @> '{"category": "food"}'::JSONB
-- => @> is the "contains" operator: checks if left JSONB contains right JSONB
-- => Without GIN index: containment queries require sequential scan
 
-- rollback DROP INDEX IF EXISTS idx_expenses_metadata;
-- rollback ALTER TABLE expenses DROP COLUMN metadata;
-- => Drop index before column (explicit ordering avoids ambiguity)

// Application code: reading and writing JSONB via Spring Boot JdbcTemplate
// File: src/main/java/com/example/repository/ExpenseRepository.java
 
import com.fasterxml.jackson.databind.ObjectMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;
import java.util.Map;
 
@Repository
public class ExpenseRepository {
 
    private final JdbcTemplate jdbc;
    private final ObjectMapper mapper;
 
    public ExpenseRepository(JdbcTemplate jdbc, ObjectMapper mapper) {
        this.jdbc = jdbc;
        this.mapper = mapper;
        // => Both dependencies injected by Spring; mapper serializes Map to JSON string
    }
 
    public void updateMetadata(String expenseId, Map<String, Object> metadata) throws Exception {
        String json = mapper.writeValueAsString(metadata);
        // => Serialize Map to JSON string: {"receipt_url": "...", "tags": ["food", "lunch"]}
        jdbc.update(
            "UPDATE expenses SET metadata = ?::JSONB WHERE id = ?::UUID",
            // => ?::JSONB: cast the string parameter to JSONB type
            // => PostgreSQL requires explicit cast; Spring JDBC passes strings by default
            json, expenseId
        );
    }
 
    public void findByMetadataTag(String tag) {
        jdbc.query(
            "SELECT id FROM expenses WHERE metadata @> ?::JSONB",
            // => @> operator: containment check (does metadata contain this JSON fragment?)
            // => ?::JSONB: cast parameter to JSONB for operator compatibility
            rs -> { System.out.println(rs.getString("id")); },
            "{\"tags\": [\"" + tag + "\"]}"
            // => Containment query: metadata must contain {tags: [tag]}
            // => GIN index makes this query O(log n) instead of O(n)
        );
    }
}

-- liquibase formatted sql
 
-- changeset demo-be:036-add-tags-array dbms:postgresql
-- => Add text array column for multi-value tags without a separate tags table
-- => Arrays are PostgreSQL-specific; dbms:postgresql gates this changeset
 
ALTER TABLE expenses ADD COLUMN tags TEXT[] DEFAULT '{}';
-- => TEXT[]: one-dimensional array of text values
-- => DEFAULT '{}': empty array default for new rows (not NULL)
-- => Existing rows get '{}' (empty array) retroactively
 
-- Create GIN index for array containment queries
CREATE INDEX idx_expenses_tags ON expenses USING GIN (tags);
-- => GIN index supports array containment: WHERE tags @> ARRAY['food']
-- => Also supports: WHERE tags && ARRAY['food', 'dining'] (array overlaps)
-- => And: WHERE 'food' = ANY(tags) (element exists in array)
 
-- rollback DROP INDEX IF EXISTS idx_expenses_tags;
-- rollback ALTER TABLE expenses DROP COLUMN tags;

// Spring Boot: read and write PostgreSQL array columns via JDBC
import org.springframework.jdbc.core.JdbcTemplate;
import java.util.List;
 
public class ExpenseTagRepository {
 
    private final JdbcTemplate jdbc;
 
    public ExpenseTagRepository(JdbcTemplate jdbc) {
        this.jdbc = jdbc;
        // => JdbcTemplate injected; wraps JDBC operations with Spring exception translation
    }
 
    public void addTags(String expenseId, List<String> tags) {
        jdbc.update(
            "UPDATE expenses SET tags = tags || ?::TEXT[] WHERE id = ?::UUID",
            // => || operator: array concatenation (append tags to existing array)
            // => ?::TEXT[]: cast string parameter to PostgreSQL text array
            "{" + String.join(",", tags) + "}",
            // => PostgreSQL array literal format: {food,dining,lunch}
            // => No quotes around simple strings; quote strings containing commas or spaces
            expenseId
        );
    }
 
    public List<String> findExpensesWithTag(String tag) {
        return jdbc.query(
            "SELECT id::TEXT FROM expenses WHERE ?::TEXT = ANY(tags)",
            // => ANY(tags): true if the tag exists anywhere in the tags array
            // => Equivalent to WHERE tags @> ARRAY[?] but ANY is more readable for single values
            (rs, row) -> rs.getString("id"),
            tag
        );
    }
}

-- liquibase formatted sql
 
-- changeset demo-be:037-add-fts-column dbms:postgresql
-- => Add stored tsvector column for efficient full-text search
-- => Stored column avoids re-computing tsvector on every search query
 
ALTER TABLE expenses ADD COLUMN search_vector TSVECTOR;
-- => TSVECTOR: preprocessed document representation for full-text search
-- => Stores lexemes (normalized word forms) with position information
-- => 'Lunch at cafe' produces tsvector that stores 'cafe' and 'lunch'
 
-- Backfill the stored tsvector for existing rows
UPDATE expenses
SET search_vector = to_tsvector('english',
    COALESCE(description, '') || ' ' || COALESCE(category, '')
    -- => Concatenate description and category as the searchable text
    -- => COALESCE: handle NULL values by substituting empty string
    -- => 'english': use English stemming (lunch and lunching both match 'lunch')
);
-- => Backfill runs once; new rows require a trigger to maintain search_vector
 
-- Create GIN index on the stored tsvector column
CREATE INDEX idx_expenses_fts ON expenses USING GIN (search_vector);
-- => GIN index enables: WHERE search_vector @@ to_tsquery('english', 'lunch')
-- => @@ operator: full-text match (does document contain query terms?)
-- => Without GIN: full-text search requires sequential scan of all rows
 
-- Create trigger to maintain search_vector on INSERT and UPDATE
CREATE OR REPLACE FUNCTION update_expense_search_vector()
RETURNS TRIGGER AS $$
BEGIN
    NEW.search_vector = to_tsvector('english',
        COALESCE(NEW.description, '') || ' ' || COALESCE(NEW.category, '')
    );
    -- => Recompute tsvector whenever description or category changes
    RETURN NEW;
    -- => RETURN NEW: required for BEFORE triggers to proceed with the modified row
END;
$$ LANGUAGE plpgsql;
 
CREATE TRIGGER trg_expenses_fts
    BEFORE INSERT OR UPDATE ON expenses
    -- => Fires on both INSERT and UPDATE; maintains search_vector automatically
    FOR EACH ROW
    EXECUTE FUNCTION update_expense_search_vector();
 
-- rollback DROP TRIGGER IF EXISTS trg_expenses_fts ON expenses;
-- rollback DROP FUNCTION IF EXISTS update_expense_search_vector();
-- rollback DROP INDEX IF EXISTS idx_expenses_fts;
-- rollback ALTER TABLE expenses DROP COLUMN search_vector;

-- liquibase formatted sql
 
-- changeset demo-be:038-partition-expenses-by-date dbms:postgresql
-- => Range partitioning by expense date; each partition covers one year
-- => Partitioning improves query performance and enables efficient data archival
 
-- Create the partitioned parent table
CREATE TABLE expenses_partitioned (
    id          UUID          NOT NULL DEFAULT gen_random_uuid(),
    user_id     UUID          NOT NULL,
    amount      NUMERIC(19,4) NOT NULL,
    date        DATE          NOT NULL,
    -- => date is the partition key; must be included in PRIMARY KEY for partitioned tables
    description VARCHAR(500)  NOT NULL,
    created_at  TIMESTAMPTZ   NOT NULL DEFAULT NOW(),
    CONSTRAINT pk_expenses_partitioned PRIMARY KEY (id, date)
    -- => Composite PRIMARY KEY including partition key (date): PostgreSQL requirement
    -- => Simple PRIMARY KEY (id) is not allowed on range-partitioned tables
) PARTITION BY RANGE (date);
-- => PARTITION BY RANGE: partition based on date ranges
-- => Parent table is empty; all data lives in child partitions
 
-- Create year-specific partitions
CREATE TABLE expenses_2024 PARTITION OF expenses_partitioned
    FOR VALUES FROM ('2024-01-01') TO ('2025-01-01');
-- => Partition covers: 2024-01-01 <= date < 2025-01-01
-- => TO value is exclusive: '2025-01-01' belongs to the next partition
 
CREATE TABLE expenses_2025 PARTITION OF expenses_partitioned
    FOR VALUES FROM ('2025-01-01') TO ('2026-01-01');
-- => 2025 partition; add new year partitions via future changesets
 
CREATE TABLE expenses_2026 PARTITION OF expenses_partitioned
    FOR VALUES FROM ('2026-01-01') TO ('2027-01-01');
-- => 2026 partition; add before January 2026 to avoid insertion errors
 
-- Create a default partition for out-of-range dates
CREATE TABLE expenses_default PARTITION OF expenses_partitioned DEFAULT;
-- => DEFAULT partition catches rows that do not match any range partition
-- => Without DEFAULT: INSERT of a date outside defined ranges raises an error
-- => Monitor expenses_default size; large growth indicates missing partitions
 
-- rollback DROP TABLE IF EXISTS expenses_partitioned CASCADE;
-- => CASCADE drops all partitions automatically (they are children of the parent)

# File: src/main/resources/db/changelog/changes/039-composite-pk.yaml
 
databaseChangeLog:
  - changeSet:
      id: 039-create-user-roles-junction
      author: demo-be
      changes:
        - createTable:
            tableName: user_roles
            # => Junction table for many-to-many: one user can have multiple roles
            columns:
              - column:
                  name: user_id
                  type: UUID
                  # => Foreign key referencing users.id
                  constraints:
                    nullable: false
                    primaryKey: true
                    # => Part of composite primary key
                    primaryKeyName: pk_user_roles
                    # => Named primary key constraint for clarity
              - column:
                  name: role_id
                  type: INT
                  # => Foreign key referencing roles.id
                  constraints:
                    nullable: false
                    primaryKey: true
                    # => Second part of composite primary key
                    primaryKeyName: pk_user_roles
                    # => Same primaryKeyName: Liquibase combines into one composite PK
              - column:
                  name: granted_at
                  type: TIMESTAMPTZ
                  defaultValueComputed: NOW()
                  # => When the role was assigned; part of the row but not the PK
                  constraints:
                    nullable: false
              - column:
                  name: granted_by
                  type: VARCHAR(255)
                  # => Who assigned the role; audit trail for permission changes
                  constraints:
                    nullable: false
 
  - changeSet:
      id: 040-add-user-roles-foreign-keys
      author: demo-be
      changes:
        - addForeignKeyConstraint:
            baseTableName: user_roles
            baseColumnNames: user_id
            # => The FK column in user_roles
            referencedTableName: users
            referencedColumnNames: id
            # => References users.id (the primary key)
            constraintName: fk_user_roles_user
            # => Named constraint for clear error messages and targeted DROP
            onDelete: CASCADE
            # => CASCADE: deleting a user deletes their role assignments
        - addForeignKeyConstraint:
            baseTableName: user_roles
            baseColumnNames: role_id
            referencedTableName: roles
            referencedColumnNames: id
            constraintName: fk_user_roles_role
            onDelete: CASCADE
            # => CASCADE: deleting a role removes all assignments to that role

# Step 1: Validate changelog syntax and checksums (no database connection needed for syntax)
liquibase validate
# => Checks: XML/YAML syntax, required attributes, known change type names
# => Detects: modified executed changesets (checksum mismatch) if --url is provided
# => Exit code 0: changelog is valid; exit code non-zero: validation failed
# => Run in CI before any database operations to catch syntax errors early
 
# Step 2: Generate preview SQL without executing (dry run)
liquibase updateSQL > pending-migrations.sql
# => Generates the SQL that would be executed by liquibase update
# => Output is a complete SQL file: DDL, DML, DATABASECHANGELOG inserts
# => Review pending-migrations.sql in pull requests for peer review
# => No database changes occur; safe to run against production connection string
 
# Step 3: Check how many changesets are pending
liquibase status --verbose
# => Lists each changeset not yet in DATABASECHANGELOG with its SQL preview
# => --verbose: shows the SQL for each pending changeset
# => Use in deployment scripts to detect "nothing to migrate" scenarios
 
# Step 4: Tag the current state before a risky migration
liquibase tag pre-v2.1.0
# => Creates a tag in DATABASECHANGELOG marking the current state
# => Rollback to tag: liquibase rollback pre-v2.1.0
# => Rolls back all changesets applied AFTER the tag was created
# => Essential before multi-changeset deployments that require atomic rollback

# File: src/test/resources/application-test.yaml
 
spring:
  datasource:
    url: jdbc:h2:mem:testdb;MODE=PostgreSQL;DB_CLOSE_DELAY=-1
    # => H2 in PostgreSQL compatibility mode: supports some PostgreSQL-specific syntax
    # => DB_CLOSE_DELAY=-1: keep database alive for the JVM lifetime
    driver-class-name: org.h2.Driver
    username: sa
    password: ""
  liquibase:
    enabled: true
    # => Run Liquibase migrations in test mode
    contexts: test
    # => Activate "test" context: runs changesets tagged context:test
    # => Suppresses context:prod changesets (seed data, prod config)
    change-log: classpath:db/changelog/db.changelog-master.yaml
    # => Same master changelog as production; dbms filters handle H2 vs PostgreSQL differences