AuthService: Database Schema¶

Version	Date	Author	Change Description
1.0	2025-09-04	Senior Systems Analyst	Initial schema for local password authentication.
2.0	2025-09-11	Senior Systems Analyst	Major revision for Google SSO integration. Removed password fields, added Google ID.

1.0 Overview¶

This document defines the physical database schema for the AuthService. The schema is designed to support a hybrid access control model (GBAC/RBAC) and is now structured to use Google Directory as the external Identity Provider (IdP), removing the need for local password storage.

2.0 Entity-Relationship Diagram (ERD)¶

The following diagram illustrates the relationships between the core entities in the AuthService database.

erDiagram
    users {
        UUID user_id PK
        String google_subject_id "UNIQUE, NOT NULL"
        String email "UNIQUE, NOT NULL"
        String display_name "NOT NULL"
        ENUM status "('Active', 'Inactive', 'Invited')"
        TIMESTAMPTZ created_at
        TIMESTAMPTZ updated_at
        TIMESTAMPTZ last_login_at
    }

    groups {
        UUID group_id PK
        String name "UNIQUE, NOT NULL"
        String description
    }

    roles {
        UUID role_id PK
        String name "UNIQUE, NOT NULL"
        String description
    }

    permissions {
        UUID permission_id PK
        String name "UNIQUE, NOT NULL"
        String description
    }

    audit_logs {
        BIGINT log_id PK
        TIMESTAMPTZ timestamp
        UUID actor_user_id FK
        String event_type
        UUID target_user_id FK
    }

    %% --- Junction Tables for Many-to-Many Relationships ---

    user_roles {
        UUID user_id FK
        UUID role_id FK
    }

    user_groups {
        UUID user_id FK
        UUID group_id FK
    }

    group_roles {
        UUID group_id FK
        UUID role_id FK
    }

    role_permissions {
        UUID role_id FK
        UUID permission_id FK
    }

    %% --- Defining Relationships ---

    users              }|--|| user_roles       : "has"
    roles              }|--|| user_roles       : "is assigned to"

    users              }|--|| user_groups      : "is member of"
    groups             }|--|| user_groups      : "has member"

    groups             }|--|| group_roles      : "is associated with"
    roles              }|--|| group_roles      : "can be granted to"

    roles              }|--|| role_permissions : "contains"
    permissions        }|--|| role_permissions : "is granted via"

    users              }|..o{ audit_logs       : "is actor/target for"

3.0 Table Definitions¶

3.1 users¶

Stores the individual user accounts. Passwords are not stored; identity is managed via the google_subject_id.

Column Name	Data Type	Constraints	Description
user_id	UUID	PRIMARY KEY	The unique internal identifier for the user.
google_subject_id	VARCHAR(255)	UNIQUE, NOT NULL	The unique, immutable subject ID provided by Google. This is the primary link to the external identity.
email	VARCHAR(255)	UNIQUE, NOT NULL	The user's email address, synced from their Google profile.
display_name	VARCHAR(255)	NOT NULL	The user's display name, synced from their Google profile.
status	ENUM('Active', 'Inactive', 'Invited')	NOT NULL	The user's status within the application. Invited is a pre-provisioned state.
created_at	TIMESTAMPTZ	NOT NULL, DEFAULT now()	Timestamp of when the user record was created.
updated_at	TIMESTAMPTZ	NOT NULL, DEFAULT now()	Timestamp of the last update to the user record.
last_login_at	TIMESTAMPTZ	NULL	Timestamp of the user's last successful login.

3.2 groups¶

Stores user groups, which can have roles assigned to them.

Column Name	Data Type	Constraints	Description
group_id	UUID	PRIMARY KEY	The unique identifier for the group.
name	VARCHAR(255)	UNIQUE, NOT NULL	The unique name of the group.
description	TEXT	NULL	A brief description of the group's purpose.

3.3 roles¶

Stores roles, which are collections of permissions.

Column Name	Data Type	Constraints	Description
role_id	UUID	PRIMARY KEY	The unique identifier for the role.
name	VARCHAR(255)	UNIQUE, NOT NULL	The unique name of the role.
description	TEXT	NULL	A brief description of the role's purpose.

3.4 permissions¶

Stores the individual, granular permissions that can be assigned to roles.

Column Name	Data Type	Constraints	Description
permission_id	UUID	PRIMARY KEY	The unique identifier for the permission.
name	VARCHAR(255)	UNIQUE, NOT NULL	The unique, machine-readable name of the permission (e.g., admin:user:edit).
description	TEXT	NULL	A brief description of what the permission allows.

3.5 Junction Tables¶

These tables create the many-to-many relationships required for the access control model.

user_groups: Links users to groups (user_id, group_id).
user_roles: Links users directly to roles (user_id, role_id).
group_roles: Links groups to roles (group_id, role_id).
role_permissions: Links roles to permissions (role_id, permission_id).

3.6 audit_logs¶

Stores immutable records of security-sensitive events.

Column Name	Data Type	Constraints	Description
log_id	BIGSERIAL	PRIMARY KEY	A unique, auto-incrementing identifier for the log entry.
timestamp	TIMESTAMPTZ	NOT NULL, DEFAULT now()	The UTC timestamp of when the event occurred.
actor_user_id	UUID	FOREIGN KEY to users.user_id	The user who performed the action.
event_type	VARCHAR(50)	NOT NULL	The type of event that occurred. Valid values include: USER_LOGIN_SUCCESS, USER_LOGIN_FAILURE, USER_LOGOUT, USER_PROVISIONED, USER_INVITED, USER_STATUS_CHANGED, USER_ROLE_ASSIGNED, USER_ROLE_UNASSIGNED, USER_GROUP_ASSIGNED, USER_GROUP_UNASSIGNED.
target_user_id	UUID	FOREIGN KEY to users.user_id, NULL	If the event targeted another user, this is their ID.
details	JSONB	NULL	A JSON object containing additional context-specific details (e.g., { "old_status": "Active", "new_status": "Inactive" }).