data-model

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

UUID PKs everywhere except SystemSetting (string) and SchemaMigration (uint)
Multi-tenancy via AppID on User, SocialAccount, ActivityLog, Role, OAuthProviderConfig, EmailServerConfig, EmailTemplate, ApiKey, WebAuthnCredential
Default sentinel AppID: 00000000-0000-0000-0000-000000000001
Sensitive field hiding: All secrets use json:"-"
Composite unique indexes enforce per-app uniqueness
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.

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

Entity Relationship Diagram

Models

User (pkg/models/user.go)

Tenant (pkg/models/tenant.go)

Application (pkg/models/application.go)

Role (pkg/models/role.go)

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

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

AdminAccount (pkg/models/admin_account.go)

SocialAccount (pkg/models/social_account.go)

WebAuthnCredential (pkg/models/webauthn_credential.go)

ActivityLog (pkg/models/activity_log.go)

ApiKey (pkg/models/api_key.go)

EmailType (pkg/models/email_type.go)

EmailTemplate (pkg/models/email_template.go)

EmailServerConfig (pkg/models/email_server_config.go)

OAuthProviderConfig (pkg/models/oauth_provider_config.go)

SystemSetting (pkg/models/system_setting.go)

SchemaMigration (pkg/models/schema_migration.go)