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

# Database

> The tables in pgw's SQLite database, how they relate, how long pgw keeps each row, and which columns hold secrets.

pgw keeps its state in one SQLite database, `pgw.db`, in `data_dir`. Use this reference to read the database during an investigation, or to know what a backup holds.

> [!WARNING]
>
> Don't change `pgw.db` by hand. Use the admin API or the CLI. To back it up, follow [Back up, move, or restore pgw](https://docs.pgw.sipmicro.services/backup-move-restore.md).

## Entity relationship diagram

Solid lines are foreign keys that the database enforces. Dashed lines are references that pgw keeps in its own code.

```mermaid theme={null}
erDiagram
    tenants ||--o{ api_keys : "authenticates"
    tenants ||--o{ apps : "owns"
    tenants ||--o{ upstreams : "owns"
    upstreams ||--o{ upstream_domains : "serves"
    tenants ||..o{ bindings : "owns"
    apps ||..o{ bindings : "registers"
    upstreams ||--o{ bindings : "forwards to"
    tenants ||--o{ users : "owns"
    upstreams ||--o{ users : "authenticates"
    bindings |o..o{ push_events : "receives"
    tenants ||..o{ push_events : "owns"

    tenants {
        TEXT id PK
        TEXT name
        BLOB data_key "encrypted by the master key"
        INTEGER created_at
    }
    api_keys {
        BLOB hash PK "SHA-256 of the key"
        TEXT tenant_id FK
        INTEGER created_at
    }
    apps {
        TEXT id PK
        TEXT tenant_id FK "UK with platform, bundle_id"
        TEXT platform "ios, android, or web"
        TEXT bundle_id
        TEXT name "shown in log lines"
        TEXT apns_team_id
        TEXT apns_key_id
        BLOB apns_key_enc "encrypted"
        TEXT apns_environment
        TEXT fcm_project_id
        TEXT fcm_project_number
        BLOB fcm_service_acc_enc "encrypted"
        BLOB vapid_public_key
        BLOB vapid_private_key_enc "encrypted"
        TEXT vapid_subject
        TEXT delivery_mode
        TEXT managed_refresh_push
        INTEGER created_at
    }
    upstreams {
        TEXT id PK
        TEXT tenant_id FK
        TEXT name
        TEXT host UK
        INTEGER port
        TEXT transport
        TEXT mode
        TEXT contact_strategy
        INTEGER max_expires
        TEXT invite_source_cidrs
        INTEGER strict_path
        INTEGER keepalive_seconds
        TEXT realm
        TEXT managed_registration
        TEXT user_source
        TEXT kamailio_driver
        TEXT kamailio_host
        INTEGER kamailio_port
        TEXT kamailio_user
        TEXT kamailio_database
        TEXT kamailio_tls
        BLOB kamailio_password_enc
        INTEGER kamailio_use_domain
        TEXT kamailio_subscriber_table
        TEXT kamailio_username_column
        TEXT kamailio_domain_column
        TEXT kamailio_password_column
        TEXT kamailio_ha1_column
        TEXT kamailio_domain_table
        TEXT kamailio_domain_table_column
        TEXT app_auth
        TEXT bearer_issuer
        TEXT bearer_audience
        TEXT bearer_jwks_url
        INTEGER created_at
    }
    upstream_domains {
        TEXT domain PK "a host name or *. wildcard"
        TEXT upstream_id FK
    }
    bindings {
        TEXT key PK "flow token"
        TEXT tenant_id
        TEXT app_id
        TEXT upstream_id FK
        TEXT aor
        TEXT contact_uri
        TEXT platform
        TEXT provider
        INTEGER sandbox
        TEXT param
        TEXT call_token
        TEXT remote_token
        TEXT purr
        TEXT call_str
        TEXT feature_tags
        TEXT instance
        TEXT user_agent "the app's User-Agent"
        TEXT transport
        TEXT remote_addr
        INTEGER app_expires
        INTEGER upstream_expires
        INTEGER last_register_at
        INTEGER refresh_push_at
        TEXT dead_reason
        INTEGER managed
    }
    users {
        TEXT id PK
        TEXT tenant_id FK
        TEXT upstream_id FK
        TEXT domain
        TEXT username
        TEXT auth_username
        BLOB password_enc "encrypted"
        INTEGER enabled
        INTEGER last_seen_at
        INTEGER created_at
    }
    push_events {
        INTEGER id PK
        TEXT tenant_id
        TEXT binding_key
        TEXT type "call, missed, refresh, or test"
        TEXT provider
        TEXT push_type
        INTEGER status_code
        INTEGER latency_ms
        TEXT error
        TEXT call_id
        INTEGER created_at
    }
```

## Tables

| Table | Holds | Created by |
| - | - | - |
| `tenants` | One row for each tenant, with the data key that encrypts the tenant's push credentials and SIP passwords. | `pgw bootstrap` |
| `api_keys` | Hashes of the admin API keys. Each key belongs to one tenant. | `pgw bootstrap` |
| `apps` | One row for each iOS, Android, or web app, with its APNs, Firebase, or VAPID credentials. The platform and bundle ID are unique in a tenant, and a tenant has at most one web app. | The admin API |
| `upstreams` | One row for each PBX that pgw forwards registrations to. The host is unique across all tenants. For a managed upstream whose users come from Kamailio, the row also holds the Kamailio database's address and the names of its tables and columns. | The admin API |
| `upstream_domains` | One row for each domain that an upstream serves besides its host, as a host name or a `*.` wildcard. A name is unique across all upstreams' hosts and domains. | The admin API |
| `bindings` | One row for each registered device, with its address of record, push tokens, and registration expiry times. | A REGISTER from an app |
| `users` | One row for each SIP account of an upstream in managed mode, with its encrypted password. The domain is empty on an upstream without domains. The username is unique in an upstream and domain. | `pgw user create` |
| `push_events` | One row for each push attempt, with its result. | Each push that pgw sends, skips, or holds back |
| `schema_migrations` | The name of each schema change applied to the database. pgw applies the missing ones at startup and ignores names that it doesn't know. | pgw, at startup |

All times are Unix timestamps in seconds.

## Data lifetime

* pgw deletes a binding when:
  * The app unregisters, or its registration expires.
  * The account has `max_bindings_per_aor` devices, and another device registers. pgw deletes the binding that registered least recently.
  * You delete or turn off the managed user that the binding registers as, or revoke the user with `pgw user revoke`.
  * The managed device didn't register for `credential_silence_purge`.
  * The push service reports the binding's push token invalid. In managed mode, pgw also unregisters it at the PBX.
  * The managed device has no push, and its connection to pgw closed or its registration expired.
  * The device registers its Contact again under another binding, as a browser page without `+sip.instance` does when it reloads. pgw keeps the binding that holds the push token. Two devices that send the same Contact with different `+sip.instance` values, as two phones behind NATs can, keep a binding each.
  * You delete the binding with `pgw binding delete`.
* A binding keeps `dead_reason` only when pgw couldn't delete it. pgw sends it no pushes, and deletes it at the next renewal scan in managed mode, or when its registration expires in passthrough mode.
* An iPhone binding whose remote token is invalid stays, without the remote token, because calls still reach it through its VoIP token.
* pgw deletes push events older than `push_event_retention_days`.
* You can't delete an upstream while bindings use it. The API returns `409`. With `force=true`, pgw deletes the upstream and its bindings together. Deleting an upstream always deletes its users and domains through the foreign keys.

## Sensitive columns

| Column | Protection |
| - | - |
| `apps.apns_key_enc`, `apps.fcm_service_acc_enc`, `apps.vapid_private_key_enc` | Encrypted with AES-256-GCM under the tenant's data key. |
| `users.password_enc` | Encrypted with AES-256-GCM under the tenant's data key. |
| `upstreams.kamailio_password_enc` | The Kamailio database user's password. Encrypted with AES-256-GCM under the tenant's data key. |
| `tenants.data_key` | Encrypted by the master key. |
| `api_keys.hash` | SHA-256 hash. pgw never stores the key itself. |
| `bindings.call_token`, `bindings.remote_token`, `bindings.param`, `bindings.purr` | Stored in plain text. Protect `pgw.db` with file permissions. |

## Next steps

> [card] [Back up, move, or restore pgw](https://docs.pgw.sipmicro.services/backup-move-restore.md)
>
> Copy `pgw.db` and the master key to a backup or another host.

> [card] [Security](https://docs.pgw.sipmicro.services/security.md)
>
> See how pgw protects the master key and the secrets in `pgw.db`.


This documentation is built and hosted on [Mintlify](https://mintlify.com), a developer documentation platform.