Core Data Models

ELI5

SMO1 stores four main things: who you are (User), what links you made (Link), badges you unlocked (Achievement), and secret passwords for apps (API Key). Each one has a specific shape — like a form with required and optional fields — and they connect to each other like a family tree.

Technical Deep Dive

Source: purr-api migrations/000001_initial_schema.up.sql:60-172, 000003_billing_columns.up.sql:10-14, 000005_text_user_ids.up.sql, 000007_custom_domains.up.sql, 000009_payments_ledger.up.sql:11, internal/models/link.go:76-116, 203, internal/handler/api_key_handler.go:95, 153-160, internal/middleware/auth.go:267-330 (origin/main bb6cfc2); purr-api docs/decisions/2026-10-24-sell-through-toll.md ruling 7 (accepted), read 2026-10-25. Corrected in v1.1: column names, user creation path, QR URL, key prefix length, dormant Polar columns.

Entity Relationship

%%{init: {'theme': 'base', 'themeVariables': {'primaryColor': '#e8f4f8', 'primaryTextColor': '#2d3748', 'primaryBorderColor': '#90cdf4', 'lineColor': '#718096', 'secondaryColor': '#f0fff4', 'tertiaryColor': '#fefcbf'}}}%%
erDiagram
    USER ||--o{ LINK : creates
    USER ||--o{ API_KEY : owns
    USER ||--o{ ACHIEVEMENT : earns
    USER ||--o{ XP_EVENT : generates
    LINK ||--o{ LINK_CLICK : receives

    USER {
        text id PK
        text email
        text username
        text display_name
        text avatar_url
        enum tier
        text polar_customer_id
        text polar_subscription_id
        enum subscription_status
        timestamptz subscription_current_period_end
        boolean subscription_cancel_at_period_end
        bigint xp
        int level
        int streak_current
        int streak_longest
        timestamptz last_streak_at
        bigint total_clicks
        int total_links
        int monthly_links_used
        timestamptz created_at
        timestamptz updated_at
        timestamptz last_login_at
    }

    LINK {
        uuid id PK
        text user_id FK
        text original_url
        text uid
        text custom_slug
        text display_name
        uuid domain_id FK
        bigint total_clicks
        bigint unique_visitors
        timestamptz last_clicked_at
        text utm_source
        text utm_medium
        text utm_campaign
        text utm_term
        text utm_content
        enum protection_type
        text protection_hash
        text protection_hint
        int protection_max_attempts
        enum content_classification
        enum redirect_mode
        boolean is_active
        timestamptz expires_at
        timestamptz created_at
        timestamptz updated_at
    }

    ACHIEVEMENT {
        uuid id PK
        text name
        text description
        enum category
        jsonb criteria
        text icon_url
        int xp_reward
        timestamptz created_at
    }

    API_KEY {
        uuid id PK
        text user_id FK
        text key_hash
        text key_prefix
        text[] permissions
        timestamptz expires_at
        timestamptz revoked_at
        timestamptz last_used_at
        timestamptz created_at
    }

User Model

The User record is created by purr-api on the first meow-web service-JWT request for a new sub (EnsureExists, tier free, level 1). The id column is text since migration 000005 (it began as uuid), and holds the Airlock subject that meow-web passes through.

Key groups:

• Identity: id, email, username, display_name, avatar_url
• Subscription: tier (free/pro/business/early_adopter) is read on every authenticated request. The Polar-era columns polar_customer_id, polar_subscription_id, subscription_status, subscription_current_period_end, subscription_cancel_at_period_end and subscription_updated_at are still written by the Polar webhook today; under the accepted 2026-10-24 TOLL decision they become dormant (no longer written, kept until an operator-gated drop) and tier becomes a cache of TOLL entitlements (smo1-026)
• Gamification: xp, level, streak_current, streak_longest, last_streak_at
• Stats: total_clicks, total_links, monthly_links_used

Link Model

The central entity. Two identifiers coexist:

• uid — 6-character auto-generated slug ([a-zA-Z0-9]), cryptographically random
• custom_slug — optional user-defined vanity slug (e.g., my-event)

Protection fields:

• protection_type: none | password | pin
• protection_hash: bcrypt hash (never exposed in API; json:"-" tag)
• protection_hint: user-facing hint (e.g., “My dog’s name”)
• protection_max_attempts: 5 (enforced per IP per link)

Lifecycle:

• is_active: soft-delete flag
• expires_at: optional TTL
• redirect_mode: redirect (302) or proxy (reverse proxy)

LinkDTO (API Response)

The API never returns raw Link structs to clients. It returns LinkDTO with computed fields:

API Key

• Prefix: sk_ (Stripe-style convention)
• Storage: Only the SHA-256 hash is stored; the plaintext is shown once at creation
• Identification: key_prefix stores the first 12 characters (sk_ plus 8) so the user can tell keys apart in the UI
• Revocation: revoked_at timestamp; revoked keys are filtered out at lookup time

Achievement

• Categories: getting_started, link_master, analytics_pro, engagement_king, streak_champion, secret
• Criteria: JSONB blob with unlock conditions (e.g., { "min_links": 10, "min_clicks": 100 })
• Progress tracking: UserAchievement junction table tracks current_value and unlocked_at

Key Terms

• LinkDTO → API-facing data transfer object with computed scores and URLs
• uid → 6-character cryptographically random alphanumeric slug
• protection_hash → bcrypt hash of password/PIN; never exposed via API
• key_prefix → First 12 characters (sk_ plus 8) of an API key plaintext, stored for UI identification
• content_classification → general, personal, work, or nsfw — used for safety filtering
• Polar → Payment provider called directly by today’s code; its ids on users go dormant once TOLL sell-through lands

Q&A

Q: What are the two protection types a link can have? A: password and pin (the enum also has none, the default). Both are stored as a bcrypt protection_hash that is never serialised.

Q: What three computed scores does a LinkDTO expose? A: pawprintz (0-100 activity with recency decay), treatz (compression percentage) and niblz (characters saved), alongside the computed status.

Q: How is an API key stored securely? A: Only the hex SHA-256 of the full sk_... key is stored in key_hash, plus a 12-character key_prefix for display. The plaintext is returned once at creation.

Q: Why is the user ID text instead of UUID? A: It holds the Airlock subject identifier that meow-web passes in the service JWT sub. Migration 000005 changed it from uuid to text when the earlier BetterAuth ids needed it; Airlock subjects use the same column.

Q: Can a link have both a uid and a custom_slug? A: Yes. Both exist simultaneously. The short URL uses custom_slug if present, otherwise falls back to uid.

Q: Why store protection_hash with json:"-"? A: This Go struct tag prevents the field from ever being serialized to JSON, ensuring the bcrypt hash never leaks through the API.

Q: How many API keys can a user have? A: There is no hard limit enforced in the schema, but the UI and service layer may impose reasonable caps per tier.

Examples

Think of a User as a gym membership card:

• Identity fields are your name and photo on the card
• Subscription fields are your membership tier (basic/premium) and renewal date
• Gamification fields are your loyalty points, visit streak, and current level
• Stats fields are the total number of times you checked in and how many guest passes you used this month

A Link is like a numbered locker:

• uid is the randomly assigned locker number
• custom_slug is a nickname you write on a sticky note
• original_url is what is actually inside the locker
• protection_type is whether the locker has a keypad, a password lock, or nothing
• redirect_mode is whether you tell people “go look in locker 42” (redirect) or you hand them the contents through a window (proxy)