[Database]: Migrate Authentication Database Schema

[jimbrig]

In order to properly integrate multi-tenancy and tie database entries for the market survey and other app-level schemas, we need the auth schema to be in the same database as the other schemas.

Consolidate auth schema into GMH database

• Rename noclocks schema to auth for clarity and to align with existing GMH naming conventions.
• Move tables and adjust any cross-schema references.

Add new functionality

• Add tenants and their relationships to accounts.
• Include support for investment partners and portfolios.
• Adjust role management for flexibility with multi-tenancy and hierarchical permissions.
• Enhance session management for more robust user activity tracking.

Revised DDL

Tenants

CREATE TABLE auth.tenants (
    tenant_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    name TEXT NOT NULL,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    modified_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

Accounts

CREATE TABLE auth.accounts (
    account_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id UUID REFERENCES auth.tenants(tenant_id) ON DELETE CASCADE,
    email TEXT UNIQUE,
    auth_key TEXT,
    hashed_auth_key TEXT,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    modified_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

Users

CREATE TABLE auth.users (
    user_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    account_id UUID REFERENCES auth.accounts(account_id) ON DELETE CASCADE,
    tenant_id UUID REFERENCES auth.tenants(tenant_id) ON DELETE CASCADE,
    email TEXT UNIQUE NOT NULL,
    hashed_password TEXT,
    email_verified BOOLEAN NOT NULL DEFAULT FALSE,
    two_fa_code TEXT,
    two_fa_verified BOOLEAN NOT NULL DEFAULT FALSE,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    modified_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    UNIQUE (tenant_id, email)
);

Roles & User Roles

CREATE TABLE auth.roles (
    role_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id UUID REFERENCES auth.tenants(tenant_id) ON DELETE CASCADE,
    role_name TEXT NOT NULL,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    modified_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    UNIQUE (tenant_id, role_name)
);

CREATE TABLE auth.user_roles (
    user_role_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id UUID REFERENCES auth.tenants(tenant_id) ON DELETE CASCADE,
    user_id UUID REFERENCES auth.users(user_id) ON DELETE CASCADE,
    role_id UUID REFERENCES auth.roles(role_id) ON DELETE CASCADE,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

Sessions

CREATE TABLE auth.sessions (
    session_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id UUID REFERENCES auth.tenants(tenant_id) ON DELETE CASCADE,
    user_id UUID REFERENCES auth.users(user_id) ON DELETE CASCADE,
    hashed_cookie TEXT,
    is_active BOOLEAN NOT NULL DEFAULT TRUE,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    modified_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

Integration with other schemas

Investment Partners and Portfolios

These entities (currently in gmh) should reference the updated auth.tenants table for better integration.

CREATE TABLE gmh.investment_partners (
    partner_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id UUID REFERENCES auth.tenants(tenant_id) ON DELETE CASCADE,
    name TEXT NOT NULL,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE TABLE gmh.portfolios (
    portfolio_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id UUID REFERENCES auth.tenants(tenant_id) ON DELETE CASCADE,
    name TEXT NOT NULL,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE TABLE gmh.portfolio_assignments (
    portfolio_assignment_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    portfolio_id UUID REFERENCES gmh.portfolios(portfolio_id) ON DELETE CASCADE,
    property_id UUID REFERENCES gmh.properties(property_id) ON DELETE CASCADE,
    assigned_at TIMESTAMPTZ DEFAULT NOW()
);

Enhance Logging

Extend logging to support tenant and user activity tracking.

CREATE TABLE logs.auth_activity (
    log_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id UUID REFERENCES auth.tenants(tenant_id) ON DELETE CASCADE,
    user_id UUID REFERENCES auth.users(user_id) ON DELETE CASCADE,
    action TEXT NOT NULL,
    details JSONB,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);