# Database

Rise uses PostgreSQL for data storage with SQLX for compile-time verified SQL queries and migrations.

## Overview

Schema: Projects, Teams, Deployments, Service Accounts, Users

## Schema Management

Rise uses SQLX migrations for database schema versioning.

### Migrations Directory

Migrations in `./migrations/` (project root) with timestamp-based names.

### Creating Migrations

```bash
sqlx migrate add <description>
```

Creates `migrations/<timestamp>_<description>.sql`. Edit and add SQL.

### Running Migrations

**Development**: `mise db:migrate` (auto-run by `mise backend:run`)

**Production**: `sqlx migrate run`

### Migration Best Practices

1. Test on production copy first
2. Use `CREATE INDEX CONCURRENTLY` in PostgreSQL
3. Avoid blocking operations on large tables
4. Test rollback procedures

## SQLX Compile-Time Verification

`.sqlx/` directory contains query metadata for offline builds.

```bash
cargo sqlx prepare              # Generate metadata
cargo sqlx prepare --check      # Verify cache
```

Regenerate after migrations or SQL query changes.

### Writing Queries

Use `sqlx::query!` macro for compile-time verification (syntax, types, columns).

## Database Access

### Development

Connect to the local PostgreSQL database:

```bash
# Using psql
docker-compose exec postgres psql -U rise -d rise

# Or with connection string
psql postgres://rise:rise123@localhost:5432/rise
```

**Common queries**:

```sql
-- List all projects
SELECT * FROM projects;

-- Show deployment status
SELECT name, status, created_at FROM deployments ORDER BY created_at DESC LIMIT 10;

-- Count users
SELECT COUNT(*) FROM users;

-- Show team membership
SELECT t.name, u.email
FROM teams t
JOIN team_members tm ON t.id = tm.team_id
JOIN users u ON tm.user_id = u.id;
```

### Production

**Use read-only access for debugging**:

```bash
# Connect with read-only user
psql postgres://rise_readonly:password@rds-endpoint:5432/rise

# Limit query results
\set LIMIT 100
SELECT * FROM projects LIMIT :LIMIT;
```

**Never run write queries directly** on production. Use migrations instead.

## Resetting the Database

### Development

Completely reset the development database:

```bash
# Remove database volume
docker-compose down -v

# Start fresh
mise backend:run
```

This deletes all data and re-runs migrations.

### Soft Reset (Keep Schema)

Delete data without removing the schema:

```bash
# Connect to database
psql postgres://rise:rise123@localhost:5432/rise

# Truncate tables (preserves schema)
TRUNCATE deployments, projects, teams, team_members, users, service_accounts RESTART IDENTITY CASCADE;
```

## Common Patterns

### Transactions

Use transactions for multi-step operations:

```rust
let mut tx = pool.begin().await?;

sqlx::query!(
    "INSERT INTO projects (name, owner_type, owner_id) VALUES ($1, $2, $3)",
    name,
    "user",
    user_id
)
.execute(&mut *tx)
.await?;

sqlx::query!(
    "INSERT INTO audit_log (action, user_id) VALUES ($1, $2)",
    "create_project",
    user_id
)
.execute(&mut *tx)
.await?;

tx.commit().await?;
```

### Optional Fields

Handle NULL columns:

```rust
let deployment = sqlx::query!(
    r#"
    SELECT id, name, expires_at
    FROM deployments
    WHERE id = $1
    "#,
    deployment_id
)
.fetch_one(&pool)
.await?;

// expires_at is Option<DateTime<Utc>>
if let Some(expiry) = deployment.expires_at {
    println!("Expires at: {}", expiry);
}
```

### Custom Types

Use Postgres ENUM types:

```sql
CREATE TYPE visibility AS ENUM ('public', 'private');

ALTER TABLE projects ADD COLUMN visibility visibility NOT NULL DEFAULT 'public';
```

In Rust:

```rust
#[derive(Debug, sqlx::Type)]
#[sqlx(type_name = "visibility", rename_all = "lowercase")]
enum Visibility {
    Public,
    Private,
}
```

## Performance Considerations

### Indexes

Create indexes for frequently queried columns:

```sql
-- Lookups by owner
CREATE INDEX idx_projects_owner ON projects(owner_type, owner_id);

-- Deployment status queries
CREATE INDEX idx_deployments_status ON deployments(status) WHERE status != 'stopped';

-- Expiration cleanup
CREATE INDEX idx_deployments_expires_at ON deployments(expires_at) WHERE expires_at IS NOT NULL;
```

### Connection Pooling

Configure connection pool size in `config/production.toml` based on load and database limits.

### Query Optimization

Use `EXPLAIN ANALYZE` to optimize slow queries:

```sql
EXPLAIN ANALYZE
SELECT * FROM deployments
WHERE project_id = 123 AND status = 'running'
ORDER BY created_at DESC;
```

## Troubleshooting

### "Migrations have not been run"

**Problem**: Backend can't start because migrations are pending.

**Solution**:
```bash
mise db:migrate
```

### "SQLX cache is out of date"

**Problem**: Query metadata doesn't match actual database schema.

**Solution**:
```bash
cargo sqlx prepare
```

### "Connection refused"

**Problem**: Can't connect to PostgreSQL.

**Solution**:
```bash
# Check if PostgreSQL is running
docker-compose ps postgres

# Check logs
docker-compose logs postgres

# Restart
docker-compose restart postgres
```

### Deadlocks

**Problem**: Transactions blocking each other.

**Solution**:
- Keep transactions short
- Always acquire locks in the same order
- Use `SELECT ... FOR UPDATE NOWAIT` to fail fast

## Next Steps

- **Learn about local development**: See [Local Development](development.md)
- **Production database setup**: See [Production Setup](production.md)
