13 — Users
What it is: Fan account data — profile, sessions (JWT), account group membership (gating), credit balance, and card token. The last two are wired but not active yet.
Tables
tbl_users
| Field | Notes |
|---|---|
userId | PK (internal) |
firstName / lastName | profile name — both NOT NULL |
email | unique (uq_user_email, a plain btree on the raw column). Case-insensitivity is enforced in code (emails lowercased on insert/compare), not by a citext/functional index |
phoneNumber | optional |
accountId | unique vendor-side identifier; 1:1 with email when present |
isActive | false = banned/deactivated; reason stored in deactivationReason (never shown to user) |
deactivatedAt | timestamp set when the account is deactivated (nullable; pairs with isActive=false) |
stripeCustomerId | created once per user at first TM Host checkout. NULL for TM Archtics and TDC (one vendor per schema) |
loginCount / lastLoginAt | tracking fields |
tbl_user_sessions
JWT session management. Refresh tokens are stored as bcrypt hashes — never plain.
| Field | Notes |
|---|---|
sessionId | UUID PK |
refreshTokenHash | hashed refresh token |
expiresAt | default 30 days (configured in authSettings) |
isActive | set to false on logout |
ipAddress / userAgent | audit trail |
Session lifecycle: create on login → update lastActivityAt on each token refresh → set isActive = false on logout. Expired sessions are rejected at request time (expiresAt > NOW()); a scheduled job to purge old rows is future scope (recommended daily — not yet implemented).
Auth flow: on each request → verify JWT → check session.isActive → check session.expiresAt > NOW().
tbl_user_account_groups
Links a user to account group IDs. accountGroupId is a plain varchar with no FK — account groups come from the vendor (there's no local lookup table), so vendor data can change without breaking users.
Used for: gating (which users can access which inventory). Not active yet.
tbl_user_account_credit
Composite PK: (userId, ledgerCode) — credit balance per user per ledger.
| Field | Notes |
|---|---|
accountCredit | decimal(9,2) — max $9,999,999.99 |
ledgerCode | plain varchar code (no FK — there's no tbl_ledgers; the ledger is a config key on the program); credits don't cross ledgers |
Not active yet. Real-world example: Knicks fan buys $5,000 credit → placed in $5k account group → pays for tickets with that credit using per-group pricing.
What about card data?
Card details are NOT stored here — they're tokenized by the vendor (Stripe → cus_xxx token; TM → CC sequence ID). Attend stores only the token, brand, last 4, and card type (in a separate card table). PCI compliance stays with the vendor.
Key rules
stripeCustomerIdis null for Archtics and TDC schemas — don't assume it's always set- Refresh tokens are always hashed before storage — never log or return the raw token
- Account groups and credit are wired in the schema but not yet used in the purchase flow
- Sessions cascade-delete when a user is deleted