| name | 03.run-local-schema-migration |
| description | This skill will guide you through running the PostgreSQL schema migration for Pronghorn development. |
| argument-hint | Please follow the instructions to apply the schema migration to your PostgreSQL database. |
| compatibility | ["linux","macos","windows"] |
| license | MIT |
| user-invokable | true |
Run PostgreSQL Schema Migration for Pronghorn
This skill will guide you through the process of applying the Pronghorn database schema migration to your PostgreSQL instance using Docker.
When to use this skill
- When you need to initialize a fresh PostgreSQL database with the Pronghorn schema.
- After setting up a new PostgreSQL container via the PostgreSQL setup skill.
- When resetting your development database to a known state.
- When migrating from an older version of the schema.
Pre-requisites
- PostgreSQL Docker container named
pronghorn-db is running and accepting connections.
- You can verify this with:
docker exec pronghorn-db pg_isready -U pronghorn_admin -d pronghorn
- Expected output:
/var/run/postgresql:5432 - accepting connections
Running the Schema Migration with Docker PostgreSQL
The full database schema is defined in infra/migrations/001_full_schema.sql. This creates all tables, functions, RPC handlers, Row Level Security (RLS) policies, and triggers.
Executing the Migration
Run the following command from the repository root:
Get-Content "infra/migrations/001_full_schema.sql" | docker exec -i pronghorn-db psql -U pronghorn_admin -d pronghorn
This command:
- Connects to the
pronghorn-db Docker container via docker exec
- Uses
psql to execute SQL commands
- Authenticates as
pronghorn_admin user
- Targets the
pronghorn database
- Reads and executes the schema file from
infra/migrations/001_full_schema.sql
Verifying the Migration
After the migration completes, verify that all tables were created:
docker exec pronghorn-db psql -U pronghorn_admin -d pronghorn -c "\dt" | head -20
This displays the first 20 tables. You should see tables like:
projectsartifactsrequirementsstandardsdeploymentsai_agentsusers- And many others (62 tables in the
public schema)
Full Table Count
To confirm all 62 public-schema tables were created:
docker exec pronghorn-db psql -U pronghorn_admin -d pronghorn -c "SELECT count(*) FROM information_schema.tables WHERE table_schema = 'public';"
Expected output: 62 (this counts only public schema tables; the migration also creates tables in auth, storage, _realtime, and cron schemas)
Troubleshooting
"Did not find any relations"?
If \dt returns "Did not find any relations", the migration hasn't been applied. This commonly happens when:
- You restarted an existing Docker container with
docker start instead of docker run
- Docker's
docker-entrypoint-initdb.d init scripts didn't run
- The database volume was lost
Solution: Re-run the migration command above. The schema will be applied to the empty database.
"role 'pronghorn_admin' does not exist"
The database user was not created. Run the PostgreSQL setup skill first to create the user and database.
"database 'pronghorn' does not exist"
The database was not created. Run the PostgreSQL setup skill first.
"Permission denied" or "FATAL: password authentication failed"
Verify the correct credentials:
- User:
pronghorn_admin
- Database:
pronghorn
- Ensure the password matches what was set during PostgreSQL setup
Schema Contents
The migration creates:
- 62 public-schema tables (plus additional tables in
auth, storage, _realtime, and cron schemas) with primary keys, foreign keys, and indexes
- RLS (Row Level Security) policies for data access control
- PostgreSQL extensions:
uuid-ossp (UUID generation), pgcrypto (cryptographic functions)
- Stored procedures and functions for application business logic
- Triggers for automated data consistency
This ensures your local database matches the production schema exactly.
Next Steps
After successful schema migration:
- Verify the API can connect to the database
- Proceed to 4. Set Up the API in the Local Development Guide
- Begin building/testing Pronghorn features
