Beginner

(require '[migratus.core :as migratus])  ; => Import migratus.core namespace
 
(def config
  {:store         :database              ; => Use the SQL database store
                                         ; => Other stores exist (:fs) but :database is standard
   :migration-dir "migrations"           ; => Path relative to classpath (resources/migrations/)
   :db            {:connection-uri       ; => Database connection map (next.jdbc format)
                   "jdbc:postgresql://localhost:5432/mydb?user=admin&password=secret"}})
                                         ; => config is a plain Clojure map; no object creation
                                         ; => Passed as first arg to every migratus/* function
 
(migratus/migrate config)                ; => Runs all pending SQL migrations
                                         ; => Reads *.up.sql files from resources/migrations/

-- => Creates the users table (applied when migrating up)
CREATE TABLE IF NOT EXISTS users (
  id      TEXT PRIMARY KEY,            -- => Primary key; TEXT for UUIDs
  username TEXT NOT NULL UNIQUE,       -- => Unique login handle; NOT NULL enforced
  email    TEXT NOT NULL UNIQUE,       -- => Unique contact address
  created_at TEXT NOT NULL             -- => ISO-8601 timestamp stored as text
);
-- => Migratus executes this file in a transaction by default

-- => Drops the users table (applied when rolling back)
DROP TABLE IF EXISTS users;
-- => IF EXISTS prevents error if table was already removed manually
-- => Always mirror every up.sql change with a matching down.sql

;; Migration file naming rules:
;; Format: {id}-{description}.{direction}.sql
;;
;; {id}          - Unique positive integer (determines execution order)
;;               ; => Migratus sorts migrations by integer value, not lexicographically
;; {description} - Kebab-case description of the change
;;               ; => Used only for human readability; not parsed by Migratus
;; {direction}   - "up" to apply, "down" to revert
;;               ; => Must exactly match these strings
 
;; Valid examples:
;; 001-create-users.up.sql          ; => First migration
;; 002-create-orders.up.sql         ; => Second migration
;; 010-add-email-index.up.sql       ; => Tenth migration (gaps allowed)
;; 20241201120000-add-column.up.sql ; => Timestamp-based IDs also valid
 
;; Invalid examples (will not be loaded by Migratus):
;; create-users.up.sql              ; => Missing numeric prefix
;; 001-create-users.sql             ; => Missing direction
;; 001_create_users.up.sql          ; => Underscore separator (must be hyphen after id)
 
;; Check what Migratus sees in the migrations directory:
(require '[migratus.core :as migratus])
 
(def config {:store         :database
             :migration-dir "migrations"
             :db            {:connection-uri "jdbc:postgresql://localhost:5432/mydb"}})
 
(migratus/pending-list config)
;; => Returns list of migration IDs not yet applied
;; => Example: [1 2 10] (the integer prefixes)

(require '[migratus.core :as migratus])
 
;; :store :database is the SQL database store
(def config
  {:store         :database            ; => Required; tells Migratus which store implementation to use
                                       ; => :database uses next.jdbc to run SQL and track state
   :migration-dir "migrations"         ; => Classpath-relative directory containing .sql files
   :db            {:connection-uri
                   "jdbc:postgresql://localhost:5432/mydb?user=app&password=secret"}})
                                       ; => :db map is passed directly to next.jdbc/get-datasource
 
;; The :database store automatically creates a tracking table:
;; CREATE TABLE IF NOT EXISTS schema_migrations (
;;   id BIGINT UNIQUE NOT NULL,         ; => The numeric migration ID
;;   applied TIMESTAMP NOT NULL         ; => When the migration ran
;; );
;; => Migratus creates this table on first use
;; => You do NOT need to create it manually
 
(migratus/migrate config)              ; => Applies all pending migrations
                                       ; => Reads schema_migrations to determine which IDs ran
                                       ; => Executes each pending *.up.sql in ascending ID order

(require '[migratus.core :as migratus])
 
;; Project structure (standard Clojure layout):
;; my-project/
;; ├── deps.edn               ; => :paths ["src" "resources"]
;; ├── resources/
;; │   └── migrations/        ; => Migration files live here
;; │       ├── 001-create-users.up.sql
;; │       └── 001-create-users.down.sql
;; └── src/
 
(def config
  {:store         :database
   :migration-dir "migrations"         ; => Classpath-relative; resolves to resources/migrations/
                                       ; => Do NOT use absolute paths or "resources/migrations"
                                       ; => "resources/" is already the classpath root
   :db            {:connection-uri "jdbc:postgresql://localhost:5432/mydb"}})
 
;; If resources/ is on the classpath (in deps.edn :paths):
;; "migrations" => resources/migrations/   OK
;; "db/migrations" => resources/db/migrations/  also valid if directory exists
 
;; Verify migration files are on classpath:
(clojure.java.io/resource "migrations/001-create-users.up.sql")
;; => Returns java.net.URL if file exists on classpath
;; => Returns nil if path is wrong (common setup mistake)

(require '[migratus.core :as migratus])
 
(def config
  {:store         :database
   :migration-dir "migrations"
   :db            {:connection-uri "jdbc:postgresql://localhost:5432/mydb?user=app&password=secret"}})
 
;; Run all pending migrations:
(migratus/migrate config)
;; => Queries SELECT id FROM schema_migrations to find applied IDs
;; => Finds all *.up.sql files in resources/migrations/
;; => Computes set difference: file IDs - applied IDs = pending IDs
;; => Executes each pending *.up.sql in ascending numeric ID order
;; => Inserts each completed ID into schema_migrations
;; => Returns nil on success
;; => Throws exception on SQL error (transaction rolls back for that migration)
 
;; Call at application startup (from -main or component start):
(defn -main [& _args]
  (migratus/migrate config)            ; => Schema up-to-date before server starts
  (start-server!))                     ; => Server starts with consistent schema

(require '[migratus.core :as migratus])
 
(def config
  {:store         :database
   :migration-dir "migrations"
   :db            {:connection-uri "jdbc:postgresql://localhost:5432/mydb?user=app&password=secret"}})
 
;; Apply migration ID 3 only:
(migratus/up config 3)
;; => Looks for resources/migrations/003-*.up.sql
;; => Executes the SQL in that file
;; => Inserts ID 3 into schema_migrations
;; => Does NOT run migrations 1 or 2 if they are pending
;; => Returns nil on success
 
;; Apply multiple specific IDs:
(migratus/up config 5 6)               ; => Runs migration 5 then migration 6
                                       ; => Useful when testing specific migration pairs
                                       ; => IDs are applied in the order you pass them
 
;; If migration already applied, migratus/up is a no-op:
(migratus/up config 3)                 ; => ID 3 already in schema_migrations
                                       ; => No SQL executed; returns nil silently

(require '[migratus.core :as migratus])
 
(def config
  {:store         :database
   :migration-dir "migrations"
   :db            {:connection-uri "jdbc:postgresql://localhost:5432/mydb?user=app&password=secret"}})
 
;; Roll back migration ID 3:
(migratus/down config 3)
;; => Looks for resources/migrations/003-*.down.sql
;; => Executes the SQL in that file (e.g., DROP TABLE)
;; => Removes ID 3 from schema_migrations
;; => Returns nil on success
 
;; Roll back multiple IDs (in order given):
(migratus/down config 6 5)             ; => Reverts migration 6, then migration 5
                                       ; => Note: typically roll back in reverse order
                                       ; => to respect foreign key dependencies
 
;; After rollback, migration is pending again:
(migratus/pending-list config)         ; => [3] — ID 3 is pending again after down
                                       ; => Running migrate again will re-apply it

(require '[migratus.core :as migratus])
 
(def config
  {:store         :database
   :migration-dir "migrations"
   :db            {:connection-uri "jdbc:postgresql://localhost:5432/mydb?user=app&password=secret"}})
 
;; Create a new migration named "add-email-index":
(migratus/create config "add-email-index")
;; => Generates two empty files in resources/migrations/:
;; => 20241201143022-add-email-index.up.sql   (timestamp as ID)
;; => 20241201143022-add-email-index.down.sql
;; => Files are empty; you fill in the SQL manually
;; => Returns nil
 
;; The generated files are empty stubs:
;; 20241201143022-add-email-index.up.sql:
;;   (empty — add your CREATE INDEX statement here)
;;
;; 20241201143022-add-email-index.down.sql:
;;   (empty — add your DROP INDEX statement here)
 
;; After creating, edit both files before running migrations:
;; up.sql:   CREATE INDEX idx_users_email ON users (email);
;; down.sql: DROP INDEX IF EXISTS idx_users_email;

(require '[migratus.core :as migratus])
 
(def config
  {:store         :database
   :migration-dir "migrations"
   :db            {:connection-uri "jdbc:postgresql://localhost:5432/mydb?user=app&password=secret"}})
 
;; Inspect pending migrations:
(migratus/pending-list config)
;; => Queries schema_migrations for applied IDs
;; => Reads *.up.sql filenames from resources/migrations/
;; => Returns vector of numeric IDs not in schema_migrations
;; => Example: [3 5 6]  (IDs 1,2,4 already applied; 3,5,6 pending)
;; => Returns [] when all migrations are applied
 
;; Use in a health check or pre-deployment gate:
(defn migrations-up-to-date? [config]
  (empty? (migratus/pending-list config))) ; => true when nothing pending
                                           ; => false when schema lags behind files
 
(if (migrations-up-to-date? config)
  (println "Schema is current")           ; => Output when all applied
  (println "WARNING: pending migrations")) ; => Output when behind

-- File: resources/migrations/001-create-users.up.sql
-- => Creates the users table with common column patterns
 
CREATE TABLE IF NOT EXISTS users (
  id            TEXT PRIMARY KEY,        -- => UUID stored as text; application generates the value
  username      TEXT NOT NULL UNIQUE,    -- => Login handle; UNIQUE enforces no duplicates at DB level
  email         TEXT NOT NULL UNIQUE,    -- => Contact address; UNIQUE index created automatically
  password_hash TEXT NOT NULL,           -- => Bcrypt or Argon2 hash; never store plaintext
  display_name  TEXT NOT NULL DEFAULT '',-- => Optional display name; empty string default
  role          TEXT NOT NULL DEFAULT 'USER', -- => Enum-like role; enforced by CHECK (see Example 22)
  created_at    TEXT NOT NULL,           -- => ISO-8601 timestamp; application sets this
  updated_at    TEXT NOT NULL            -- => Last modification timestamp
);
-- => IF NOT EXISTS prevents error if migration is re-run manually
-- => Migratus wraps this in a transaction; error causes rollback

-- File: resources/migrations/001-create-users.down.sql
-- => Drops the users table, reversing the up migration
 
DROP TABLE IF EXISTS users;
-- => IF EXISTS prevents error if table was removed manually before rollback
-- => All rows, indexes, and constraints are dropped with the table

-- File: resources/migrations/006-add-users-status.up.sql
-- => Adds status column to existing users table
 
ALTER TABLE users
  ADD COLUMN status TEXT NOT NULL DEFAULT 'ACTIVE';
-- => ADD COLUMN appends the new column to the table
-- => DEFAULT 'ACTIVE' back-fills existing rows immediately
-- => NOT NULL is safe here because DEFAULT provides a value for existing rows
-- => Without DEFAULT, NOT NULL ADD COLUMN fails if table has existing rows
 
-- Add a second column in the same migration:
ALTER TABLE users
  ADD COLUMN failed_login_attempts INTEGER NOT NULL DEFAULT 0;
-- => Integer counter; default 0 for all existing users
-- => Multiple ALTER TABLE statements in one up.sql file are allowed

-- File: resources/migrations/006-add-users-status.down.sql
-- => Removes columns added in up migration (in reverse order)
 
ALTER TABLE users DROP COLUMN IF EXISTS failed_login_attempts;
-- => Remove second column first (reverse of up order)
ALTER TABLE users DROP COLUMN IF EXISTS status;
-- => Remove first column last
-- => IF EXISTS prevents error if column was already removed manually

-- File: resources/migrations/007-add-users-indexes.up.sql
-- => Adds query performance indexes to the users table
 
-- Standard index for frequent lookup by email:
CREATE INDEX IF NOT EXISTS idx_users_email
  ON users (email);
-- => Speeds up WHERE email = '...' queries
-- => IF NOT EXISTS prevents error on re-run
-- => Index name convention: idx_{table}_{column(s)}
 
-- Partial index for active users only:
CREATE INDEX IF NOT EXISTS idx_users_active
  ON users (username)
  WHERE status = 'ACTIVE';
-- => Only indexes rows where status = 'ACTIVE'
-- => Smaller index; faster for queries that always filter by status
-- => WHERE clause mirrors common application query pattern
 
-- Composite index for multi-column lookups:
CREATE INDEX IF NOT EXISTS idx_users_role_status
  ON users (role, status);
-- => Covers queries filtering on both role AND status
-- => Column order matters: role is the leading column
-- => Query must include role to use this index efficiently

-- File: resources/migrations/007-add-users-indexes.down.sql
-- => Drops indexes added in the up migration
 
DROP INDEX IF EXISTS idx_users_role_status;
DROP INDEX IF EXISTS idx_users_active;
DROP INDEX IF EXISTS idx_users_email;
-- => Indexes are dropped in reverse creation order (good practice)
-- => IF EXISTS prevents errors if already removed

-- File: resources/migrations/008-add-expenses-user-fk.up.sql
-- => Adds foreign key relationship between expenses and users
 
-- First ensure the expenses table exists (created in an earlier migration):
ALTER TABLE expenses
  ADD COLUMN IF NOT EXISTS user_id TEXT NOT NULL DEFAULT '';
-- => Add column first if it does not exist
-- => DEFAULT '' allows back-fill; will be updated to proper values
 
-- Then add the foreign key constraint:
ALTER TABLE expenses
  ADD CONSTRAINT fk_expenses_user
  FOREIGN KEY (user_id)
  REFERENCES users (id)
  ON DELETE CASCADE;
-- => FOREIGN KEY (user_id) references the id column of users table
-- => ON DELETE CASCADE: deleting a user auto-deletes their expenses
-- => Constraint name fk_{child_table}_{parent_table} is a clear convention
-- => PostgreSQL creates an index on user_id automatically for FK performance

-- File: resources/migrations/008-add-expenses-user-fk.down.sql
-- => Removes the foreign key constraint (column remains)
 
ALTER TABLE expenses
  DROP CONSTRAINT IF EXISTS fk_expenses_user;
-- => Removes only the constraint, not the column
-- => IF EXISTS prevents error if constraint was already removed
-- => Data in user_id column is preserved

-- File: resources/migrations/009-add-unique-constraints.up.sql
-- => Adds unique constraints to enforce data integrity rules
 
-- Single-column unique constraint:
ALTER TABLE users
  ADD CONSTRAINT uq_users_username UNIQUE (username);
-- => Prevents two users from having the same username
-- => Constraint name uq_{table}_{column} is self-documenting
-- => PostgreSQL automatically creates a unique index to enforce this
-- => Fails if duplicate usernames already exist in the table
 
-- Composite unique constraint (multi-column):
ALTER TABLE refresh_tokens
  ADD CONSTRAINT uq_refresh_tokens_user_hash
  UNIQUE (user_id, token_hash);
-- => Prevents the same user from having duplicate token hashes
-- => Both columns together must be unique; either alone may repeat
-- => Useful for junction tables and token management

-- File: resources/migrations/009-add-unique-constraints.down.sql
-- => Removes unique constraints (preserves data)
 
ALTER TABLE refresh_tokens
  DROP CONSTRAINT IF EXISTS uq_refresh_tokens_user_hash;
-- => Removes composite unique constraint
 
ALTER TABLE users
  DROP CONSTRAINT IF EXISTS uq_users_username;
-- => Removes single-column unique constraint
-- => Drops the underlying unique index automatically

-- The schema_migrations table is created automatically by Migratus on first use.
-- Its structure (PostgreSQL dialect):
 
-- => Migratus creates this table if it does not exist:
-- CREATE TABLE IF NOT EXISTS schema_migrations (
--   id      BIGINT UNIQUE NOT NULL,   -- => The numeric migration ID (e.g., 1, 2, 20241201143022)
--   applied TIMESTAMP NOT NULL        -- => UTC timestamp when migration was applied
-- );
 
-- Query applied migrations manually:
-- SELECT id, applied FROM schema_migrations ORDER BY id;
-- => id=1, applied=2024-12-01 10:00:00
-- => id=2, applied=2024-12-01 10:00:01
-- => id=3, applied=2024-12-01 10:00:02
 
-- Query in Clojure to see applied state:
(require '[next.jdbc :as jdbc]
         '[migratus.core :as migratus])
 
(def ds (jdbc/get-datasource
          {:connection-uri "jdbc:postgresql://localhost:5432/mydb?user=app&password=secret"}))
                                                ; => Creates next.jdbc datasource
 
(jdbc/execute! ds ["SELECT id, applied FROM schema_migrations ORDER BY id"])
;; => [{:schema_migrations/id 1 :schema_migrations/applied #inst "2024-12-01T10:00:00"}
;;     {:schema_migrations/id 2 :schema_migrations/applied #inst "2024-12-01T10:00:01"}]
;; => Each map represents one applied migration row

(require '[migratus.core :as migratus])
 
(defn build-migratus-config
  "Constructs Migratus config from environment variables.
   Credentials are query parameters because next.jdbc reads them from the URI."
  []
  (let [host     (or (System/getenv "DB_HOST")     "localhost") ; => DB host; default localhost
        port     (or (System/getenv "DB_PORT")     "5432")      ; => DB port; default PostgreSQL port
        db-name  (or (System/getenv "DB_NAME")     "mydb")      ; => Database name
        user     (or (System/getenv "DB_USER")     "app")       ; => DB username
        password (or (System/getenv "DB_PASSWORD") "secret")    ; => DB password
        base-uri (str "jdbc:postgresql://" host ":" port "/" db-name)
                                                                  ; => Constructs base JDBC URI
        full-uri (str base-uri "?user=" user "&password=" password)]
                                                                  ; => Appends credentials as query params
    {:store         :database
     :migration-dir "migrations"
     :db            {:connection-uri full-uri}}))
                                                                  ; => Returns complete Migratus config
 
;; Usage:
(def config (build-migratus-config))   ; => Reads from environment at call time
(migratus/migrate config)              ; => Runs migrations with environment-sourced credentials

-- File: resources/migrations/010-add-display-name.up.sql
-- => Adds display_name column with NOT NULL constraint safely
 
-- CORRECT: NOT NULL with DEFAULT (works on tables with existing rows)
ALTER TABLE users
  ADD COLUMN display_name TEXT NOT NULL DEFAULT '';
-- => DEFAULT '' satisfies NOT NULL for all existing rows immediately
-- => New rows without explicit display_name also get empty string
-- => Two-phase approach: add with default, remove default later if desired
 
-- WRONG (do not do this on a populated table):
-- ALTER TABLE users ADD COLUMN display_name TEXT NOT NULL;
-- => ERROR: column "display_name" of relation "users" contains null values
-- => Fails because existing rows get NULL, violating NOT NULL constraint
 
-- Optional: Remove the default after backfilling real values
-- ALTER TABLE users ALTER COLUMN display_name DROP DEFAULT;
-- => Run this in a LATER migration after application populates all rows
-- => Removing default in same migration as adding column is safe only for empty tables

-- File: resources/migrations/010-add-display-name.down.sql
-- => Removes display_name column
 
ALTER TABLE users DROP COLUMN IF EXISTS display_name;
-- => Drops column and any associated default constraint

-- File: resources/migrations/011-create-orders-uuid.up.sql
-- => Creates a table using UUID primary keys generated by PostgreSQL
 
-- Option A: Use gen_random_uuid() (PostgreSQL 13+, no extension needed)
CREATE TABLE IF NOT EXISTS orders (
  id          UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  -- => UUID type; gen_random_uuid() generates v4 UUID automatically
  -- => Application can omit id; database generates it
  user_id     UUID NOT NULL,             -- => FK to users.id (also UUID)
  total       NUMERIC(12,2) NOT NULL,    -- => Monetary amount; 12 digits, 2 decimal places
  created_at  TIMESTAMPTZ NOT NULL DEFAULT NOW()
  -- => Timestamp with timezone; NOW() defaults to current time
);
 
-- Option B: TEXT primary key (Clojure generates UUID string in application)
-- CREATE TABLE IF NOT EXISTS orders (
--   id         TEXT PRIMARY KEY,        -- => Application sets UUID via (str (random-uuid))
--   ...
-- );
-- => TEXT approach avoids PostgreSQL UUID type; portable across databases
-- => Used in demo-be-clojure-pedestal where Clojure generates UUIDs

-- File: resources/migrations/011-create-orders-uuid.down.sql
DROP TABLE IF EXISTS orders;

-- File: resources/migrations/012-add-timestamps.up.sql
-- => Adds standard audit timestamp columns to an existing table
 
ALTER TABLE expenses
  ADD COLUMN IF NOT EXISTS created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  -- => TIMESTAMPTZ stores UTC time with timezone offset
  -- => DEFAULT NOW() auto-fills on INSERT when application omits the column
  -- => TIMESTAMPTZ preferred over TIMESTAMP for multi-timezone applications
  ADD COLUMN IF NOT EXISTS updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW();
  -- => Applications must explicitly update this on UPDATE operations
  -- => PostgreSQL does NOT auto-update updated_at (unlike MySQL's ON UPDATE)
  -- => Use a trigger or application logic to keep updated_at current
 
-- Note: Multiple ADD COLUMN clauses in one ALTER TABLE statement are atomic
-- => Both columns added in a single DDL transaction

-- File: resources/migrations/012-add-timestamps.down.sql
ALTER TABLE expenses
  DROP COLUMN IF EXISTS updated_at,
  DROP COLUMN IF EXISTS created_at;
-- => Multiple DROP COLUMN in one statement; atomic operation

-- File: resources/migrations/013-create-status-enum.up.sql
-- => Demonstrates enum-like columns via CHECK constraint (preferred) vs native ENUM
 
-- Approach A: TEXT with CHECK constraint (migration-friendly)
ALTER TABLE users
  ADD COLUMN IF NOT EXISTS account_status TEXT NOT NULL DEFAULT 'ACTIVE'
    CHECK (account_status IN ('ACTIVE', 'SUSPENDED', 'DELETED'));
-- => CHECK constraint acts as an enum at the database level
-- => Adding new values = ALTER TABLE users ADD CHECK (... IN (..., 'NEW_VALUE'))
-- => No type to drop; works across all SQL databases
 
-- Approach B: Native PostgreSQL ENUM type (avoid in migrated schemas)
-- CREATE TYPE user_status AS ENUM ('ACTIVE', 'SUSPENDED', 'DELETED');
-- ALTER TABLE users ADD COLUMN status user_status NOT NULL DEFAULT 'ACTIVE';
-- => Harder to evolve: ALTER TYPE user_status ADD VALUE 'PENDING' requires careful ordering
-- => Cannot remove values from a ENUM without complex workarounds
-- => Native ENUM is used in demo-be-clojure-pedestal as TEXT for portability

-- File: resources/migrations/013-create-status-enum.down.sql
ALTER TABLE users DROP COLUMN IF EXISTS account_status;
-- => Drops column and its CHECK constraint together

-- File: resources/migrations/014-add-check-constraints.up.sql
-- => Adds CHECK constraints to enforce business rules
 
-- Positive amount constraint:
ALTER TABLE expenses
  ADD CONSTRAINT chk_expenses_amount_positive
    CHECK (amount::NUMERIC > 0);
-- => Prevents zero or negative expense amounts
-- => amount::NUMERIC casts TEXT to NUMERIC for comparison
-- => Constraint name chk_{table}_{rule} is self-documenting
 
-- Valid currency code constraint:
ALTER TABLE expenses
  ADD CONSTRAINT chk_expenses_currency_valid
    CHECK (currency IN ('USD', 'EUR', 'GBP', 'IDR', 'SGD'));
-- => Restricts currency to known codes; mirrors application-level validation
-- => Database is the last line of defense; never rely solely on app validation
 
-- Non-empty description with length limit:
ALTER TABLE users
  ADD CONSTRAINT chk_users_username_length
    CHECK (length(username) >= 3 AND length(username) <= 50);
-- => Prevents empty usernames and excessively long ones
-- => length() is a standard SQL function available in all major databases

-- File: resources/migrations/014-add-check-constraints.down.sql
ALTER TABLE users    DROP CONSTRAINT IF EXISTS chk_users_username_length;
ALTER TABLE expenses DROP CONSTRAINT IF EXISTS chk_expenses_currency_valid;
ALTER TABLE expenses DROP CONSTRAINT IF EXISTS chk_expenses_amount_positive;
-- => Dropped in reverse order as a convention (mirrors up migration)

-- File: resources/migrations/015-add-composite-indexes.up.sql
-- => Adds composite indexes optimised for common query patterns
 
-- Composite index for filtering and sorting expenses by user and date:
CREATE INDEX IF NOT EXISTS idx_expenses_user_date
  ON expenses (user_id, date DESC);
-- => Covers: WHERE user_id = ? ORDER BY date DESC
-- => Leading column user_id filters first (most selective)
-- => DESC on date matches common "most recent first" sort
-- => Index scan is efficient for paginated expense lists per user
 
-- Composite index for token lookup by user and revocation status:
CREATE INDEX IF NOT EXISTS idx_refresh_tokens_user_revoked
  ON refresh_tokens (user_id, revoked);
-- => Covers: WHERE user_id = ? AND revoked = 0
-- => Filters active tokens for a specific user efficiently
-- => Both columns have low cardinality individually; composite gives selectivity
 
-- Covering index (includes extra column to avoid table lookup):
CREATE INDEX IF NOT EXISTS idx_users_email_covering
  ON users (email)
  INCLUDE (id, display_name);
-- => Index-only scan for: SELECT id, display_name FROM users WHERE email = ?
-- => INCLUDE columns are stored in the index leaf pages
-- => Avoids heap table access for these specific queries

-- File: resources/migrations/015-add-composite-indexes.down.sql
DROP INDEX IF EXISTS idx_users_email_covering;
DROP INDEX IF EXISTS idx_refresh_tokens_user_revoked;
DROP INDEX IF EXISTS idx_expenses_user_date;

-- File: resources/migrations/016-create-user-roles.up.sql
-- => Creates junction table for many-to-many user-role relationship
 
CREATE TABLE IF NOT EXISTS user_roles (
  user_id  TEXT NOT NULL,               -- => FK to users.id
  role_id  TEXT NOT NULL,               -- => FK to roles.id (roles table created separately)
  granted_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  -- => When this role was granted; audit trail
  granted_by TEXT,                      -- => Who granted the role; nullable (system grants)
 
  PRIMARY KEY (user_id, role_id),       -- => Composite PK prevents duplicate grants
                                        -- => Same as UNIQUE(user_id, role_id) but also defines PK
  CONSTRAINT fk_user_roles_user
    FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE CASCADE,
  -- => Deleting a user removes all their role grants
  CONSTRAINT fk_user_roles_role
    FOREIGN KEY (role_id) REFERENCES roles (id) ON DELETE CASCADE
  -- => Deleting a role removes all user grants for that role
);
 
-- Index on role_id for reverse lookup (find all users with a role):
CREATE INDEX IF NOT EXISTS idx_user_roles_role
  ON user_roles (role_id);
-- => Covers: SELECT user_id FROM user_roles WHERE role_id = ?
-- => user_id index covered by PRIMARY KEY; role_id needs explicit index

-- File: resources/migrations/016-create-user-roles.down.sql
DROP TABLE IF EXISTS user_roles;
-- => Cascade constraints dropped with table

-- File: resources/migrations/017-seed-roles.up.sql
-- => Inserts initial role data required by the application
 
INSERT INTO roles (id, name, description, created_at) VALUES
  ('role-admin',   'ADMIN',   'Full system access',      NOW()),
  -- => Admin role; id is application-generated UUID string
  ('role-manager', 'MANAGER', 'Team management access',  NOW()),
  -- => Manager role; second row in the same INSERT
  ('role-user',    'USER',    'Standard user access',    NOW())
  -- => Default user role; third row
ON CONFLICT (id) DO NOTHING;
-- => ON CONFLICT DO NOTHING makes the INSERT idempotent
-- => If roles already exist (e.g., migration re-run), skip silently
-- => Without this, re-running the migration causes PRIMARY KEY violation errors

-- File: resources/migrations/017-seed-roles.down.sql
-- => Removes seeded roles (only if no user_roles references them)
 
DELETE FROM roles WHERE id IN ('role-admin', 'role-manager', 'role-user');
-- => Explicit ID list ensures only seeded rows are deleted
-- => Will fail with FK violation if user_roles still references these IDs
-- => Consider disabling FK checks or clearing user_roles first in a real rollback

-- File: resources/migrations/018-create-audit-log.up.sql
-- => Multiple SQL statements; all execute in the same transaction
 
-- Create the audit log table:
CREATE TABLE IF NOT EXISTS audit_log (
  id          TEXT PRIMARY KEY,
  table_name  TEXT NOT NULL,             -- => Which table was affected
  operation   TEXT NOT NULL,             -- => 'INSERT', 'UPDATE', or 'DELETE'
  record_id   TEXT NOT NULL,             -- => ID of the affected record
  changed_by  TEXT,                      -- => User who made the change; NULL for system ops
  changed_at  TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  old_values  TEXT,                      -- => JSON of previous values (nullable for INSERT)
  new_values  TEXT                       -- => JSON of new values (nullable for DELETE)
);
-- => Statement 1 complete
 
-- Add index for common lookup patterns:
CREATE INDEX IF NOT EXISTS idx_audit_log_table_record
  ON audit_log (table_name, record_id);
-- => Statement 2: covers WHERE table_name = ? AND record_id = ?
 
CREATE INDEX IF NOT EXISTS idx_audit_log_changed_by
  ON audit_log (changed_by)
  WHERE changed_by IS NOT NULL;
-- => Statement 3: partial index; only indexed rows where changed_by is set
-- => Skips NULL rows; useful for user-activity audit queries
 
-- => All three statements execute atomically in one transaction
-- => If statement 2 or 3 fails, statement 1 is also rolled back

-- File: resources/migrations/018-create-audit-log.down.sql
DROP TABLE IF EXISTS audit_log;
-- => Dropping the table cascades to its indexes automatically

-- File: resources/migrations/019-idempotent-ddl-patterns.up.sql
-- => Demonstrates IF NOT EXISTS / IF EXISTS guards for safe re-runnable migrations
 
-- Table creation guard:
CREATE TABLE IF NOT EXISTS settings (
  key   TEXT PRIMARY KEY,
  value TEXT NOT NULL
);
-- => Creates table only if it does not exist; no error on re-run
 
-- Column addition guard:
ALTER TABLE settings
  ADD COLUMN IF NOT EXISTS updated_at TIMESTAMPTZ DEFAULT NOW();
-- => Adds column only if absent; PostgreSQL supports IF NOT EXISTS for ADD COLUMN
-- => MySQL/SQLite handle this differently; check your database dialect
 
-- Index creation guard:
CREATE INDEX IF NOT EXISTS idx_settings_key ON settings (key);
-- => Creates index only if no index with this name exists
 
-- Sequence guard (PostgreSQL):
CREATE SEQUENCE IF NOT EXISTS order_seq START WITH 1000;
-- => Creates sequence only if it does not exist
-- => Useful for custom ID generators
 
-- Drop guard (down direction):
-- DROP TABLE IF EXISTS settings;
-- DROP INDEX IF EXISTS idx_settings_key;
-- DROP SEQUENCE IF EXISTS order_seq;
-- => All drop statements should use IF EXISTS in down files

-- File: resources/migrations/020-cascade-deletes.up.sql
-- => Configures cascade delete for parent-child table relationships
 
-- Refresh tokens cascade with user deletion:
ALTER TABLE refresh_tokens
  ADD CONSTRAINT fk_refresh_tokens_user
  FOREIGN KEY (user_id) REFERENCES users (id)
  ON DELETE CASCADE;
-- => Deleting a user automatically deletes all their refresh tokens
-- => No orphan tokens remain after account deletion
-- => Application does not need to explicitly delete tokens before deleting user
 
-- Expenses cascade with user deletion:
ALTER TABLE expenses
  ADD CONSTRAINT fk_expenses_user_cascade
  FOREIGN KEY (user_id) REFERENCES users (id)
  ON DELETE CASCADE;
-- => Deleting a user automatically deletes all their expenses
-- => Permanent data loss: cascade is appropriate for owned child data
 
-- Attachments cascade with expense deletion:
ALTER TABLE attachments
  ADD CONSTRAINT fk_attachments_expense
  FOREIGN KEY (expense_id) REFERENCES expenses (id)
  ON DELETE CASCADE;
-- => Multi-level cascade: delete user -> deletes expenses -> deletes attachments
-- => Cascades chain through the entire ownership hierarchy

-- File: resources/migrations/020-cascade-deletes.down.sql
ALTER TABLE attachments  DROP CONSTRAINT IF EXISTS fk_attachments_expense;
ALTER TABLE expenses     DROP CONSTRAINT IF EXISTS fk_expenses_user_cascade;
ALTER TABLE refresh_tokens DROP CONSTRAINT IF EXISTS fk_refresh_tokens_user;

-- File: resources/migrations/021-drop-legacy-columns.up.sql
-- => Removes columns that are no longer used by the application
-- => IMPORTANT: Only run AFTER deploying application code that no longer reads these columns
 
-- Safe column drop (column no longer referenced by any code):
ALTER TABLE users
  DROP COLUMN IF EXISTS legacy_session_token,
  -- => Remove old session token column (replaced by refresh_tokens table)
  DROP COLUMN IF EXISTS old_avatar_url;
  -- => Remove old avatar URL (migrated to object storage in migration 015)
  -- => IF EXISTS prevents error if columns were never added (staging env drift)
 
-- Safe table drop:
DROP TABLE IF EXISTS legacy_user_preferences;
-- => Table replaced by settings table in migration 019
-- => Verify zero traffic to this table in production before dropping
-- => Consider: SELECT COUNT(*) FROM legacy_user_preferences; first

-- File: resources/migrations/021-drop-legacy-columns.down.sql
-- => Restore dropped columns (data is lost; this restores structure only)
 
ALTER TABLE users
  ADD COLUMN IF NOT EXISTS old_avatar_url TEXT,
  -- => Column restored but all data is gone (permanent loss)
  ADD COLUMN IF NOT EXISTS legacy_session_token TEXT;
  -- => Down migration can only restore column structure, not data
 
CREATE TABLE IF NOT EXISTS legacy_user_preferences (
  user_id TEXT PRIMARY KEY,
  preferences TEXT
);
-- => Table structure restored but data is permanently lost
-- => This is why drop migrations require careful pre-deployment validation

;; File: deps.edn
;; => Clojure deps.edn format (not Leiningen project.clj)
 
{:paths ["src"           ; => Source code directory on classpath
         "resources"]    ; => Resources directory on classpath (migrations live here)
                         ; => resources/ must be in :paths for Migratus to find migration files
 
 :deps {;; Clojure core:
        org.clojure/clojure {:mvn/version "1.12.0"}
 
        ;; Migratus (database migrations):
        migratus/migratus {:mvn/version "1.6.5"}
        ;; => Maven group: migratus
        ;; => Artifact: migratus
        ;; => Version: 1.6.5 (latest stable as of 2026)
        ;; => Brings next.jdbc as a transitive dependency
 
        ;; JDBC driver (PostgreSQL):
        org.postgresql/postgresql {:mvn/version "42.7.10"}
        ;; => PostgreSQL JDBC driver required for jdbc:postgresql:// URIs
        ;; => Migratus does not bundle any JDBC driver
 
        ;; next.jdbc (direct database access alongside Migratus):
        com.github.seancorfield/next.jdbc {:mvn/version "1.3.1093"}}}
        ;; => next.jdbc is a transitive dep of Migratus but pin it explicitly
        ;; => for direct jdbc usage in the application

;; Verify Migratus resolves after editing deps.edn:
;; Run in terminal:
;; clj -P
;; => Downloads all dependencies declared in deps.edn
;; => -P flag: prepare (download only, do not start REPL)
;; => Error here means wrong coordinates or network issues
 
;; Test Migratus is on the classpath from the REPL:
(require '[migratus.core :as migratus])  ; => Loads migratus.core namespace
                                          ; => Error if dependency not resolved
migratus/migrate                          ; => #function[migratus.core/migrate]
                                          ; => Confirms function is available