---
name: data-model
description: All 17 GORM database models with fields, relationships, indexes, and the entity relationship diagram for the Auth API.
license: MIT
---

## Overview

All models are in `pkg/models/`. The project uses PostgreSQL with GORM. All models except SystemSetting and SchemaMigration use UUID primary keys (`gen_random_uuid()`). Sensitive fields use `json:"-"` to prevent API exposure.

## Entity Relationship Diagram

```
Tenant
  |-- has-many --> Application
                      |-- has-many --> OAuthProviderConfig
                      |-- has-one  --> EmailServerConfig (nullable AppID)
                      |-- has-many --> ApiKey (ON DELETE CASCADE)
                      |-- has-many --> Role (via AppID)
                      |-- has-many --> EmailTemplate (nullable AppID)
                      |-- has-many --> WebAuthnCredential (nullable AppID)

User (scoped to AppID)
  |-- has-many --> SocialAccount
  |-- has-many --> UserRole
  |-- has-many --> ActivityLog (implicit)
  |-- has-many --> WebAuthnCredential (nullable UserID)

Role (scoped to AppID)
  |-- many-to-many --> Permission (join table: role_permissions)

UserRole (join table)
  |-- belongs-to --> User
  |-- belongs-to --> Role

EmailTemplate
  |-- belongs-to --> EmailType
  |-- belongs-to --> EmailServerConfig (nullable)

AdminAccount (standalone, system-level)
  |-- has-many --> WebAuthnCredential (via AdminID)

SystemSetting (standalone key-value store)
SchemaMigration (standalone migration tracker)
```

## Models

### User (`pkg/models/user.go`)

Table: `users`

| Field | Type | Key Tags | Notes |
|-------|------|----------|-------|
| ID | uuid.UUID | `primaryKey` | |
| AppID | uuid.UUID | `uniqueIndex:idx_email_app_id` | Default: `00000000-...0001` |
| Email | string | `uniqueIndex:idx_email_app_id` | Composite unique with AppID |
| PasswordHash | string | | `json:"-"` |
| EmailVerified | bool | | Default: false |
| IsActive | bool | | Default: true |
| Name, FirstName, LastName | string | | |
| ProfilePicture | string | | URL from social login |
| Locale | string | | |
| TwoFAEnabled | bool | | |
| TwoFAMethod | string | | "totp" or "email" |
| TwoFASecret | string | | `json:"-"` encrypted |
| TwoFARecoveryCodes | datatypes.JSON | `type:jsonb` | `json:"-"` encrypted |
| SocialAccounts | []SocialAccount | `foreignKey:UserID` | Has-Many |

### Tenant (`pkg/models/tenant.go`)

Table: `tenants`

| Field | Type | Notes |
|-------|------|-------|
| ID | uuid.UUID | |
| Name | string | |
| Apps | []Application | `foreignKey:TenantID` Has-Many |

### Application (`pkg/models/application.go`)

Table: `applications`

| Field | Type | Notes |
|-------|------|-------|
| ID | uuid.UUID | |
| TenantID | uuid.UUID | FK to Tenant |
| Name | string | |
| Description | string | |
| TwoFAIssuerName | string | Custom name in authenticator apps |
| TwoFAEnabled | bool | Master switch for 2FA |
| TwoFARequired | bool | Force all users to set up 2FA |
| Email2FAEnabled | bool | Email-based 2FA toggle |
| Passkey2FAEnabled | bool | Passkey as 2FA method |
| PasskeyLoginEnabled | bool | Passwordless login via passkey |
| MagicLinkEnabled | bool | Passwordless login via magic link |
| TwoFAMethods | string | Comma-separated: "totp", "email", "passkey" |
| OAuthProviderConfigs | []OAuthProviderConfig | `foreignKey:AppID` Has-Many |
| EmailServerConfig | *EmailServerConfig | `foreignKey:AppID` Has-One |

### Role (`pkg/models/role.go`)

Table: `roles`

| Field | Type | Notes |
|-------|------|-------|
| ID | uuid.UUID | |
| AppID | uuid.UUID | `uniqueIndex:idx_role_app_name` |
| Name | string | `uniqueIndex:idx_role_app_name` |
| Description | string | |
| IsSystem | bool | Cannot be deleted |
| Permissions | []Permission | `many2many:role_permissions` |

### Permission (`pkg/models/role.go`, same file as Role)

Table: `permissions`

| Field | Type | Notes |
|-------|------|-------|
| ID | uuid.UUID | |
| Resource | string | `uniqueIndex:idx_permission_resource_action` |
| Action | string | `uniqueIndex:idx_permission_resource_action` |
| Description | string | |

### UserRole (`pkg/models/role.go`, same file)

Table: `user_roles`

| Field | Type | Notes |
|-------|------|-------|
| UserID | uuid.UUID | Composite PK with RoleID |
| RoleID | uuid.UUID | Composite PK with UserID |
| AppID | uuid.UUID | Denormalized for fast lookup |
| AssignedAt | time.Time | |
| AssignedBy | *uuid.UUID | Nullable |
| Role | Role | Belongs-To |
| User | User | Belongs-To |

### AdminAccount (`pkg/models/admin_account.go`)

Table: `admin_accounts` (explicit TableName())

| Field | Type | Notes |
|-------|------|-------|
| ID | uuid.UUID | |
| Username | string | `uniqueIndex` |
| Email | string | `uniqueIndex` |
| PasswordHash | string | `json:"-"` |
| LastLoginAt | *time.Time | |
| TwoFAEnabled, TwoFAMethod | | Same pattern as User |
| TwoFASecret, TwoFARecoveryCodes | | `json:"-"` |
| MagicLinkEnabled | bool | |

Standalone entity -- not scoped to any application.

### SocialAccount (`pkg/models/social_account.go`)

Table: `social_accounts`

| Field | Type | Notes |
|-------|------|-------|
| ID | uuid.UUID | |
| AppID | uuid.UUID | `uniqueIndex:idx_provider_user_id_app_id` |
| UserID | uuid.UUID | FK to User |
| Provider | string | "google", "facebook", "github" |
| ProviderUserID | string | `uniqueIndex:idx_provider_user_id_app_id` |
| Email, Name, FirstName, LastName | string | From provider |
| Username | string | e.g., GitHub login |
| RawData | datatypes.JSON | Complete raw JSON from provider |
| AccessToken, RefreshToken | string | `json:"-"` |

Composite unique: `(AppID, Provider, ProviderUserID)`

### WebAuthnCredential (`pkg/models/webauthn_credential.go`)

Table: `web_authn_credentials`

| Field | Type | Notes |
|-------|------|-------|
| ID | uuid.UUID | |
| UserID | *uuid.UUID | Set for regular users (nullable) |
| AppID | *uuid.UUID | Set for regular users (nullable) |
| AdminID | *uuid.UUID | Set for admin passkeys (nullable) |
| CredentialID | []byte | `uniqueIndex`, `json:"-"` |
| PublicKey | []byte | `json:"-"` |
| Name | string | User-friendly label |
| Transports | string | Comma-separated: "usb,ble,nfc,internal" |

Polymorphic ownership: regular users use `UserID+AppID`, admins use `AdminID`.

### ActivityLog (`pkg/models/activity_log.go`)

Table: `activity_logs` (explicit TableName())

| Field | Type | Notes |
|-------|------|-------|
| ID | uuid.UUID | |
| AppID | uuid.UUID | Default: sentinel UUID |
| UserID | uuid.UUID | `index:idx_user_timestamp`, `index:idx_cleanup` |
| EventType | string | Indexed |
| Timestamp | time.Time | |
| IPAddress, UserAgent | string | |
| Details | json.RawMessage | `type:jsonb` |
| Severity | string | CRITICAL, IMPORTANT, INFORMATIONAL |
| ExpiresAt | *time.Time | `index:idx_expires` |
| IsAnomaly | bool | |

### ApiKey (`pkg/models/api_key.go`)

Table: `api_keys` (explicit TableName())

| Field | Type | Notes |
|-------|------|-------|
| ID | uuid.UUID | |
| KeyType | string | "admin" or "app" |
| Name, Description | string | |
| KeyHash | string | SHA-256, `uniqueIndex`, `json:"-"` |
| KeyPrefix | string | First 8 chars for display |
| KeySuffix | string | Last 4 chars |
| AppID | *uuid.UUID | Required when `key_type = "app"` |
| ExpiresAt | *time.Time | Optional |
| IsRevoked | bool | |
| Application | *Application | `ON DELETE CASCADE` |

### EmailType (`pkg/models/email_type.go`)

Table: `email_types` (explicit TableName())

| Field | Type | Notes |
|-------|------|-------|
| ID | uuid.UUID | |
| Code | string | `uniqueIndex` (e.g., "email_verification") |
| Name | string | |
| DefaultSubject | string | |
| Variables | datatypes.JSON | Array of EmailTypeVariable structs |
| IsSystem | bool | System types cannot be deleted |
| IsActive | bool | |

### EmailTemplate (`pkg/models/email_template.go`)

Table: `email_templates` (explicit TableName())

| Field | Type | Notes |
|-------|------|-------|
| ID | uuid.UUID | |
| AppID | *uuid.UUID | NULL = global default |
| EmailTypeID | uuid.UUID | `uniqueIndex:idx_app_email_type` |
| Subject, BodyHTML, BodyText | string | |
| TemplateEngine | string | "go_template", "placeholder", "raw_html" |
| FromEmail, FromName | string | Optional sender override |
| ServerConfigID | *uuid.UUID | Optional FK to EmailServerConfig |
| EmailType | EmailType | Belongs-To |
| ServerConfig | *EmailServerConfig | Belongs-To |

Composite unique: `(AppID, EmailTypeID)` -- one template per type per app scope.

### EmailServerConfig (`pkg/models/email_server_config.go`)

Table: `email_server_configs` (explicit TableName())

| Field | Type | Notes |
|-------|------|-------|
| ID | uuid.UUID | |
| AppID | *uuid.UUID | NULL = global/system config |
| Name | string | Label (e.g., "Transactional") |
| SMTPHost, SMTPPort | string, int | |
| SMTPUsername, SMTPPassword | string | Password: `json:"-"` |
| FromAddress, FromName | string | |
| UseTLS | bool | Default: true |
| IsDefault | bool | One default per scope |
| IsActive | bool | |

### OAuthProviderConfig (`pkg/models/oauth_provider_config.go`)

Table: `oauth_provider_configs` (explicit TableName())

| Field | Type | Notes |
|-------|------|-------|
| ID | uuid.UUID | |
| AppID | uuid.UUID | `uniqueIndex:idx_app_provider` |
| Provider | string | "google", "facebook", "github" |
| ClientID | string | |
| ClientSecret | string | `json:"-"` |
| RedirectURL | string | |
| IsEnabled | bool | |

### SystemSetting (`pkg/models/system_setting.go`)

Table: `system_settings` (explicit TableName())

| Field | Type | Notes |
|-------|------|-------|
| Key | string | **String primary key** (matches env var name) |
| Value | string | |
| Category | string | Indexed |

Resolution: env var > DB value > hardcoded default.

### SchemaMigration (`pkg/models/schema_migration.go`)

Table: `schema_migrations` (explicit TableName())

| Field | Type | Notes |
|-------|------|-------|
| ID | uint | **Auto-increment PK** (only model not using UUID) |
| Version | string | `uniqueIndex`, YYYYMMDD_HHMMSS format |
| Name | string | |
| ExecutionTimeMs | int | |
| Success | bool | |
| Checksum | string | SHA256 of migration file |

## Cross-Cutting Patterns

1. **UUID PKs everywhere** except SystemSetting (string) and SchemaMigration (uint)
2. **Multi-tenancy via AppID** on User, SocialAccount, ActivityLog, Role, OAuthProviderConfig, EmailServerConfig, EmailTemplate, ApiKey, WebAuthnCredential
3. **Default sentinel AppID:** `00000000-0000-0000-0000-000000000001`
4. **Sensitive field hiding:** All secrets use `json:"-"`
5. **Composite unique indexes** enforce per-app uniqueness
6. **Nullable AppID** enables global-vs-app-specific resolution (EmailTemplate, EmailServerConfig)

## When To Use This Skill

Load this skill when working on database models, migrations, repository queries, or any feature that touches data persistence.