07 - Database Schema Changes for Admin Service

This document describes all database and cache schema modifications required by the Admin Service feature. It covers PostgreSQL table/enum changes managed through Prisma ORM, along with Redis key patterns used for passwordless authentication.

---

1. ER Diagram

The diagram below shows the tables that are modified or directly involved in the Admin Service feature and their relationships.

erDiagram
    users {
        uuid id PK
        varchar email
        varchar phone
        varchar password_hash
        varchar password_salt
        boolean is_email_verified
        boolean is_blocked
        timestamp created_at
        timestamp updated_at
    }

    roles {
        uuid id PK
        varchar type
        timestamp created_at
        timestamp updated_at
    }

    users_on_roles {
        uuid id PK
        uuid user_id FK
        uuid role_id FK
        timestamp created_at
        timestamp updated_at
    }

    tokens {
        uuid id PK
        uuid user_id FK
        uuid access_token_id
        int access_token_expired_at
        varchar refresh_token_id
        int refresh_token_expired_at
        timestamp created_at
        timestamp updated_at
    }

    sessions {
        uuid id PK
        uuid user_id FK
        uuid token_id FK
        varchar fingerprint
        varchar ip_address
        timestamp created_at
        timestamp updated_at
    }

    projects {
        uuid id PK
        text title
        text description
        varchar category
        varchar status
        uuid customer_id FK
        timestamp deleted_at
        timestamp created_at
        timestamp updated_at
    }

    customers {
        uuid id PK
        uuid user_id FK
        timestamp created_at
        timestamp updated_at
    }

    users ||--o{ users_on_roles : "has roles"
    roles ||--o{ users_on_roles : "assigned to users"
    users ||--o{ tokens : "owns"
    users ||--o{ sessions : "has sessions"
    tokens |o--o| sessions : "linked to"
    users o|--o| customers : "may be"
    customers ||--o{ projects : "owns"

---

2. Existing Table Changes

2.1 users Table

Change	Column	Before	After	Reason
ADD	is_blocked	—	boolean NOT NULL DEFAULT false	Allows admins to block users, preventing login and API access.
ALTER	password_hash	varchar(255) NOT NULL	varchar(255) NULL	Admin accounts use passwordless auth (email + verification code). Password fields must be nullable to support user records without credentials.
ALTER	password_salt	varchar(255) NOT NULL	varchar(255) NULL	Same as above — admin users have no password, so the salt is also null.

2.2 projects Table

Change	Column	Before	After	Reason
ADD	deleted_at	—	timestamp NULL	Enables soft-delete for projects. Admins can remove projects from public visibility without destroying data. A null value means the project is active; a non-null timestamp means it has been soft-deleted.

2.3 RoleType Enum

Change	Value	Reason
ADD	ADMIN	New role type for administrative users. Combined with the existing roles/users_on_roles junction table pattern, this allows any User record to be granted admin privileges.

2.4 roles Table (Seed Data)

Change	Record	Reason
INSERT	{ type: ADMIN }	A new seed record must be inserted so that admin users can be linked via users_on_roles. The UUID will be generated at seed time and stored as a constant (similar to existing CUSTOMER_ROLE and EMPLOYEE_ROLE constants).

Existing seeded records remain unchanged:

• CUSTOMER_ROLE = { id: '5b8618e8-ea03-4f7b-b73c-928d90d911e1', type: CUSTOMER }
• EMPLOYEE_ROLE = { id: '121b7004-def6-439c-966e-5fb2da0ec9c4', type: EMPLOYEE }

---

3. Column Details

3.1 users — Modified/New Columns

Column	DB Type	Nullable	Default	Description
id	uuid	NO	gen_random_uuid()	Primary key (unchanged).
email	varchar(255)	NO	—	Unique user email (unchanged).
phone	varchar(255)	YES	NULL	Optional phone number (unchanged).
password_hash	varchar(255)	YES (changed)	NULL	Bcrypt hash of user password. Null for passwordless admin accounts.
password_salt	varchar(255)	YES (changed)	NULL	Random salt used for password hashing. Null for passwordless admin accounts.
is_email_verified	boolean	NO	false	Whether the user has verified their email (unchanged).
is_blocked	boolean	NO	false	Whether the user is blocked by an admin. Blocked users cannot authenticate or access protected endpoints.
created_at	timestamp	NO	now()	Record creation time (unchanged).
updated_at	timestamp	NO	auto	Last update time (unchanged).

3.2 projects — Modified/New Columns

Column	DB Type	Nullable	Default	Description
id	uuid	NO	gen_random_uuid()	Primary key (unchanged).
title	text	NO	—	Project title (unchanged).
description	text	NO	—	Project description (unchanged).
category	ProjectCategoryType	YES	NULL	Project category enum (unchanged).
status	ProjectStatus	NO	DRAFT	Project lifecycle status (unchanged).
customer_id	uuid	NO	—	FK to customers.id (unchanged).
deleted_at	timestamp	YES	NULL	Soft-delete timestamp. Null means active; non-null means the project was soft-deleted by an admin at that time.
created_at	timestamp	NO	now()	Record creation time (unchanged).
updated_at	timestamp	NO	auto	Last update time (unchanged).

---

4. Index Plan

4.1 New Indexes

Table	Column(s)	Type	Justification
users	is_blocked	B-tree	Admin queries frequently filter by blocked status (e.g., listing all blocked users, checking block status during authentication). Since the column has low cardinality (true/false), a partial index on is_blocked = true would be ideal, but Prisma does not support partial indexes natively. The standard index still benefits queries that filter WHERE is_blocked = true when the blocked population is small relative to total users.
projects	deleted_at	B-tree	Soft-delete queries need to filter WHERE deleted_at IS NULL (active projects) or WHERE deleted_at IS NOT NULL (deleted projects). This index supports both admin audit views and the default project listing that excludes soft-deleted records.

4.2 Existing Indexes (Unchanged)

Table	Column(s)	Type	Status
users	email	Unique	Retained — used for login lookups.
users	phone	Unique	Retained — used for phone-based lookups.
projects	customer_id	B-tree	Retained — used for customer-scoped project queries.
projects	status	B-tree	Retained — used for status-filtered listings.
projects	category	B-tree	Retained — used for category-filtered listings.
users_on_roles	[user_id, role_id]	Unique	Retained — prevents duplicate role assignments.
sessions	token_id	Unique	Retained — one session per token.
sessions	[user_id, fingerprint]	Unique	Retained — one session per device per user.
tokens	user_id	Implicit FK	Retained — token lookups by user.

---

5. Prisma Entity Mapping

5.1 Updated RoleType Enum

enum RoleType {
  CUSTOMER
  EMPLOYEE
  ADMIN       // [NEW] Admin role type
}

5.2 Updated User Model

model User {
  id           String  @id @default(dbgenerated("gen_random_uuid()")) @db.Uuid
  email        String  @unique @db.VarChar(255)
  phone        String? @unique @db.VarChar(255)
  passwordHash String? @map("password_hash") @db.VarChar(255)   // [CHANGED] Now nullable
  passwordSalt String? @map("password_salt") @db.VarChar(255)   // [CHANGED] Now nullable

  isEmailVerified Boolean @default(false) @map("is_email_verified")
  isBlocked       Boolean @default(false) @map("is_blocked")    // [NEW] Block flag

  createdAt DateTime @default(now()) @map("created_at")
  updatedAt DateTime @updatedAt @map("updated_at")

  customer   Customer?
  employee   Employee?
  profile    Profile?
  userOnRole UserOnRole[]
  token      Token[]
  session    Session[]

  @@index([isBlocked])                                           // [NEW] Index
  @@map("users")
}

5.3 Updated Project Model

model Project {
  id          String               @id @default(dbgenerated("gen_random_uuid()")) @db.Uuid
  title       String
  description String
  category    ProjectCategoryType?
  status      ProjectStatus        @default(DRAFT)
  customerId  String               @map("customer_id") @db.Uuid

  deletedAt DateTime? @map("deleted_at")                         // [NEW] Soft-delete

  createdAt DateTime @default(now()) @map("created_at")
  updatedAt DateTime @updatedAt @map("updated_at")

  customer       Customer        @relation(fields: [customerId], references: [id], onDelete: Cascade)
  walriderThread WalriderThread?

  @@index([customerId])
  @@index([status])
  @@index([category])
  @@index([deletedAt])                                           // [NEW] Index
  @@map("projects")
}

5.4 Unchanged Models (Reference Only)

The following models are not modified but participate in the admin authentication flow. Their Prisma definitions remain identical to the current schema:

• Role — no structural change; only the RoleType enum gains a new value.
• UserOnRole — used to assign the ADMIN role to users; no schema change needed.
• Token — stores admin JWT token pairs; no schema change needed.
• Session — stores admin sessions; no schema change needed.

---

6. Migration Change List

Step 1: Add ADMIN to RoleType Enum

ALTER TYPE "RoleType" ADD VALUE 'ADMIN';

Note: PostgreSQL enum value additions are not transactional. This statement must run outside the main migration transaction or use CREATE TYPE ... AS ENUM replacement strategy if the migration tool requires it.

Step 2: Add is_blocked Column to users

ALTER TABLE "users"
  ADD COLUMN "is_blocked" BOOLEAN NOT NULL DEFAULT false;

CREATE INDEX "users_is_blocked_idx" ON "users" ("is_blocked");

Impact: Non-blocking for existing rows — the DEFAULT false ensures all current users are unblocked.

Step 3: Alter password_hash and password_salt to Nullable

ALTER TABLE "users"
  ALTER COLUMN "password_hash" DROP NOT NULL;

ALTER TABLE "users"
  ALTER COLUMN "password_salt" DROP NOT NULL;

Impact: No data loss. Existing rows retain their current non-null values. Only new admin user records will be inserted with null values.

Step 4: Add deleted_at Column to projects

ALTER TABLE "projects"
  ADD COLUMN "deleted_at" TIMESTAMP;

CREATE INDEX "projects_deleted_at_idx" ON "projects" ("deleted_at");

Impact: All existing projects receive deleted_at = NULL, meaning they are active. No data change.

Step 5: Insert ADMIN Role Record

INSERT INTO "roles" ("id", "type", "created_at", "updated_at")
VALUES (gen_random_uuid(), 'ADMIN', NOW(), NOW());

Note: The generated UUID should be captured and stored as a seed constant (e.g., ADMIN_ROLE in the seed configuration) for use in application code and tests, following the same pattern as CUSTOMER_ROLE and EMPLOYEE_ROLE.

Rollback Considerations

Step	Rollback Action	Risk
Step 1 (enum value)	PostgreSQL does not support DROP VALUE from enums. Rollback requires renaming the type, creating a new enum without ADMIN, and migrating all columns. Only feasible if no rows reference ADMIN.	High — should only be rolled back before any admin users are created.
Step 2 (is_blocked)	ALTER TABLE "users" DROP COLUMN "is_blocked"	Low — no existing data depends on it at launch.
Step 3 (nullable passwords)	UPDATE "users" SET password_hash = '', password_salt = '' WHERE password_hash IS NULL; ALTER TABLE "users" ALTER COLUMN "password_hash" SET NOT NULL; ALTER TABLE "users" ALTER COLUMN "password_salt" SET NOT NULL;	Medium — admin user records with null passwords must be handled before restoring the NOT NULL constraint.
Step 4 (deleted_at)	ALTER TABLE "projects" DROP COLUMN "deleted_at"	Low — soft-delete timestamps can be discarded if the feature is reverted.
Step 5 (seed record)	DELETE FROM "roles" WHERE "type" = 'ADMIN'	Low — only possible if no users_on_roles rows reference this role (FK constraint).

---

7. Redis Key Schema

The Admin Service uses Redis for storing verification codes during passwordless authentication. These keys are not part of the PostgreSQL database but are documented here for completeness.

Key Pattern	Value	TTL	Description
ADMIN_VERIFICATION_CODE:email={email}	6-character alphanumeric code	900s (15 min)	Stores the verification code sent to the admin’s corporate email during registration or login. Deleted from Redis upon successful verification.
ADMIN_VERIFICATION_RESEND_CODE:email={email}	1 (presence flag)	120s (2 min)	Cooldown guard that prevents re-sending a verification code within the TTL window. If this key exists, the resend request is rejected with a rate-limit error.

Key Lifecycle

1. Send code — system generates a 6-character code via generateRandomCode(), stores it at ADMIN_VERIFICATION_CODE:email={email} with TTL 900s, and sets ADMIN_VERIFICATION_RESEND_CODE:email={email} with TTL 120s.
2. Resend code — system checks for the existence of the resend cooldown key. If present, the request is rejected. Otherwise, a new code is generated and both keys are reset.
3. Verify code — system reads the code from ADMIN_VERIFICATION_CODE:email={email}, compares it with the submitted code, and deletes the key on success.
4. Expiration — if the code is not verified within 15 minutes, it expires automatically via Redis TTL. The cooldown key expires independently after 2 minutes.