173 lines
7.3 KiB
SQL
173 lines
7.3 KiB
SQL
-- =============================================================================
|
||
-- AURGANIZE V6.2 - SESSIONS TABLE (USER-ISOLATED, NO MULTI-TENANCY)
|
||
-- =============================================================================
|
||
-- Migration: 000002_add_sessions
|
||
-- Description: Creates sessions table for JWT refresh token lifecycle
|
||
-- Author: Aurganize Team
|
||
-- Date: 2025-12-11
|
||
-- Version: 2.1 (Aligned to Go Model, Tenant-less RLS Edition)
|
||
-- =============================================================================
|
||
-- This migration creates the sessions table exactly matching Go models.Session.
|
||
-- Multi-tenant isolation is intentionally removed (no tenant_id column).
|
||
-- RLS still protects session rows, ensuring users cannot see other users’ data.
|
||
-- =============================================================================
|
||
|
||
|
||
-- =============================================================================
|
||
-- SECTION 1: SESSIONS TABLE
|
||
-- =============================================================================
|
||
|
||
CREATE TABLE sessions (
|
||
-- ======================================================================
|
||
-- IDENTITY COLUMNS
|
||
-- ======================================================================
|
||
id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
|
||
-- Purpose: Unique identifier for a session
|
||
-- Example: "a3bb189e-8bf9-4558-93c9-62cd9c8b9e5e"
|
||
|
||
user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
|
||
-- Purpose: Maps each session to a specific user
|
||
-- Cascade delete ensures all user sessions are removed when user is deleted
|
||
|
||
-- ======================================================================
|
||
-- AUTHENTICATION TOKENS
|
||
-- ======================================================================
|
||
refresh_token_hash TEXT NOT NULL,
|
||
-- Purpose: Stores bcrypt/SHA256 hash of refresh token (never plaintext)
|
||
-- Security: Hash-only approach protects against DB compromise
|
||
|
||
-- ======================================================================
|
||
-- DEVICE + CLIENT METADATA
|
||
-- ======================================================================
|
||
user_agent TEXT,
|
||
-- Purpose: Browser/device fingerprinting for security and activity display
|
||
|
||
ip_address INET,
|
||
-- Purpose: Track login-origin IP for anomaly detection
|
||
|
||
device_name TEXT,
|
||
-- Purpose: Optional user-friendly device label (e.g., "John's iPhone")
|
||
|
||
device_type TEXT NOT NULL DEFAULT 'unknown',
|
||
-- Purpose: Categorize device ("mobile", "desktop", "web", "unknown")
|
||
|
||
-- ======================================================================
|
||
-- SESSION LIFECYCLE
|
||
-- ======================================================================
|
||
expires_at TIMESTAMPTZ NOT NULL,
|
||
-- Purpose: Refresh token expiry timestamp
|
||
|
||
is_revoked BOOLEAN NOT NULL DEFAULT FALSE,
|
||
-- Purpose: Marks a session as invalidated due to logout/security rules
|
||
|
||
revoked_at TIMESTAMPTZ NULL,
|
||
-- Purpose: Timestamp of revocation; NULL means active session
|
||
|
||
revoked_reason TEXT,
|
||
-- Purpose: Optional context ("logout", "password_change", "admin_action")
|
||
|
||
-- ======================================================================
|
||
-- AUDIT TIMESTAMPS
|
||
-- ======================================================================
|
||
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
||
-- Purpose: Login timestamp (never updated)
|
||
|
||
last_used_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
|
||
-- Purpose: Updated on every refresh token usage
|
||
);
|
||
|
||
-- =============================================================================
|
||
-- SECTION 2: INDEXES FOR PERFORMANCE & SECURITY
|
||
-- =============================================================================
|
||
|
||
-- Fast retrieval of all sessions for a user
|
||
CREATE INDEX idx_sessions_user_id ON sessions(user_id);
|
||
|
||
-- Lookup active sessions quickly
|
||
CREATE INDEX idx_sessions_active
|
||
ON sessions(user_id, is_revoked)
|
||
WHERE is_revoked = FALSE;
|
||
|
||
-- Cleanup expired sessions
|
||
CREATE INDEX idx_sessions_expires_at
|
||
ON sessions(expires_at)
|
||
WHERE is_revoked = FALSE;
|
||
|
||
-- IP anomaly investigations
|
||
CREATE INDEX idx_sessions_ip_address
|
||
ON sessions(ip_address)
|
||
WHERE is_revoked = FALSE;
|
||
|
||
-- Idle session detection
|
||
CREATE INDEX idx_sessions_last_used
|
||
ON sessions(last_used_at)
|
||
WHERE is_revoked = FALSE;
|
||
|
||
|
||
-- =============================================================================
|
||
-- SECTION 3: AUTOMATIC TRIGGERS
|
||
-- =============================================================================
|
||
-- Note: updated_at column removed because the Go model does not include it.
|
||
-- No trigger required.
|
||
|
||
|
||
-- =============================================================================
|
||
-- SECTION 4: ROW LEVEL SECURITY (RLS)
|
||
-- =============================================================================
|
||
|
||
-- Enable row-level isolation
|
||
ALTER TABLE sessions ENABLE ROW LEVEL SECURITY;
|
||
|
||
-- User-based isolation policy
|
||
CREATE POLICY sessions_user_isolation ON sessions
|
||
FOR ALL
|
||
USING (
|
||
user_id = current_setting('app.current_user_id', true)::UUID
|
||
)
|
||
WITH CHECK (
|
||
user_id = current_setting('app.current_user_id', true)::UUID
|
||
);
|
||
|
||
COMMENT ON POLICY sessions_user_isolation ON sessions IS
|
||
'Restricts all session operations to the authenticated user (no tenant-level RLS).';
|
||
|
||
|
||
-- =============================================================================
|
||
-- SECTION 5: TABLE CONSTRAINTS & VALIDATION
|
||
-- =============================================================================
|
||
|
||
-- Expiry must occur after creation
|
||
ALTER TABLE sessions ADD CONSTRAINT sessions_valid_expiry
|
||
CHECK (expires_at > created_at);
|
||
|
||
-- Revoked_at cannot be before created_at
|
||
ALTER TABLE sessions ADD CONSTRAINT sessions_valid_revocation
|
||
CHECK (revoked_at IS NULL OR revoked_at >= created_at);
|
||
|
||
|
||
-- =============================================================================
|
||
-- SECTION 6: COMMENTS FOR DOCUMENTATION
|
||
-- =============================================================================
|
||
|
||
COMMENT ON TABLE sessions IS
|
||
'User authentication sessions with refresh token hashes. Exact match to Go models.Session (tenant-less).';
|
||
|
||
COMMENT ON COLUMN sessions.id IS 'Unique session ID (UUID v4).';
|
||
COMMENT ON COLUMN sessions.user_id IS 'User who owns this session.';
|
||
COMMENT ON COLUMN sessions.refresh_token_hash IS 'Hash of refresh token (never store plaintext).';
|
||
COMMENT ON COLUMN sessions.user_agent IS 'Client user-agent string.';
|
||
COMMENT ON COLUMN sessions.ip_address IS 'IP address at session creation.';
|
||
COMMENT ON COLUMN sessions.device_name IS 'Optional user-friendly device name.';
|
||
COMMENT ON COLUMN sessions.device_type IS 'Device category: mobile/desktop/web.';
|
||
COMMENT ON COLUMN sessions.expires_at IS 'Refresh token expiration timestamp.';
|
||
COMMENT ON COLUMN sessions.is_revoked IS 'TRUE when session has been explicitly revoked.';
|
||
COMMENT ON COLUMN sessions.revoked_at IS 'Timestamp of revocation event.';
|
||
COMMENT ON COLUMN sessions.revoked_reason IS 'Reason for revocation.';
|
||
COMMENT ON COLUMN sessions.created_at IS 'Timestamp when session was created.';
|
||
COMMENT ON COLUMN sessions.last_used_at IS 'Timestamp of last refresh token usage.';
|
||
|
||
|
||
-- =============================================================================
|
||
-- END OF MIGRATION 000002_add_sessions.up.sql
|
||
-- =============================================================================
|