# Gateway Database Management
This document describes how to manage database migrations for the Newton Prover Gateway.
> **Related Documentation**: For the complete secrets management data model including on-chain contract relationships, API workflows, and security considerations, see [`../../../gateway/src/rpc/api/README.md`](../../../gateway/src/rpc/api/README.md).
## Overview
The gateway uses PostgreSQL for persistent storage of API keys and encrypted secrets. Database migrations are managed using `sqlx-cli` and run **offline** (before the application starts), not at runtime.
## Prerequisites
1. Install `sqlx-cli`:
```bash
cargo install sqlx-cli --no-default-features --features postgres
```
2. Set up your database connection in environment:
```bash
export DATABASE_URL=postgres://newton:newton@localhost:5432/newton_gateway
```
Or create a `.env` file in the project root with:
```
DATABASE_URL=postgres://newton:newton@localhost:5432/newton_gateway
```
## Migration Commands
All migration commands are available through the Justfile in the project root:
### Run Pending Migrations
Apply all pending migrations to your database:
```bash
just db-migrate
```
This command:
- Connects to the database specified in `DATABASE_URL`
- Runs all pending migrations from `crates/gateway/migrations/`
- Updates the `_sqlx_migrations` table to track applied migrations
### Revert Last Migration
Rollback the most recently applied migration:
```bash
just db-migrate-revert
```
**Warning**: This will undo schema changes and may result in data loss.
### Create New Migration
Generate a new reversible migration (`.up.sql` + `.down.sql` pair):
```bash
just db-migration-new --name create_users_table
```
This creates two timestamped SQL files in `crates/gateway/migrations/`:
- `{timestamp}_create_users_table.up.sql` — the forward migration
- `{timestamp}_create_users_table.down.sql` — the rollback migration
Always write both the up and down SQL. The down migration should cleanly undo the up migration.
**Note**: The first 5 migrations (pre-2026-02-20) use the simple `.sql` format. Their rollback SQL is documented in `crates/gateway/migrations/_rollback_reference/` for manual use. All new migrations use reversible format.
### Generate Init SQL (for Dashboard Repo)
Generate an idempotent init SQL file from gateway migrations. This is used by the dashboard repo (`newton-dashboard-api`) for local development, where gateway tables need to exist before Alembic runs:
```bash
# Print to stdout
just db-generate-init-sql
# Write to file
just db-generate-init-sql --out ../newton-dashboard-api/scripts/init_gateway_tables.sql
```
This command:
- Starts a temporary Docker Postgres container
- Runs all gateway sqlx migrations against it
- Dumps the final schema for gateway-owned tables (`api_keys`, `policy_client_secret`, `encrypted_data_refs`)
- Post-processes the dump to add `IF NOT EXISTS` / `CREATE OR REPLACE` guards
- Cleans up the container
### Validate Init SQL
Verify that an existing init SQL file produces the same schema as the migrations:
```bash
just db-validate-init-sql --file ../newton-dashboard-api/scripts/init_gateway_tables.sql
```
This catches schema drift between the gateway migrations and the dashboard's local dev init script. CI runs this automatically when migration files change.
### CI Workflows
Two GitHub Actions workflows automate migration validation and cross-repo sync:
**`validate-migrations.yml`** (PR checks): Triggers on PRs that change `crates/gateway/migrations/**`. Runs the full `generate-init-sql.sh` pipeline to verify migrations apply cleanly on a fresh Postgres and produce valid idempotent SQL. The generated SQL is uploaded as an artifact for review.
**`sync-init-sql.yml`** (main branch): Triggers when migration files are merged to main. Generates fresh init SQL and dispatches a `repository_dispatch` event (`update-init-sql`) to the dashboard repo (`newton-dashboard-api`). Requires `CROSS_REPO_PAT` secret with repo scope on the dashboard repo. The dashboard repo needs a corresponding workflow to receive the event and update its `scripts/init_gateway_tables.sql`.
### Prepare Offline Mode
Generate query metadata for SQLx offline mode (allows compilation without a database connection):
```bash
just db-prepare
```
This command:
- Analyzes all SQL queries in the gateway code
- Generates `.sqlx/query-*.json` files containing type information
- Enables `SQLX_OFFLINE=true` mode for CI/CD builds
**Important**: Run this after:
- Adding or modifying SQL queries in the code
- Running new migrations that change the schema
- Before committing code that changes database interactions
## Offline Mode
The gateway is configured to use SQLx offline mode by default (`SQLX_OFFLINE=true` in `.env`). This means:
**Advantages**:
- No database connection required to compile the code
- Faster CI/CD builds
- Type-safe SQL queries at compile time
**Requirements**:
- The `.sqlx/` directory must contain up-to-date query metadata
- Run `just db-prepare` after any SQL changes
## Development Workflow
### Quick Start (Recommended)
Single command to start Postgres + Redis, run migrations, and seed dev API keys:
```bash
just local-dev
```
This uses Docker Compose to provide Postgres (port 5432) and Redis (port 6379).
### Manual Setup
If you prefer to manage your own Postgres instance:
1. Set database URL:
```bash
export DATABASE_URL=postgres://newton:newton@localhost:5432/newton_gateway
```
2. Run migrations:
```bash
just db-migrate
```
3. Seed development API keys:
```bash
just db-seed-api-keys
```
4. Prepare offline mode (optional, for faster builds):
```bash
just db-prepare
```
### Making Schema Changes
1. Create a new migration:
```bash
just db-migration-new --name add_user_roles
```
2. Edit the generated migration file in `crates/gateway/migrations/`
3. Apply the migration:
```bash
just db-migrate
```
4. Update offline query data:
```bash
just db-prepare
```
5. Commit both the migration file and updated `.sqlx/` files
### Production Deployment
**Before starting the gateway application**:
1. Ensure `DATABASE_URL` is set in production environment
2. Run migrations:
```bash
just db-migrate
```
3. Start the gateway:
```bash
cargo run --package newton-prover-gateway --bin gateway
```
## Existing Migrations
Current migrations in `crates/gateway/migrations/` (simple format, rollback SQL in `_rollback_reference/`):
- **20251213100000_api_keys_user_pk_address_api_key.sql**: Creates the `api_keys` table with Ethereum `address` field and comprehensive permission management. Includes indexes for address, active status, and permissions (GIN). Sets up automatic `updated_at` trigger.
- **20251213100500_create_policy_client_secret.sql**: Creates `policy_client_secret` table for secrets management. 20-byte address length validation and composite unique constraint on (policy_client_address, policy_data_address).
- **20260210100000_create_encrypted_data_refs.sql**: Creates `encrypted_data_refs` table for privacy-preserving policy evaluation. HPKE-encrypted data blobs with sender/policy client/recipient scoping.
- **20260212100000_drop_policy_client_owner.sql**: Removes the `policy_client_owner` table (ownership now verified on-chain via `INewtonPolicyClient.getOwner()`).
- **20260220100000_api_keys_add_id_pk.sql**: Adds `id` UUID column as new primary key (replaces `user_id` PK to support multiple keys per user). Creates index on `user_id`, conditional FK to `user` table for dashboard integration, and composite PK on `policy_client_secret`.
## API Key Management
API keys are stored in the database. See the comments in `gateway.toml` for SQL commands to:
- Create new API keys
- Update permissions
- Manage rate limits
- Revoke keys
## Troubleshooting
### Error: "DATABASE_URL is not set"
Set the environment variable:
```bash
export DATABASE_URL=postgres://newton:newton@localhost:5432/newton_gateway
```
### Error: "Failed to connect to database"
Check that:
1. PostgreSQL is running
2. Database exists: `psql -l | grep newton_gateway`
3. Connection string is correct
4. User has proper permissions
### Error: "query data is out of sync"
Run `just db-prepare` to regenerate query metadata:
```bash
just db-prepare
```
### Migration Conflicts
If you have conflicting migrations:
1. Revert to a known good state: `just db-migrate-revert`
2. Fix the migration file
3. Re-apply: `just db-migrate`
## Cross-Repo Development (Dashboard)
The gateway and dashboard (`newton-dashboard-api`) share a single `newton_gateway` Postgres database. When developing across both repos locally:
1. Start services from the gateway repo: `just local-dev`
2. In the dashboard repo, Alembic expects gateway tables to already exist. The dashboard's `docker-compose.yml` mounts `scripts/init_gateway_tables.sql` at `/docker-entrypoint-initdb.d/` to create them.
3. If you change gateway migrations, regenerate the init SQL: `just db-generate-init-sql --out ../newton-dashboard-api/scripts/init_gateway_tables.sql`
Gateway-owned tables: `api_keys`, `policy_client_secret`, `encrypted_data_refs`. Dashboard-owned tables: `user`, `user_factor`, `user_key`, `cli_session`, `project`.
## References
- [SQLx Documentation](https://github.com/launchbadge/sqlx)
- [SQLx CLI Documentation](https://github.com/launchbadge/sqlx/tree/main/sqlx-cli)
- [SQLx Offline Mode](https://github.com/launchbadge/sqlx/blob/main/sqlx-cli/README.md#enable-building-in-offline-mode-with-query)