# PostgreSQL Schema

## Core Tables

### `roles`

- `id uuid primary key`
- `name text unique not null`
- `description text not null default ''`
- `created_at timestamptz not null default now()`
- `updated_at timestamptz not null default now()`

### `users`

- `id uuid primary key`
- `role_id uuid not null references roles(id)`
- `username citext unique not null`
- `display_name text not null`
- `email citext unique`
- `password_hash text not null`
- `is_active boolean not null default true`
- `last_login_at timestamptz`
- `created_at timestamptz not null default now()`
- `updated_at timestamptz not null default now()`
- `deleted_at timestamptz`

### `sessions`

- `id uuid primary key`
- `user_id uuid not null references users(id)`
- `ip_address inet`
- `user_agent text`
- `last_seen_at timestamptz not null default now()`
- `expires_at timestamptz not null`
- `created_at timestamptz not null default now()`
- `revoked_at timestamptz`

### `refresh_tokens`

- `id uuid primary key`
- `session_id uuid not null references sessions(id)`
- `user_id uuid not null references users(id)`
- `token_hash text not null`
- `expires_at timestamptz not null`
- `created_at timestamptz not null default now()`
- `revoked_at timestamptz`

### `gateways`

- `id uuid primary key`
- `name text unique not null`
- `endpoint text not null`
- `public_key text not null`
- `listen_port integer not null`
- `vpn_cidr cidr not null`
- `dns_servers text[] not null default '{}'`
- `is_active boolean not null default true`
- `last_sync_at timestamptz`
- `created_at timestamptz not null default now()`
- `updated_at timestamptz not null default now()`
- `deleted_at timestamptz`

### `devices`

- `id uuid primary key`
- `user_id uuid not null references users(id)`
- `gateway_id uuid references gateways(id)`
- `name text not null`
- `platform text not null`
- `os_version text not null default ''`
- `app_version text not null default ''`
- `device_fingerprint text not null`
- `public_key text not null`
- `status text not null default 'active'`
- `last_seen_at timestamptz`
- `last_connected_at timestamptz`
- `approved_at timestamptz`
- `revoked_at timestamptz`
- `created_at timestamptz not null default now()`
- `updated_at timestamptz not null default now()`
- `deleted_at timestamptz`

Unique index:

- `(user_id, device_fingerprint)` where `deleted_at is null`

### `wireguard_peers`

- `id uuid primary key`
- `device_id uuid not null references devices(id)`
- `gateway_id uuid not null references gateways(id)`
- `public_key text unique not null`
- `assigned_ip inet not null`
- `preshared_key_ciphertext text`
- `allowed_ips cidr[] not null default '{}'`
- `dns_servers text[] not null default '{}'`
- `profile_revision integer not null default 1`
- `last_profile_issued_at timestamptz`
- `created_at timestamptz not null default now()`
- `updated_at timestamptz not null default now()`
- `deleted_at timestamptz`

### `ip_allocations`

- `id uuid primary key`
- `gateway_id uuid not null references gateways(id)`
- `device_id uuid references devices(id)`
- `address inet not null`
- `status text not null default 'allocated'`
- `allocated_at timestamptz not null default now()`
- `released_at timestamptz`
- `created_at timestamptz not null default now()`
- `updated_at timestamptz not null default now()`

Unique indexes:

- `(gateway_id, address)`
- `(device_id)` where `status = 'allocated'`

### `policies`

- `id uuid primary key`
- `name text unique not null`
- `description text not null default ''`
- `priority integer not null default 100`
- `effect text not null default 'allow'`
- `is_active boolean not null default true`
- `full_tunnel boolean not null default false`
- `created_by uuid references users(id)`
- `created_at timestamptz not null default now()`
- `updated_at timestamptz not null default now()`
- `deleted_at timestamptz`

### `policy_targets`

- `id uuid primary key`
- `policy_id uuid not null references policies(id)`
- `target_type text not null`
- `target_id uuid not null`
- `created_at timestamptz not null default now()`

Target types:

- `user`
- `device`
- `group`

### `policy_destinations`

- `id uuid primary key`
- `policy_id uuid not null references policies(id)`
- `destination cidr not null`
- `description text not null default ''`
- `created_at timestamptz not null default now()`

### `groups`

- `id uuid primary key`
- `name text unique not null`
- `description text not null default ''`
- `created_at timestamptz not null default now()`
- `updated_at timestamptz not null default now()`
- `deleted_at timestamptz`

### `group_memberships`

- `id uuid primary key`
- `group_id uuid not null references groups(id)`
- `user_id uuid not null references users(id)`
- `created_at timestamptz not null default now()`

### `audit_logs`

- `id uuid primary key`
- `actor_user_id uuid references users(id)`
- `actor_device_id uuid references devices(id)`
- `event_type text not null`
- `entity_type text not null`
- `entity_id uuid`
- `status text not null`
- `ip_address inet`
- `message text not null`
- `metadata jsonb not null default '{}'::jsonb`
- `created_at timestamptz not null default now()`

### `settings`

- `id uuid primary key`
- `category text not null`
- `key text not null`
- `value jsonb not null`
- `created_at timestamptz not null default now()`
- `updated_at timestamptz not null default now()`

Unique index:

- `(category, key)`

## Notes

- UUIDs are generated with `gen_random_uuid()`.
- `citext` is used for case-insensitive usernames and emails.
- Soft deletes are enabled where historical traceability matters.
- Group tables are included now so policy resolution can grow without a destructive migration later.
