rckrdmrd
3ce06fbce4
Migración desde workspace-v2/projects/template-saas/apps/database Este repositorio es parte del estándar multi-repo v2 Co-Authored-By: Claude Opus 4.5 <noreply@anthropic.com>
89 lines
3.1 KiB
SQL
89 lines
3.1 KiB
SQL
-- ============================================
|
|
-- TEMPLATE-SAAS: Auth Tokens
|
|
-- Schema: auth
|
|
-- Version: 1.0.0
|
|
-- ============================================
|
|
|
|
-- Generic tokens (password reset, email verification, etc.)
|
|
CREATE TABLE auth.tokens (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
tenant_id UUID REFERENCES tenants.tenants(id) ON DELETE CASCADE, -- NULL for pre-signup tokens
|
|
user_id UUID REFERENCES users.users(id) ON DELETE CASCADE,
|
|
|
|
-- Token data
|
|
token_hash VARCHAR(255) UNIQUE NOT NULL,
|
|
type auth.token_type NOT NULL,
|
|
|
|
-- For API keys
|
|
name VARCHAR(200), -- User-defined name
|
|
prefix VARCHAR(10), -- First chars for identification (e.g., "sk_live_")
|
|
last_used_at TIMESTAMPTZ,
|
|
|
|
-- Scope/permissions (for API keys)
|
|
scopes JSONB DEFAULT '[]'::jsonb,
|
|
|
|
-- Expiration
|
|
expires_at TIMESTAMPTZ,
|
|
is_revoked BOOLEAN DEFAULT FALSE,
|
|
revoked_at TIMESTAMPTZ,
|
|
|
|
-- Metadata
|
|
metadata JSONB DEFAULT '{}'::jsonb,
|
|
|
|
-- Audit
|
|
created_at TIMESTAMPTZ DEFAULT NOW() NOT NULL,
|
|
created_by UUID
|
|
);
|
|
|
|
-- Refresh tokens (separate for better management)
|
|
CREATE TABLE auth.refresh_tokens (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
tenant_id UUID NOT NULL REFERENCES tenants.tenants(id) ON DELETE CASCADE,
|
|
user_id UUID NOT NULL REFERENCES users.users(id) ON DELETE CASCADE,
|
|
session_id UUID REFERENCES auth.sessions(id) ON DELETE CASCADE,
|
|
|
|
-- Token
|
|
token_hash VARCHAR(255) UNIQUE NOT NULL,
|
|
|
|
-- Rotation tracking
|
|
family_id UUID NOT NULL, -- For refresh token rotation
|
|
generation INT DEFAULT 1,
|
|
|
|
-- Status
|
|
is_revoked BOOLEAN DEFAULT FALSE,
|
|
revoked_at TIMESTAMPTZ,
|
|
|
|
-- Expiration
|
|
expires_at TIMESTAMPTZ NOT NULL,
|
|
|
|
-- Audit
|
|
created_at TIMESTAMPTZ DEFAULT NOW() NOT NULL,
|
|
used_at TIMESTAMPTZ
|
|
);
|
|
|
|
-- Indexes
|
|
CREATE INDEX idx_tokens_user ON auth.tokens(user_id) WHERE NOT is_revoked;
|
|
CREATE INDEX idx_tokens_type ON auth.tokens(type) WHERE NOT is_revoked;
|
|
CREATE INDEX idx_tokens_tenant ON auth.tokens(tenant_id) WHERE NOT is_revoked;
|
|
CREATE INDEX idx_tokens_api_key ON auth.tokens(prefix) WHERE type = 'api_key' AND NOT is_revoked;
|
|
|
|
CREATE INDEX idx_refresh_tokens_user ON auth.refresh_tokens(user_id) WHERE NOT is_revoked;
|
|
CREATE INDEX idx_refresh_tokens_family ON auth.refresh_tokens(family_id);
|
|
CREATE INDEX idx_refresh_tokens_session ON auth.refresh_tokens(session_id);
|
|
|
|
-- RLS
|
|
ALTER TABLE auth.tokens ENABLE ROW LEVEL SECURITY;
|
|
ALTER TABLE auth.refresh_tokens ENABLE ROW LEVEL SECURITY;
|
|
|
|
CREATE POLICY tokens_tenant_isolation ON auth.tokens
|
|
USING (tenant_id IS NULL OR tenant_id = current_setting('app.current_tenant_id', true)::UUID);
|
|
|
|
CREATE POLICY refresh_tokens_tenant_isolation ON auth.refresh_tokens
|
|
USING (tenant_id = current_setting('app.current_tenant_id', true)::UUID);
|
|
|
|
-- Comments
|
|
COMMENT ON TABLE auth.tokens IS 'Generic tokens for various auth purposes';
|
|
COMMENT ON TABLE auth.refresh_tokens IS 'JWT refresh tokens with rotation support';
|
|
COMMENT ON COLUMN auth.tokens.prefix IS 'Visible prefix for API key identification';
|
|
COMMENT ON COLUMN auth.refresh_tokens.family_id IS 'Group ID for refresh token rotation (detect reuse)';
|