User & Subscription Schema

ELI5

Every Skyflow user is anchored to a Bluesky DID — a permanent, opaque identity string the user can never change. Subscriptions are a separate table with a partial unique constraint that allows many cancelled rows but only one active row per user. Deletes are soft (set deleted_at) so audit and billing history survive.

Technical Deep Dive

users (core_schema.sql)

A trigger users_updated_at bumps updated_at on every UPDATE.

subscriptions

The decisive constraint:

CONSTRAINT one_active_subscription_per_user UNIQUE (user_id)
  WHERE status = 'active'

Multiple historical canceled / past_due rows are allowed; only one active row may exist. polar_subscription_id is UNIQUE so re-running a Polar webhook is idempotent.

ER View

erDiagram
    users ||--o{ subscriptions : "has"
    users ||--o{ checkout_sessions : "started"
    users ||--o{ api_keys : "owns"
    users ||--o{ sessions : "logged-in via"
    users {
        uuid id PK
        text did UK
        text handle
        text email UK
        tier tier
        timestamptz deleted_at
    }
    subscriptions {
        uuid id PK
        uuid user_id FK
        tier tier
        subscription_status status
        text polar_subscription_id UK
        timestamptz current_period_end
    }
    checkout_sessions {
        uuid id PK
        uuid user_id FK
        text polar_session_id UK
        text status
    }

Class View — Proto Models

classDiagram
    class User {
      +string id
      +string did
      +string handle
      +string email
      +Tier tier
      +UserProfile profile
      +UserStats stats
    }
    class Subscription {
      +string id
      +string user_id
      +Tier tier
      +SubscriptionStatus status
      +BillingCycle billing_cycle
      +string polar_subscription_id
    }
    class APIKey {
      +string id
      +string user_id
      +string key_hash
      +string key_prefix
      +string[] scopes
      +bool revoked
    }
    User "1" --> "*" Subscription
    User "1" --> "*" APIKey

GDPR Delete Path

DELETE /api/v1/users/:id does NOT issue an SQL DELETE. It:

1. Sets deleted_at = NOW().
2. Anonymises PII columns (handle, email, profile).
3. Publishes UserDeletedEvent { hard_delete = false }.

subscriptions.user_id is ON DELETE CASCADE, so a future hard delete will sweep billing rows automatically.

Key Terms

• DID → AT Protocol “Decentralized Identifier”, e.g. did:plc:abc123xyz. Immutable per account.
• Soft delete → deleted_at IS NOT NULL; row stays for audit / FK integrity
• Partial unique index → UNIQUE … WHERE status = 'active' enforces “at most one active” without blocking history rows
• uuid_generate_v4() → default PK; provided by the uuid-ossp extension created at the top of 001_core_schema.sql

Q&A

Q: Can two Skyflow accounts share a DID? A: No. did is UNIQUE and the valid_did CHECK constraint enforces the did:plc: shape.

Q: Why is email only conditionally indexed? A: idx_users_email ON users(email) WHERE email IS NOT NULL — many users may have NULL email; the partial index keeps the b-tree small.

Q: What stops a Polar webhook replay from creating duplicate subscriptions? A: polar_subscription_id TEXT UNIQUE plus polar_session_id on checkout_sessions. The webhook handler upserts on these keys.

Q: Where does last_seen_at get updated? A: The schema declares the column but no trigger writes it; it is the application’s responsibility (Core API on JWT validation). Treat it as best-effort, not authoritative.

Examples

Subscriptions are like gym contracts: you can have a folder full of expired contracts, but the front desk will only honour one active one. The unique-where-active index is the front desk; the cancelled rows are old paperwork in the cabinet.