server/db/schema/auth-tables.ts
Rama main · réplica de lectura del repositorio original.
import { sql } from 'drizzle-orm'
import { index, integer, sqliteTable, text, uniqueIndex } from 'drizzle-orm/sqlite-core'
import type { AccountProfileLink } from '../../../shared/api/public-profiles'
export const user = sqliteTable('user', {
id: text('id').primaryKey(),
deletedAt: integer('deleted_at', { mode: 'timestamp_ms' }),
name: text('name').notNull(),
username: text('username').unique(),
displayUsername: text('display_username'),
email: text('email').notNull().unique(),
emailVerified: integer('email_verified', { mode: 'boolean' }).default(false).notNull(),
twoFactorEnabled: integer('two_factor_enabled', { mode: 'boolean' }).default(false),
image: text('image'),
avatarAssetId: text('avatar_asset_id'),
role: text('role'),
banned: integer('banned', { mode: 'boolean' }).default(false),
banReason: text('ban_reason'),
banExpires: integer('ban_expires', { mode: 'timestamp_ms' }),
createdAt: integer('created_at', { mode: 'timestamp_ms' })
.default(sql`(cast(unixepoch('subsecond') * 1000 as integer))`)
.notNull(),
updatedAt: integer('updated_at', { mode: 'timestamp_ms' })
.default(sql`(cast(unixepoch('subsecond') * 1000 as integer))`)
.$onUpdate(() => new Date())
.notNull(),
})
export const userProfile = sqliteTable('user_profile', {
userId: text('user_id')
.primaryKey()
.references(() => user.id, { onDelete: 'cascade' }),
bio: text('bio'),
location: text('location'),
links: text('links', { mode: 'json' }).$type<AccountProfileLink[]>().notNull().default([]),
createdAt: integer('created_at', { mode: 'timestamp_ms' })
.default(sql`(cast(unixepoch('subsecond') * 1000 as integer))`)
.notNull(),
updatedAt: integer('updated_at', { mode: 'timestamp_ms' })
.default(sql`(cast(unixepoch('subsecond') * 1000 as integer))`)
.$onUpdate(() => new Date())
.notNull(),
})
export const session = sqliteTable(
'session',
{
id: text('id').primaryKey(),
expiresAt: integer('expires_at', { mode: 'timestamp_ms' }).notNull(),
token: text('token').notNull().unique(),
createdAt: integer('created_at', { mode: 'timestamp_ms' })
.default(sql`(cast(unixepoch('subsecond') * 1000 as integer))`)
.notNull(),
updatedAt: integer('updated_at', { mode: 'timestamp_ms' })
.default(sql`(cast(unixepoch('subsecond') * 1000 as integer))`)
.$onUpdate(() => new Date())
.notNull(),
ipAddress: text('ip_address'),
userAgent: text('user_agent'),
activeOrganizationId: text('active_organization_id'),
activeTeamId: text('active_team_id'),
userId: text('user_id')
.notNull()
.references(() => user.id, { onDelete: 'cascade' }),
impersonatedBy: text('impersonated_by'),
},
(table) => [index('session_userId_idx').on(table.userId)],
)
export const passwordResetRequest = sqliteTable(
'password_reset_request',
{
id: text('id').primaryKey(),
userId: text('user_id')
.notNull()
.references(() => user.id, { onDelete: 'cascade' }),
status: text('status').notNull(),
createdAt: integer('created_at', { mode: 'timestamp_ms' }).notNull(),
},
(table) => [index('passwordResetRequest_userId_idx').on(table.userId)],
)
export const account = sqliteTable(
'account',
{
id: text('id').primaryKey(),
accountId: text('account_id').notNull(),
providerId: text('provider_id').notNull(),
userId: text('user_id')
.notNull()
.references(() => user.id, { onDelete: 'cascade' }),
accessToken: text('access_token'),
refreshToken: text('refresh_token'),
idToken: text('id_token'),
accessTokenExpiresAt: integer('access_token_expires_at', { mode: 'timestamp_ms' }),
refreshTokenExpiresAt: integer('refresh_token_expires_at', { mode: 'timestamp_ms' }),
scope: text('scope'),
password: text('password'),
createdAt: integer('created_at', { mode: 'timestamp_ms' })
.default(sql`(cast(unixepoch('subsecond') * 1000 as integer))`)
.notNull(),
updatedAt: integer('updated_at', { mode: 'timestamp_ms' })
.default(sql`(cast(unixepoch('subsecond') * 1000 as integer))`)
.$onUpdate(() => new Date())
.notNull(),
},
(table) => [
uniqueIndex('account_providerId_accountId_unique').on(table.providerId, table.accountId),
index('account_userId_idx').on(table.userId),
],
)
export const verification = sqliteTable(
'verification',
{
id: text('id').primaryKey(),
identifier: text('identifier').notNull(),
value: text('value').notNull(),
expiresAt: integer('expires_at', { mode: 'timestamp_ms' }).notNull(),
createdAt: integer('created_at', { mode: 'timestamp_ms' })
.default(sql`(cast(unixepoch('subsecond') * 1000 as integer))`)
.notNull(),
updatedAt: integer('updated_at', { mode: 'timestamp_ms' })
.default(sql`(cast(unixepoch('subsecond') * 1000 as integer))`)
.$onUpdate(() => new Date())
.notNull(),
},
(table) => [index('verification_identifier_idx').on(table.identifier)],
)
export const jwks = sqliteTable('jwks', {
id: text('id').primaryKey(),
publicKey: text('public_key').notNull(),
privateKey: text('private_key').notNull(),
alg: text('alg'),
crv: text('crv'),
createdAt: integer('created_at', { mode: 'timestamp_ms' })
.default(sql`(cast(unixepoch('subsecond') * 1000 as integer))`)
.notNull(),
expiresAt: integer('expires_at', { mode: 'timestamp_ms' }),
})
export const twoFactor = sqliteTable(
'two_factor',
{
id: text('id').primaryKey(),
secret: text('secret').notNull(),
backupCodes: text('backup_codes').notNull(),
userId: text('user_id')
.notNull()
.references(() => user.id, { onDelete: 'cascade' }),
verified: integer('verified', { mode: 'boolean' }).default(true),
failedVerificationCount: integer('failed_verification_count').default(0),
lockedUntil: integer('locked_until', { mode: 'timestamp_ms' }),
},
(table) => [index('twoFactor_secret_idx').on(table.secret), index('twoFactor_userId_idx').on(table.userId)],
)
export const passkey = sqliteTable(
'passkey',
{
id: text('id').primaryKey(),
name: text('name'),
publicKey: text('public_key').notNull(),
userId: text('user_id')
.notNull()
.references(() => user.id, { onDelete: 'cascade' }),
credentialID: text('credential_id').notNull(),
counter: integer('counter').notNull(),
deviceType: text('device_type').notNull(),
backedUp: integer('backed_up', { mode: 'boolean' }).notNull(),
transports: text('transports'),
createdAt: integer('created_at', { mode: 'timestamp_ms' }),
aaguid: text('aaguid'),
},
(table) => [index('passkey_userId_idx').on(table.userId), index('passkey_credentialID_idx').on(table.credentialID)],
)
export const walletAddress = sqliteTable(
'wallet_address',
{
id: text('id').primaryKey(),
userId: text('user_id')
.notNull()
.references(() => user.id, { onDelete: 'cascade' }),
address: text('address').notNull(),
chainId: integer('chain_id').notNull(),
isPrimary: integer('is_primary', { mode: 'boolean' }).default(false),
createdAt: integer('created_at', { mode: 'timestamp_ms' })
.default(sql`(cast(unixepoch('subsecond') * 1000 as integer))`)
.notNull(),
},
(table) => [
index('walletAddress_userId_idx').on(table.userId),
uniqueIndex('walletAddress_address_chainId_unique').on(table.address, table.chainId),
],
)
export const oauthClient = sqliteTable(
'oauth_client',
{
id: text('id').primaryKey(),
clientDiscoveryId: text('client_discovery_id'),
clientCredentialsScopes: text('client_credentials_scopes').default('[]'),
backchannelLogoutUri: text('backchannel_logout_uri'),
backchannelLogoutSessionRequired: integer('backchannel_logout_session_required', { mode: 'boolean' }),
applicationType: text('application_type'),
jwks: text('jwks'),
jwksUri: text('jwks_uri'),
dpopBoundAccessTokens: integer('dpop_bound_access_tokens', { mode: 'boolean' }).default(false),
clientId: text('client_id').notNull().unique(),
clientSecret: text('client_secret'),
disabled: integer('disabled', { mode: 'boolean' }).default(false),
skipConsent: integer('skip_consent', { mode: 'boolean' }),
enableEndSession: integer('enable_end_session', { mode: 'boolean' }),
subjectType: text('subject_type'),
scopes: text('scopes'),
userId: text('user_id').references(() => user.id, { onDelete: 'cascade' }),
createdAt: integer('created_at', { mode: 'timestamp_ms' })
.default(sql`(cast(unixepoch('subsecond') * 1000 as integer))`)
.notNull(),
updatedAt: integer('updated_at', { mode: 'timestamp_ms' })
.default(sql`(cast(unixepoch('subsecond') * 1000 as integer))`)
.$onUpdate(() => new Date())
.notNull(),
name: text('name'),
uri: text('uri'),
icon: text('icon'),
contacts: text('contacts'),
tos: text('tos'),
policy: text('policy'),
softwareId: text('software_id'),
softwareVersion: text('software_version'),
softwareStatement: text('software_statement'),
redirectUris: text('redirect_uris').notNull(),
postLogoutRedirectUris: text('post_logout_redirect_uris'),
tokenEndpointAuthMethod: text('token_endpoint_auth_method'),
grantTypes: text('grant_types'),
responseTypes: text('response_types'),
public: integer('public', { mode: 'boolean' }),
type: text('type'),
requirePKCE: integer('require_pkce', { mode: 'boolean' }),
referenceId: text('reference_id'),
metadata: text('metadata'),
},
(table) => [
index('oauthClient_userId_idx').on(table.userId),
index('oauthClient_referenceId_idx').on(table.referenceId),
],
)
export const oauthRefreshToken = sqliteTable(
'oauth_refresh_token',
{
id: text('id').primaryKey(),
authorizationCodeId: text('authorization_code_id'),
requestedUserInfoClaims: text('requested_user_info_claims'),
rotatedAt: integer('rotated_at', { mode: 'timestamp_ms' }),
rotationReplayResponse: text('rotation_replay_response'),
rotationReplayExpiresAt: integer('rotation_replay_expires_at', { mode: 'timestamp_ms' }),
confirmation: text('confirmation'),
token: text('token').notNull().unique(),
clientId: text('client_id')
.notNull()
.references(() => oauthClient.clientId, { onDelete: 'cascade' }),
sessionId: text('session_id').references(() => session.id, { onDelete: 'set null' }),
userId: text('user_id')
.notNull()
.references(() => user.id, { onDelete: 'cascade' }),
referenceId: text('reference_id'),
expiresAt: integer('expires_at', { mode: 'timestamp_ms' }).notNull(),
createdAt: integer('created_at', { mode: 'timestamp_ms' })
.default(sql`(cast(unixepoch('subsecond') * 1000 as integer))`)
.notNull(),
revoked: integer('revoked', { mode: 'timestamp_ms' }),
authTime: integer('auth_time', { mode: 'timestamp_ms' }),
scopes: text('scopes').notNull(),
resources: text('resources'),
},
(table) => [
index('oauthRefreshToken_authorizationCodeId_idx').on(table.authorizationCodeId),
index('oauthRefreshToken_clientId_idx').on(table.clientId),
index('oauthRefreshToken_sessionId_idx').on(table.sessionId),
index('oauthRefreshToken_userId_idx').on(table.userId),
],
)
export const oauthAccessToken = sqliteTable(
'oauth_access_token',
{
id: text('id').primaryKey(),
authorizationCodeId: text('authorization_code_id'),
resources: text('resources'),
requestedUserInfoClaims: text('requested_user_info_claims'),
revoked: integer('revoked', { mode: 'timestamp_ms' }),
confirmation: text('confirmation'),
token: text('token').notNull().unique(),
clientId: text('client_id')
.notNull()
.references(() => oauthClient.clientId, { onDelete: 'cascade' }),
sessionId: text('session_id').references(() => session.id, { onDelete: 'set null' }),
userId: text('user_id').references(() => user.id, { onDelete: 'cascade' }),
referenceId: text('reference_id'),
refreshId: text('refresh_id').references(() => oauthRefreshToken.id, { onDelete: 'cascade' }),
expiresAt: integer('expires_at', { mode: 'timestamp_ms' }).notNull(),
createdAt: integer('created_at', { mode: 'timestamp_ms' })
.default(sql`(cast(unixepoch('subsecond') * 1000 as integer))`)
.notNull(),
scopes: text('scopes').notNull(),
},
(table) => [
index('oauthAccessToken_authorizationCodeId_idx').on(table.authorizationCodeId),
index('oauthAccessToken_clientId_idx').on(table.clientId),
index('oauthAccessToken_sessionId_idx').on(table.sessionId),
index('oauthAccessToken_userId_idx').on(table.userId),
index('oauthAccessToken_refreshId_idx').on(table.refreshId),
],
)
export const oauthConsent = sqliteTable(
'oauth_consent',
{
id: text('id').primaryKey(),
resources: text('resources'),
requestedUserInfoClaims: text('requested_user_info_claims'),
clientId: text('client_id')
.notNull()
.references(() => oauthClient.clientId, { onDelete: 'cascade' }),
userId: text('user_id').references(() => user.id, { onDelete: 'cascade' }),
referenceId: text('reference_id'),
scopes: text('scopes').notNull(),
createdAt: integer('created_at', { mode: 'timestamp_ms' })
.default(sql`(cast(unixepoch('subsecond') * 1000 as integer))`)
.notNull(),
updatedAt: integer('updated_at', { mode: 'timestamp_ms' })
.default(sql`(cast(unixepoch('subsecond') * 1000 as integer))`)
.$onUpdate(() => new Date())
.notNull(),
},
(table) => [
index('oauthConsent_clientId_idx').on(table.clientId),
index('oauthConsent_userId_idx').on(table.userId),
index('oauthConsent_referenceId_idx').on(table.referenceId),
uniqueIndex('oauthConsent_clientUser_default_unique')
.on(table.clientId, table.userId)
.where(sql`${table.userId} is not null and ${table.referenceId} is null`),
uniqueIndex('oauthConsent_clientUserReference_unique')
.on(table.clientId, table.userId, table.referenceId)
.where(sql`${table.userId} is not null and ${table.referenceId} is not null`),
],
)
export const deviceCode = sqliteTable(
'device_code',
{
id: text('id').primaryKey(),
resources: text('resources'),
oauthClientId: text('oauth_client_id').references(() => oauthClient.clientId, { onDelete: 'cascade' }),
deviceCode: text('device_code').notNull().unique(),
userCode: text('user_code').notNull().unique(),
userId: text('user_id').references(() => user.id, { onDelete: 'cascade' }),
expiresAt: integer('expires_at', { mode: 'timestamp_ms' }).notNull(),
status: text('status').notNull(),
lastPolledAt: integer('last_polled_at', { mode: 'timestamp_ms' }),
pollingInterval: integer('polling_interval'),
clientId: text('client_id').references(() => oauthClient.clientId, { onDelete: 'cascade' }),
scope: text('scope'),
},
(table) => [
index('deviceCode_deviceCode_idx').on(table.deviceCode),
index('deviceCode_userCode_idx').on(table.userCode),
index('deviceCode_clientId_idx').on(table.clientId),
index('deviceCode_userId_idx').on(table.userId),
index('deviceCode_expiresAt_idx').on(table.expiresAt),
],
)
export const oauthResource = sqliteTable('oauth_resource', {
id: text('id').primaryKey(),
identifier: text('identifier').unique().notNull(),
name: text('name').notNull(),
accessTokenTtl: integer('access_token_ttl'),
refreshTokenTtl: integer('refresh_token_ttl'),
signingAlgorithm: text('signing_algorithm'),
signingKeyId: text('signing_key_id'),
allowedScopes: text('allowed_scopes'),
customClaims: text('custom_claims'),
dpopBoundAccessTokensRequired: integer('dpop_bound_access_tokens_required', { mode: 'boolean' }).default(false),
disabled: integer('disabled', { mode: 'boolean' }).default(false),
createdAt: integer('created_at', { mode: 'timestamp_ms' }),
updatedAt: integer('updated_at', { mode: 'timestamp_ms' }),
policyVersion: integer('policy_version').default(1),
metadata: text('metadata'),
})
export const oauthClientResource = sqliteTable(
'oauth_client_resource',
{
id: text('id').primaryKey(),
clientId: text('client_id')
.notNull()
.references(() => oauthClient.clientId, { onDelete: 'cascade' }),
resourceId: text('resource_id')
.notNull()
.references(() => oauthResource.id, { onDelete: 'cascade' }),
metadata: text('metadata'),
createdAt: integer('created_at', { mode: 'timestamp_ms' }),
},
(table) => [
uniqueIndex('oauthClientResource_clientResource_unique').on(table.clientId, table.resourceId),
index('oauthClientResource_resourceId_idx').on(table.resourceId),
],
)
export const oauthClientAssertion = sqliteTable('oauth_client_assertion', {
id: text('id').primaryKey(),
expiresAt: integer('expires_at', { mode: 'timestamp_ms' }).notNull(),
})