UserDb¶

UserDb stores user identity and role information. It implements a role-extension pattern where user is the identity root and admin, client, and subscriber are role extensions sharing the same primary key.

Schema¶

erDiagram
    user ||--o| admin : "is"
    user ||--o| client : "is"
    user ||--o| subscriber : "is"
    client }o--o{ subscriber : "client_subscriber"

    user {
        uuid id PK
        string subject
        bytea phone_number_encrypted
        string phone_number_hash UK
        string locale
        timestamp joined_date
    }

    admin {
        uuid id PK,FK
    }

    client {
        uuid id PK,FK
        string name
        string customer_id
        string subscription_id
        string slug UK
    }

    subscriber {
        uuid id PK,FK
    }

    client_subscriber {
        uuid client_id PK,FK
        uuid subscriber_id PK,FK
    }

Tables¶

user¶

Identity root table. All users have exactly one row here.

Column	Type	Description
`id`	uuid	Primary key (auto-generated)
`subject`	string	Keycloak subject identifier
`phone_number_encrypted`	bytea	Encrypted phone number
`phone_number_hash`	string	SHA-256 hash for lookups (unique)
`locale`	string	User's locale (default: `en-US`)
`joined_date`	timestamp	Registration timestamp

admin¶

System administrators. Can manage all clients and system configuration.

Column	Type	Description
`id`	uuid	FK to `user.id`

client¶

Business accounts that send announcements to subscribers.

Column	Type	Description
`id`	uuid	FK to `user.id`
`name`	string	Business display name
`customer_id`	string	Stripe customer ID
`subscription_id`	string	Stripe subscription ID
`slug`	string	Unique URL-safe identifier (max 12 chars)

subscriber¶

Users who receive SMS announcements from clients.

Column	Type	Description
`id`	uuid	FK to `user.id`

client_subscriber¶

Many-to-many join table linking clients to their subscribers.

Column	Type	Description
`client_id`	uuid	FK to `client.id`
`subscriber_id`	uuid	FK to `subscriber.id`

Role Extension Pattern¶

A user can have multiple roles simultaneously. For example, a user could be both a client (sending announcements) and a subscriber (receiving announcements from other clients).

user (id: abc-123)
   client (id: abc-123)  -- same user is a client
   subscriber (id: abc-123)  -- and also a subscriber

This is implemented via 1:1 relationships where the role tables use the user's ID as both their primary key and foreign key.

Phone Number Security¶

Phone numbers are stored with defense-in-depth:

Encrypted storage (phone_number_encrypted) - AES encryption for the actual number
Hash for lookups (phone_number_hash) - SHA-256 hash enables finding users by phone without decryption

Connection String¶

ConnectionStrings__UserDb=Host=localhost;Database=userdb;Username=postgres;Password=...