Skip to main content

Database Schema

CORTEX uses PostgreSQL with Prisma ORM. This document describes the core data model.

Entity Relationship Diagram​

┌─────────────┐       ┌─────────────┐
│   Tenant    │──────▶│    User     │
└─────────────┘       └─────────────┘
      │                     │
      │                     │
      ▼                     ▼
┌─────────────┐       ┌─────────────┐
│Organization │       │   Session   │
└─────────────┘       └─────────────┘
      │
      │
      ▼
┌─────────────┐       ┌─────────────┐
│    Role     │──────▶│ Permission  │
└─────────────┘       └─────────────┘
      │
      ▼
┌─────────────┐       ┌─────────────┐
│   Role      │       │  AuditLog   │
│ Assignment  │       │             │
└─────────────┘       └─────────────┘

Core Tables​

Tenant​

CREATE TABLE "Tenant" (
  id          UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  name        VARCHAR(255) NOT NULL,
  slug        VARCHAR(100) NOT NULL UNIQUE,
  status      VARCHAR(20) NOT NULL DEFAULT 'ACTIVE',
  settings    JSONB DEFAULT '{}',
  created_at  TIMESTAMP DEFAULT NOW(),
  updated_at  TIMESTAMP DEFAULT NOW()
);

User​

CREATE TABLE "User" (
  id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  tenant_id       UUID NOT NULL REFERENCES "Tenant"(id),
  email           VARCHAR(255) NOT NULL,
  password_hash   VARCHAR(255) NOT NULL,
  first_name      VARCHAR(100) NOT NULL,
  last_name       VARCHAR(100) NOT NULL,
  status          VARCHAR(20) NOT NULL DEFAULT 'ACTIVE',
  last_login_at   TIMESTAMP,
  failed_attempts INT DEFAULT 0,
  locked_until    TIMESTAMP,
  created_at      TIMESTAMP DEFAULT NOW(),
  updated_at      TIMESTAMP DEFAULT NOW(),
  deleted_at      TIMESTAMP,
  UNIQUE(tenant_id, email)
);

Organization​

CREATE TABLE "Organization" (
  id          UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  tenant_id   UUID NOT NULL REFERENCES "Tenant"(id),
  parent_id   UUID REFERENCES "Organization"(id),
  name        VARCHAR(255) NOT NULL,
  slug        VARCHAR(100) NOT NULL,
  status      VARCHAR(20) NOT NULL DEFAULT 'ACTIVE',
  created_at  TIMESTAMP DEFAULT NOW(),
  updated_at  TIMESTAMP DEFAULT NOW(),
  UNIQUE(tenant_id, slug)
);

Role​

CREATE TABLE "Role" (
  id          UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  tenant_id   UUID NOT NULL REFERENCES "Tenant"(id),
  parent_id   UUID REFERENCES "Role"(id),
  name        VARCHAR(100) NOT NULL,
  description TEXT,
  scope_level VARCHAR(20) NOT NULL,
  is_system   BOOLEAN DEFAULT FALSE,
  created_at  TIMESTAMP DEFAULT NOW(),
  updated_at  TIMESTAMP DEFAULT NOW(),
  UNIQUE(tenant_id, name)
);

Permission​

CREATE TABLE "Permission" (
  id          UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  tenant_id   UUID NOT NULL REFERENCES "Tenant"(id),
  resource    VARCHAR(100) NOT NULL,
  action      VARCHAR(100) NOT NULL,
  description TEXT,
  created_at  TIMESTAMP DEFAULT NOW(),
  UNIQUE(tenant_id, resource, action)
);

RolePermission​

CREATE TABLE "RolePermission" (
  role_id       UUID NOT NULL REFERENCES "Role"(id),
  permission_id UUID NOT NULL REFERENCES "Permission"(id),
  created_at    TIMESTAMP DEFAULT NOW(),
  PRIMARY KEY (role_id, permission_id)
);

RoleAssignment​

CREATE TABLE "RoleAssignment" (
  id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id         UUID NOT NULL REFERENCES "User"(id),
  role_id         UUID NOT NULL REFERENCES "Role"(id),
  organization_id UUID REFERENCES "Organization"(id),
  expires_at      TIMESTAMP,
  created_by      UUID REFERENCES "User"(id),
  created_at      TIMESTAMP DEFAULT NOW()
);

Session​

CREATE TABLE "Session" (
  id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id         UUID NOT NULL REFERENCES "User"(id),
  refresh_token   VARCHAR(500) NOT NULL,
  ip_address      VARCHAR(45),
  user_agent      TEXT,
  expires_at      TIMESTAMP NOT NULL,
  last_active_at  TIMESTAMP DEFAULT NOW(),
  created_at      TIMESTAMP DEFAULT NOW()
);

AuditLog​

CREATE TABLE "AuditLog" (
  id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  tenant_id       UUID NOT NULL REFERENCES "Tenant"(id),
  user_id         UUID REFERENCES "User"(id),
  action          VARCHAR(50) NOT NULL,
  resource_type   VARCHAR(50) NOT NULL,
  resource_id     UUID,
  old_value       JSONB,
  new_value       JSONB,
  metadata        JSONB DEFAULT '{}',
  ip_address      VARCHAR(45),
  user_agent      TEXT,
  correlation_id  VARCHAR(100),
  created_at      TIMESTAMP DEFAULT NOW()
);

Indexes​

-- User indexes
CREATE INDEX idx_user_tenant ON "User"(tenant_id);
CREATE INDEX idx_user_email ON "User"(tenant_id, email);
CREATE INDEX idx_user_status ON "User"(status) WHERE deleted_at IS NULL;

-- Organization indexes
CREATE INDEX idx_org_tenant ON "Organization"(tenant_id);
CREATE INDEX idx_org_parent ON "Organization"(parent_id);

-- Role indexes
CREATE INDEX idx_role_tenant ON "Role"(tenant_id);
CREATE INDEX idx_role_parent ON "Role"(parent_id);

-- RoleAssignment indexes
CREATE INDEX idx_ra_user ON "RoleAssignment"(user_id);
CREATE INDEX idx_ra_role ON "RoleAssignment"(role_id);
CREATE INDEX idx_ra_org ON "RoleAssignment"(organization_id);

-- AuditLog indexes
CREATE INDEX idx_audit_tenant ON "AuditLog"(tenant_id);
CREATE INDEX idx_audit_user ON "AuditLog"(user_id);
CREATE INDEX idx_audit_resource ON "AuditLog"(resource_type, resource_id);
CREATE INDEX idx_audit_created ON "AuditLog"(created_at DESC);

Soft Delete Pattern​

Tables with deleted_at support soft delete:

-- Soft delete a user
UPDATE "User" SET deleted_at = NOW() WHERE id = 'user-id';

-- Query excludes deleted records
SELECT * FROM "User" WHERE deleted_at IS NULL;

Prisma Schema Location​

The schema is defined at:

apps/core/prisma/schema.prisma

To apply changes:

cd apps/core
pnpm db:migrate