Authentication Schema Patterns for Apso
Reference patterns for designing schemas with Better Auth integration using Apso v2 format
Standard Auth Schema Pattern
Complete Multi-Tenant SaaS Schema (Apso v2)
{
"version": 2,
"rootFolder": "src",
"relationships": [
// Auth relationships
{
"from": "account",
"to": "User",
"type": "ManyToOne",
"to_name": "user"
},
{
"from": "session",
"to": "User",
"type": "ManyToOne",
"to_name": "user"
},
// Business relationships
{
"from": "OrganizationUser",
"to": "Organization",
"type": "ManyToOne",
"to_name": "organization"
},
{
"from": "OrganizationUser",
"to": "User",
"type": "ManyToOne",
"to_name": "user"
}
],
"entities": [
// ============ AUTH ENTITIES (Better Auth) ============
{
"name": "User",
"created_at": true,
"updated_at": true,
"fields": [
{ "name": "email", "type": "text", "length": 255, "unique": true },
{ "name": "email_verified", "type": "boolean", "default": false },
{ "name": "name", "type": "text", "length": 255 },
{ "name": "avatar_url", "type": "text", "nullable": true },
{ "name": "password_hash", "type": "text", "nullable": true },
{
"name": "oauth_provider",
"type": "enum",
"values": ["google", "github", "microsoft"],
"nullable": true
},
{ "name": "oauth_id", "type": "text", "nullable": true }
]
},
{
"name": "account",
"created_at": true,
"fields": [
{ "name": "accountId", "type": "text", "length": 255 },
{ "name": "providerId", "type": "text", "length": 255 },
{ "name": "accessToken", "type": "text", "nullable": true },
{ "name": "refreshToken", "type": "text", "nullable": true },
{ "name": "password", "type": "text", "nullable": true }
]
},
{
"name": "session",
"created_at": true,
"fields": [
{ "name": "sessionToken", "type": "text", "length": 255, "unique": true },
{ "name": "expiresAt", "type": "timestamp" }
]
},
{
"name": "verification",
"created_at": true,
"fields": [
{ "name": "identifier", "type": "text", "length": 255 },
{ "name": "value", "type": "text", "length": 255 },
{ "name": "expiresAt", "type": "timestamp" }
]
},
// ============ BUSINESS ENTITIES ============
{
"name": "Organization",
"created_at": true,
"updated_at": true,
"fields": [
{ "name": "name", "type": "text", "length": 255 },
{ "name": "slug", "type": "text", "length": 100, "unique": true },
{ "name": "billing_email", "type": "text", "length": 255 },
{
"name": "subscription_tier",
"type": "enum",
"values": ["free", "starter", "professional", "enterprise"],
"default": "free"
}
]
},
{
"name": "OrganizationUser",
"created_at": true,
"fields": [
{
"name": "role",
"type": "enum",
"values": ["owner", "admin", "member", "viewer"],
"default": "member"
}
]
}
]
}Common Auth Schema Patterns (Add to entities array)
1. Single Sign-On (SSO) Pattern
{
"name": "SSOProvider",
"created_at": true,
"updated_at": true,
"fields": [
{
"name": "provider",
"type": "enum",
"values": ["okta", "auth0", "azure_ad"]
},
{ "name": "client_id", "type": "text", "length": 255 },
{ "name": "client_secret", "type": "text" },
{ "name": "domain", "type": "text", "length": 255 },
{ "name": "enabled", "type": "boolean", "default": true }
]
}With relationship:
{
"from": "SSOProvider",
"to": "Organization",
"type": "ManyToOne",
"to_name": "organization"
}2. Two-Factor Authentication Pattern
{
"name": "TwoFactorAuth",
"created_at": true,
"updated_at": true,
"fields": [
{ "name": "secret", "type": "text" },
{ "name": "backup_codes", "type": "json" },
{ "name": "enabled", "type": "boolean", "default": false },
{ "name": "verified_at", "type": "timestamp", "nullable": true }
]
}With relationship:
{
"from": "TwoFactorAuth",
"to": "User",
"type": "OneToOne",
"to_name": "user"
}3. API Key Pattern
{
"name": "ApiKey",
"created_at": true,
"updated_at": true,
"fields": [
{ "name": "name", "type": "text", "length": 255 },
{ "name": "key_hash", "type": "text", "unique": true },
{ "name": "last_used_at", "type": "timestamp", "nullable": true },
{ "name": "expires_at", "type": "timestamp", "nullable": true },
{ "name": "scopes", "type": "json" }
]
}With relationships:
{
"from": "ApiKey",
"to": "Organization",
"type": "ManyToOne",
"to_name": "organization"
},
{
"from": "ApiKey",
"to": "User",
"type": "ManyToOne",
"to_name": "created_by"
}4. Audit Log Pattern
{
"name": "AuditLog",
"created_at": true,
"fields": [
{ "name": "action", "type": "text", "length": 100 },
{ "name": "resource_type", "type": "text", "length": 100 },
{ "name": "resource_id", "type": "text", "length": 100, "nullable": true },
{ "name": "ip_address", "type": "text", "length": 45, "nullable": true },
{ "name": "user_agent", "type": "text", "nullable": true },
{ "name": "metadata", "type": "json", "nullable": true }
]
}With relationships:
{
"from": "AuditLog",
"to": "Organization",
"type": "ManyToOne",
"to_name": "organization"
},
{
"from": "AuditLog",
"to": "User",
"type": "ManyToOne",
"to_name": "user",
"nullable": true
}5. Permission System Pattern
{
"name": "Role",
"created_at": true,
"updated_at": true,
"fields": [
{ "name": "name", "type": "text", "length": 100 },
{ "name": "description", "type": "text", "nullable": true },
{ "name": "permissions", "type": "json" },
{ "name": "is_system", "type": "boolean", "default": false }
]
},
{
"name": "UserRole",
"created_at": true,
"fields": [
{ "name": "granted_at", "type": "timestamp" }
]
}With relationships:
{
"from": "Role",
"to": "Organization",
"type": "ManyToOne",
"to_name": "organization"
},
{
"from": "UserRole",
"to": "User",
"type": "ManyToOne",
"to_name": "user"
},
{
"from": "UserRole",
"to": "Role",
"type": "ManyToOne",
"to_name": "role"
},
{
"from": "UserRole",
"to": "Organization",
"type": "ManyToOne",
"to_name": "organization"
}Entity Naming Rules
Reserved Names (Better Auth)
| Entity | Must Be | Table Name |
|---|---|---|
| User | User (PascalCase) |
user |
| Account | account (lowercase) |
account |
| Session | session (lowercase) |
session |
| Verification | verification (lowercase) |
verification |
Business Entity Alternatives
| Avoid | Use Instead |
|---|---|
| Account | Organization, Company, Workspace, Tenant |
| Session | UserSession, DiscoverySession, ChatSession |
| Verification | EmailConfirmation, TokenVerification |
Field Requirements (Apso v2)
Always Nullable (OAuth Support)
In Apso v2, add "nullable": true to field definitions:
{ "name": "avatar_url", "type": "text", "nullable": true },
{ "name": "password_hash", "type": "text", "nullable": true },
{ "name": "oauth_provider", "type": "enum", "values": [...], "nullable": true },
{ "name": "oauth_id", "type": "text", "nullable": true },
{ "name": "accessToken", "type": "text", "nullable": true },
{ "name": "refreshToken", "type": "text", "nullable": true }Never Nullable (Required)
These fields should NOT have "nullable": true:
{ "name": "email", "type": "text", "length": 255, "unique": true },
{ "name": "sessionToken", "type": "text", "length": 255, "unique": true },
{ "name": "expiresAt", "type": "timestamp" }Unique Constraints (Apso v2)
Add unique constraint directly to field or use indexes:
// Single field unique
{ "name": "email", "type": "text", "unique": true }
// Or using unique property on field
{ "name": "sessionToken", "type": "text", "unique": true }For composite unique constraints, add to entity definition (note: Apso v2 may require custom implementation)
Migration Checklist
When adding auth to existing schema:
- Rename conflicting entities (Account → Organization)
- Add User entity with nullable OAuth fields
- Add account, session, verification entities (lowercase)
- Create junction table (OrganizationUser)
- Update foreign key references
- Add appropriate indexes
- Test with Better Auth adapter
Common Mistakes to Avoid
- ❌ Using
Accountfor business entity - ❌ Not making OAuth fields nullable
- ❌ Using wrong case for auth entities
- ❌ Missing id field in DTOs
- ❌ Not adding unique constraints
- ❌ Forgetting CASCADE on delete
- ❌ Missing organization_id in multi-tenant entities