Database Migration: V1 - Initial Schema for Google SSO

Version	Date	Author	Change Description
1.0	2025-09-11	Senior Systems Analyst	Initial database schema creation for the Google SSO architecture.

1.0 Overview

This document contains the official UP and DOWN SQL migration scripts for Version 1 of the AuthService database. This migration establishes the complete set of tables required to support the application, based on the v2.0 schema designed for Google SSO integration, Just-In-Time provisioning, and internal access control management.

Database System: PostgreSQL

2.0 UP Migration

The following SQL statements are executed to apply this migration. They will create all necessary tables, constraints, and relationships for the initial deployment.

-- UP MIGRATION V1

-- Enable UUID extension  
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

-- Create ENUM type for user status  
CREATE TYPE user_status AS ENUM ('Active', 'Inactive', 'Invited');

-- Create users table  
-- This table stores user profile information, linking to Google via google_subject_id.  
CREATE TABLE users (  
    user_id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),  
    google_subject_id VARCHAR(255) UNIQUE NOT NULL,  
    email VARCHAR(255) UNIQUE NOT NULL,  
    display_name VARCHAR(255) NOT NULL,  
    status user_status NOT NULL DEFAULT 'Active',  
    created_at TIMESTAMPTZ NOT NULL DEFAULT now(),  
    updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),  
    last_login_at TIMESTAMPTZ  
);  
CREATE INDEX idx_users_email ON users(email);

-- Create groups table  
CREATE TABLE groups (  
    group_id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),  
    name VARCHAR(255) UNIQUE NOT NULL,  
    description TEXT  
);

-- Create roles table  
CREATE TABLE roles (  
    role_id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),  
    name VARCHAR(255) UNIQUE NOT NULL,  
    description TEXT  
);

-- Create permissions table  
CREATE TABLE permissions (  
    permission_id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),  
    name VARCHAR(255) UNIQUE NOT NULL,  
    description TEXT  
);

-- Create JUNCTION tables for many-to-many relationships  
CREATE TABLE user_groups (  
    user_id UUID NOT NULL REFERENCES users(user_id) ON DELETE CASCADE,  
    group_id UUID NOT NULL REFERENCES groups(group_id) ON DELETE CASCADE,  
    PRIMARY KEY (user_id, group_id)  
);

CREATE TABLE user_roles (  
    user_id UUID NOT NULL REFERENCES users(user_id) ON DELETE CASCADE,  
    role_id UUID NOT NULL REFERENCES roles(role_id) ON DELETE CASCADE,  
    PRIMARY KEY (user_id, role_id)  
);

CREATE TABLE group_roles (  
    group_id UUID NOT NULL REFERENCES groups(group_id) ON DELETE CASCADE,  
    role_id UUID NOT NULL REFERENCES roles(role_id) ON DELETE CASCADE,  
    PRIMARY KEY (group_id, role_id)  
);

CREATE TABLE role_permissions (  
    role_id UUID NOT NULL REFERENCES roles(role_id) ON DELETE CASCADE,  
    permission_id UUID NOT NULL REFERENCES permissions(permission_id) ON DELETE CASCADE,  
    PRIMARY KEY (role_id, permission_id)  
);

-- Create audit_logs table  
-- Stores an immutable record of security-sensitive events.  
CREATE TABLE audit_logs (  
    log_id BIGSERIAL PRIMARY KEY,  
    timestamp TIMESTAMPTZ NOT NULL DEFAULT now(),  
    actor_user_id UUID NOT NULL REFERENCES users(user_id),  
    event_type VARCHAR(50) NOT NULL,  
    target_user_id UUID REFERENCES users(user_id),  
    details JSONB  
);  
CREATE INDEX idx_audit_logs_timestamp ON audit_logs(timestamp);  
CREATE INDEX idx_audit_logs_actor ON audit_logs(actor_user_id);  
CREATE INDEX idx_audit_logs_target ON audit_logs(target_user_id);

3.0 DOWN Migration

The following SQL statements are executed to revert this migration. They will drop all tables created in the UP script in the reverse order of creation to respect foreign key constraints.

-- DOWN MIGRATION V1
DROP TABLE IF EXISTS audit_logs;  
DROP TABLE IF EXISTS role_permissions;  
DROP TABLE IF EXISTS group_roles;  
DROP TABLE IF EXISTS user_roles;  
DROP TABLE IF EXISTS user_groups;  
DROP TABLE IF EXISTS permissions;  
DROP TABLE IF EXISTS roles;  
DROP TABLE IF EXISTS groups;  
DROP TABLE IF EXISTS users;  
DROP TYPE IF EXISTS user_status;