pgsupa

Unified CLI for pgschema + Supabase on Windows.

pgsupa brings together everything needed to manage a Postgres + Supabase project from a single tool — declarative schema migrations, cron jobs, seed data, edge functions, auth, and storage — with shared credentials and a consistent interface.

---

How it works

Concern	Tool	How
Schema migrations (DDL)	pgschema	Via WSL (Linux binary)
Cron jobs (pg_cron)	psql	Via WSL — runs crons.sql
Seed data	psql	Via WSL — runs seeds.sql
Edge functions	Supabase CLI	Native Windows binary
Auth / Storage	Supabase CLI	Native Windows binary
Local dev stack	Supabase CLI	Native Windows binary
Credentials	Windows Credential Manager	Stored once via pgsupa auth login

pgsupa does not install or manage any of these tools. You install them yourself — pgsupa checks they are available and fails fast with a clear message if one is missing.

---

Prerequisites

Tool	Where	Install
WSL	Windows	wsl --install
pgschema	Inside WSL	go install github.com/pgplex/pgschema@latest
psql	Inside WSL	sudo apt install postgresql-client
Supabase CLI	Windows	scoop install supabase
Docker Desktop	Windows	docker.com — only for pgsupa dev start

---

Installation

Winget (recommended)

winget install pgplex.pgsupa

Manual

Download the latest .zip from Releases, extract pgsupa.exe, and add it to your PATH.

---

Quick start

# Verify all tools are installed and visible
pgsupa status

# Scaffold a new project in a new directory
pgsupa create myapp
cd myapp

# Or add pgsupa to an existing project (uses current directory)
cd existing-project
pgsupa create

# Store credentials once — saved in Windows Credential Manager
pgsupa auth login

# Capture your existing database schema into files
pgsupa schema dump

# Edit the generated files, then preview what will change
pgsupa schema plan

# Apply the changes to the database
pgsupa schema apply

# Or plan + confirm + apply in a single step
pgsupa schema push

---

Project structure

myproject/
│
├── pgsupa.toml              # config — db, schemas, crons, seeds, supabase
├── .env                     # credentials (gitignored)
├── .env.example             # committed template
├── .pgschemaignore          # exclude paths from pgschema
│
├── database/
│   └── public/
│       ├── public.sql       # pgschema entrypoint — \i DDL files only
│       ├── tables/          # ─┐
│       ├── views/           #  │  pgschema DDL
│       ├── functions/       #  │  applied via: pgsupa schema plan/apply
│       ├── indexes/         #  │
│       ├── policies/        #  │
│       └── triggers/        # ─┘
│       │
│       ├── crons/
│       │   ├── crons.sql            # \i's cron files — pgsupa cron apply
│       │   └── cleanup_sessions.sql
│       │
│       └── seeds/
│           ├── seeds.sql            # \i's seed files — pgsupa seed run
│           ├── extensions.sql
│           └── reference_data.sql
│
├── supabase/
│   ├── functions/
│   │   └── my-function/index.ts
│   ├── config.toml
│   └── seed.sql
│
└── migrations/
    ├── plans/     # public-plan.json + public-plan.txt (review before apply)
    └── applied/   # archived here after pgsupa schema apply

See docs/project-structure.md for full details including the cron and seed SQL patterns.

---

Declarative approach

Everything in pgsupa follows the same principle: declare the desired state in files, apply it to the database. This mirrors how Terraform works for infrastructure.

Schema (DDL) — pgschema

Edit tables.sql, functions.sql etc. then:

pgsupa schema plan    # see what will change
pgsupa schema apply   # apply it

Cron jobs — pg_cron via psql

Each cron file uses the unschedule-then-schedule pattern, making it idempotent and safe to re-run:

-- database/public/crons/cleanup_sessions.sql
SELECT cron.unschedule('public.cleanup-sessions')
WHERE EXISTS (SELECT 1 FROM cron.job WHERE jobname = 'public.cleanup-sessions');

SELECT cron.schedule(
  'public.cleanup-sessions',
  '0 * * * *',
  $$DELETE FROM public.sessions WHERE expires_at < now()$$
);

crons.sql controls the order with \i:

\i cleanup_sessions.sql
\i refresh_views.sql

Apply: pgsupa cron apply

Seeds — psql

Each seed file uses ON CONFLICT DO NOTHING for idempotency:

-- database/public/seeds/reference_data.sql
INSERT INTO public.countries (code, name) VALUES
  ('ET', 'Ethiopia'),
  ('US', 'United States')
ON CONFLICT (code) DO NOTHING;

seeds.sql controls the order with \i:

\i extensions.sql
\i reference_data.sql
\i dev_users.sql

Apply: pgsupa seed run

Webhooks — pgschema

Database webhooks are triggers + pg_net functions — pure DDL. They live in functions/ and triggers/ and are managed by pgschema automatically. No extra commands needed.

---

Full command reference

Setup
  pgsupa create [name]           New project (CWD if no name given)
  pgsupa init                    Add pgsupa to an existing project
  pgsupa link                    Link to a remote Supabase project
  pgsupa auth login              Store credentials (Windows Credential Manager)
  pgsupa auth status             Show stored credentials (masked)
  pgsupa status                  Check all tools and project config

Schema  (→ pgschema via WSL)
  pgsupa schema dump             Dump live db → SQL files (--multi-file)
  pgsupa schema plan             Diff files vs db → plan files
  pgsupa schema apply            Apply plan → db
  pgsupa schema diff             Show diff inline, no files written
  pgsupa schema push             Plan + confirm + apply in one step
  pgsupa schema clean            Delete all plan files
  pgsupa schema clean-schemas    DESTRUCTIVE: delete all SQL files

Cron Jobs  (→ psql via WSL)
  pgsupa cron apply              Run crons.sql → schedules jobs via pg_cron
  pgsupa cron list               Show live jobs from cron.job table

Seeds  (→ psql via WSL)
  pgsupa seed run                Run seeds.sql → populate tables

Edge Functions  (→ Supabase CLI)
  pgsupa functions new <n>       Scaffold a new edge function
  pgsupa functions deploy [n]    Deploy all or a specific function
  pgsupa functions serve [n]     Serve locally
  pgsupa functions list          List deployed functions
  pgsupa functions delete <n>    Delete a function

Local Dev  (→ Supabase CLI + Docker)
  pgsupa dev start               Start local Supabase stack
  pgsupa dev stop                Stop local stack
  pgsupa dev reset               Reset db + re-apply seed.sql

Storage & Auth  (→ Supabase CLI)
  pgsupa storage push            Push storage bucket config
  pgsupa auth push               Push auth config

Full reference: docs/commands.md

---

New environment setup

pgsupa schema dump    # 1. capture live db structure into files
pgsupa schema apply   # 2. apply any pending schema changes
pgsupa cron apply     # 3. schedule cron jobs
pgsupa seed run       # 4. populate reference and seed data

---

License

MIT