> ## Documentation Index
> Fetch the complete documentation index at: https://docs.charlemagnelabs.ai/llms.txt
> Use this file to discover all available pages before exploring further.

# Database Setup

> Initialize and seed the Charley PostgreSQL database

Charley requires **PostgreSQL 16**. The schema is fully idempotent — you can apply it multiple times safely.

## Applying the Schema

```bash theme={null}
psql $DATABASE_RW_URL -f db/schema.sql
```

The schema creates the following tables:

| Table                     | Description                           |
| ------------------------- | ------------------------------------- |
| `orgs`                    | Organizations / workspaces            |
| `org_members`             | Members with roles                    |
| `licenses`                | Billing plan and subscription state   |
| `devices`                 | Enrolled endpoint devices             |
| `telemetry_rollups`       | Hourly and daily analytics aggregates |
| `device_enrollment_codes` | One-time enrollment codes             |
| `org_invites`             | Pending email invitations             |
| `notifications`           | In-app notification feed              |
| `org_sso_config`          | SAML/OIDC SSO configuration           |
| `org_alert_endpoints`     | Webhook alert destinations            |
| `org_slack_integrations`  | Slack workspace connections           |

## Seed Data (Development)

To populate the database with a test organization and sample telemetry:

```bash theme={null}
psql $DATABASE_RW_URL -f db/seed.sql
```

The seed creates:

* 1 test organization
* 1 ORG\_ADMIN member (matched by email — update `seed.sql` to match your Auth0 user's email)
* 2 test devices
* 30 days of daily telemetry rollups
* 48 hours of hourly telemetry rollups

<Warning>
  Only apply `seed.sql` to development databases. It inserts fixed IDs that will conflict if applied to a database that already has data.
</Warning>

## Running Locally with Docker

The repository includes a `docker-compose.yml` for local development:

```bash theme={null}
# Start Postgres only
./dev.sh --local-db

# Start Postgres + pgAdmin GUI
./dev.sh --local-db --tools
```

pgAdmin is available at `http://localhost:5050` when started with `--tools`.

### Connection Details (local Docker)

| Field    | Value       |
| -------- | ----------- |
| Host     | `localhost` |
| Port     | `5432`      |
| Database | `charley`   |
| User     | `charley`   |
| Password | `secret`    |

## Migrations

There is no migration framework in the current codebase — the schema is maintained as a single idempotent `schema.sql`. For production schema changes, apply DDL statements manually or through your database management tooling.