Yeti Jobs Database Migrations: Setup and Execution

Yeti Jobs manages its database schema through 14 numbered SQL files stored in backend/db/. Each file handles one concern — extensions, enums, tables, indexes, or triggers — and they must be executed in strict ascending order so that every object exists before it is referenced by a later file. Migrations can be run either with the included migrate.js Node.js script or manually through psql.

These migration files are designed to be run against a fresh, empty database. All CREATE statements use IF NOT EXISTS guards, so re-running individual files is safe in isolation, but running the full sequence against a database that already has data can cause constraint violations or silent no-ops that leave the schema in an inconsistent state. Always test against a development database before migrating production.

Migration file order

The 14 files are named with a numeric prefix that defines execution order:

File	Purpose
`01_extensions.sql`	Enables `pgcrypto` for `gen_random_uuid()`
`02_enums.sql`	Creates all seven custom enum types
`03_companies.sql`	Creates the `companies` table
`04_users.sql`	Creates the `users` table (FK → `companies`)
`05_user_companies_follows.sql`	Creates the `user_companies_follows` join table
`06_jobs.sql`	Creates the `jobs` table (FK → `companies`, `users`)
`07_saved_jobs.sql`	Creates the `saved_jobs` table (FK → `companies`, `jobs`, `users`)
`08_applications.sql`	Creates the `applications` table (FK → `jobs`, `users`)
`09_email_verified.sql`	Creates the `email_verified` table (FK → `users`)
`10_ats_scores.sql`	Creates the `ats_scores` table (FK → `users`)
`11_user_education.sql`	Creates the `user_educations` table (FK → `users`)
`12_notifications.sql`	Creates the `notifications` table (FK → `users`, `companies`, `jobs`)
`13_indexes.sql`	Adds all btree, GIN, and unique indexes
`14_trigger.sql`	Creates the `update_search_job_title` function and trigger

The ordering follows the dependency graph: extensions before types, types before tables, companies before users (because users.company_id references companies.uid), and all tables before the indexes and trigger that depend on their columns.

Option 1 — Run with `migrate.js`

The migrate.js script in backend/db/ uses Node’s built-in fs module to read every .sql file from its own directory and execute them in order against the database configured in your .env.

The script

import "dotenv/config"
import { readdirSync, readFileSync } from "node:fs"
import { extname, join } from "node:path"
import connect from "../src/db.js"

const runMigration = async () => {
  try {
    const { dirname } = import.meta;
    const files = readdirSync(dirname)
    for (let i = 0; i < files.length; i++) {
      const filePath = join(dirname, files[i]);
      if (extname(filePath) === '.sql') {
        const command = readFileSync(filePath, "utf-8")
        await connect.query(command)
      }
    }
    console.log('Migration Successful')
    process.exit()
  } catch (error) {
    console.log(error)
  } finally {
    await connect.end();
  }
}
runMigration()

The script:

Reads the directory it lives in (backend/db/) with readdirSync.
Iterates every file and skips non-.sql files (such as migrate.js itself).
Reads each SQL file as a UTF-8 string and passes it directly to connect.query().
Exits the process on success, or logs the error and closes the pool on failure.

File ordering relies on readdirSync returning files in alphabetical order, which is guaranteed on most filesystems. The numeric 01_ – 14_ prefixes ensure the correct sequence without any manual sorting logic.

Running the script

Navigate to the backend directory

cd backend

Set up environment variables

Ensure your .env file exists at backend/.env and contains a valid DATABASE_PASSWORD (and any other connection details expected by src/db.js).

touch .env
# Add DATABASE_PASSWORD and other required variables

Install dependencies (first time only)

npm install

Run the migration script

node db/migrate.js

On success you will see:

Migration Successful

Option 2 — Run SQL files manually with `psql`

If you prefer full control over each step, or if you are applying migrations to a remote Supabase database from a machine without Node.js, you can execute each file individually through the psql CLI.

Connect to your PostgreSQL database

For a local Postgres instance:

psql -U postgres -d yeti_jobs

For a Supabase remote database, use the connection string from Supabase → Settings → Database → Connection string:

psql "postgresql://postgres:<PASSWORD>@<HOST>:5432/postgres"

Run the extension file

psql -U postgres -d yeti_jobs -f backend/db/01_extensions.sql

Run the enums file

psql -U postgres -d yeti_jobs -f backend/db/02_enums.sql

Run all remaining table files in order

Execute files 03 through 12 in sequence. Each command follows the same pattern:

psql -U postgres -d yeti_jobs -f backend/db/03_companies.sql
psql -U postgres -d yeti_jobs -f backend/db/04_users.sql
psql -U postgres -d yeti_jobs -f backend/db/05_user_companies_follows.sql
psql -U postgres -d yeti_jobs -f backend/db/06_jobs.sql
psql -U postgres -d yeti_jobs -f backend/db/07_saved_jobs.sql
psql -U postgres -d yeti_jobs -f backend/db/08_applications.sql
psql -U postgres -d yeti_jobs -f backend/db/09_email_verified.sql
psql -U postgres -d yeti_jobs -f backend/db/10_ats_scores.sql
psql -U postgres -d yeti_jobs -f backend/db/11_user_education.sql
psql -U postgres -d yeti_jobs -f backend/db/12_notifications.sql

Run the indexes file

psql -U postgres -d yeti_jobs -f backend/db/13_indexes.sql

Run the trigger file

psql -U postgres -d yeti_jobs -f backend/db/14_trigger.sql

You can also pipe all files in one shell command using a loop:

for f in backend/db/*.sql; do
  psql -U postgres -d yeti_jobs -f "$f"
done

The glob expansion on most Unix shells returns files in alphabetical order, preserving the 01–14 execution sequence.

Connecting to Supabase PostgreSQL

Yeti Jobs uses Supabase as its managed PostgreSQL host in production. To run migrations against a remote Supabase instance:

Get your Supabase connection string

Open your Supabase project dashboard.
Go to Settings → Database.
Copy the Connection string (URI format). It looks like:

postgresql://postgres:[YOUR-PASSWORD]@db.<PROJECT-REF>.supabase.co:5432/postgres

Update your backend .env for remote connection

Your src/db.js file reads connection details from environment variables. Update backend/.env to point at the Supabase host:

DATABASE_PASSWORD=your-supabase-database-password

If src/db.js constructs the connection from individual host, port, user, and password fields, replace them with the full Supabase connectionString and add ssl: { rejectUnauthorized: false } to allow the TLS connection from your server or local machine.

Run migrate.js against Supabase

With the updated .env in place, the standard migrate command works unchanged:

node db/migrate.js

Verify the schema in Supabase Table Editor

Open Supabase → Table Editor to confirm that all 10 tables (companies, users, jobs, applications, saved_jobs, user_companies_follows, email_verified, ats_scores, user_educations, notifications) appear in the public schema.

Comparing the two approaches

# From backend/
node db/migrate.js

	`migrate.js`	`psql` manual
Requires Node.js	Yes	No
Uses `.env` config	Yes	No (pass credentials as flags)
Stops on first error	Yes (try/catch exits)	Per-file; continues unless `-v ON_ERROR_STOP=1` is set
Best for	Local development, CI pipelines	Remote/Supabase migrations, debugging individual files

When running against a CI pipeline (GitHub Actions, etc.), use migrate.js — it reads the same environment variables your app already uses, so no extra credential management is required.

Get Started

Core Features

Configuration & Deployment

Database

Security

Yeti Jobs Database Migrations: Setup and Execution

Migration file order

Option 1 — Run with `migrate.js`

The script

Running the script

Option 2 — Run SQL files manually with `psql`

Connecting to Supabase PostgreSQL

Comparing the two approaches

Build docs developers (and LLMs) love

Get Started

Core Features

Configuration & Deployment

Database

Security

Documentation Index

​Migration file order

​Option 1 — Run with migrate.js

​The script

​Running the script

​Option 2 — Run SQL files manually with psql

​Connecting to Supabase PostgreSQL

​Comparing the two approaches

Build docs developers (and LLMs) love

Migration file order

Option 1 — Run with `migrate.js`

The script

Running the script

Option 2 — Run SQL files manually with `psql`

Connecting to Supabase PostgreSQL

Comparing the two approaches