Database Schema

Database Schema

DataShield uses Prisma 7 with PostgreSQL. The schema lives in prisma/schema.prisma. All identifiers are CUIDs. Every tenant-owned row references a Company, and deletes cascade from the company down.

Entity relationships

erDiagram
  Company ||--o{ User : has
  Company ||--o{ Employee : has
  Company ||--o{ Alert : has
  Company ||--o{ DashboardPreset : has
  Company ||--o{ DirectoryConnection : has
  Company ||--o{ ApiCredential : has
  Company ||--o{ Webhook : has
  Company ||--o{ RemediationAction : logs
  Company ||--o{ ExposureRegisterEntry : records
  Company ||--o{ ReportSchedule : schedules
  Company ||--o{ Role : defines
  Company ||--o{ AuditLog : records
  Company ||--o| SsoProvider : trusts
  User }o--o| Role : holds
  User ||--o{ Session : opens
  User ||--o{ Passkey : registers
  User ||--o{ TwoFactor : enrolls
  User ||--o{ StepUpGrant : earns
  User ||--o| DashboardConfig : owns
  User ||--o{ DashboardPreset : authors
  Employee ||--o{ BreachRecord : has
  Employee ||--o{ Alert : triggers
  Breach ||--o{ BreachRecord : appears_in
  Breach ||--o{ Alert : triggers

Loading

Models

Company

Tenant root. domain is unique. Owns every other tenant-scoped entity; all relations cascade on delete. Holds the auth policy (allowedAuthMethods, default [TOTP], and ssoMandatory), plus company-wide settings: riskWeights (JSON override, see Risk Scoring), remediationEnabled (default false, see Remediation), the SIEM pull token (siemTokenEnc / siemTokenHint) and push config (siemPushUrlEnc / siemPushHint / siemPushFormat / siemPushSince, see SIEM Integration), and the auto-scan cadence (scanIntervalMinutes / lastScanAt).

User

email unique. Points at an optional Role (roleId, set to null if the role is deleted). Auth state lives in emailVerified, twoFactorEnabled, ssoExempt and mustChangePassword. Credentials are held in Account, not on the user row. Has one optional DashboardConfig, authors many DashboardPresets, and points to an activePreset (set to null on preset delete). See Authentication.

Role

A named permission set, scoped to a company. permissions is a String[] validated against the fixed vocabulary. isSystem marks the built-in Administrator role, which cannot be edited or deleted; isAssignable controls whether it can be handed to a user. See Roles and Permissions.

Session / Account / Verification

Better Auth's own tables. Session makes sign-ins server-side revocable, Account holds credentials and linked providers, Verification backs email and OTP flows.

TwoFactor

Per-user TOTP enrollment: secret, backupCodes, verified, plus failedVerificationCount and lockedUntil for lockout after repeated failures.

Passkey

A registered WebAuthn credential. A passkey sign-in is complete primary authentication, so the company auth policy is re-checked on that path.

StepUpGrant

A short-lived proof that the user re-entered their password. Valid for 5 minutes and required before any crown-jewel permission change.

UserInvitation

A pending invitation. Only the token hash is stored, and it expires after 72 hours.

SsoProvider

One OIDC provider per company. domain plus domainVerified gate whether the login page routes an email to it. oidcConfig is encrypted at rest through a Prisma extension.

AuditLog

Append-only record of identity and access changes: action, targetType / targetId, before / after JSON snapshots, ip, and a nullable actorUserId so history survives the actor's deletion. See Audit Log.

RateLimit / ApiRateLimit

Fixed-window counters kept in PostgreSQL rather than per-process memory, so limits hold across instances. Expired rows are swept on write.

Employee

@@unique([email, companyId]) so the same address can exist across tenants but is unique within one. Optional department. Optional mfaEnabled (tri-state: true / false / null unknown, see MFA Coverage). Linked to BreachRecords and Alerts.

Breach

name unique (used as the upsert key by the scan engine). source (HIBP | MANUAL | DARK_WEB | STEALER_LOG), breachDate, dataTypes string array.

BreachRecord

Join between an Employee and a Breach with the exposedData for that specific match. @@unique([employeeId, breachId]) prevents duplicate links. Stealer-log matches add artifacts (ArtifactKind[]) and optional infection metadata (machineId, malwareFamily, capturedAt). See Breach Scanning.

Alert

Severity-scored event. severity (CRITICAL | HIGH | MEDIUM | LOW), status (OPEN | ACKNOWLEDGED | RESOLVED, defaults OPEN). Employee and breach references are nullable and set to null on delete so alert history survives.

DirectoryConnection

An identity-provider connection. type (see DirectoryType), encryptedConfig (AES-256-GCM blob), status (ACTIVE | ERROR | PENDING), plus lastSyncAt, lastSyncCount, errorMessage.

ApiCredential

A breach-provider API key. provider (see ApiProvider), encryptedKey, keyHint (display-safe suffix), status, lastUsedAt. @@unique([companyId, provider]), so one key per provider per company.

SyncJob

A retryable background directory-sync job, tied to a DirectoryConnection. status (SyncJobStatus), attempts / maxAttempts (default 3), runAfter for scheduling/backoff, plus startedAt / finishedAt / lastError. See Directory Integrations.

Webhook

Outbound notification target. channel (WEBHOOK | SLACK | TEAMS | EMAIL, defaults WEBHOOK), encryptedUrl (URL or recipient address), urlHint, minSeverity (defaults MEDIUM), enabled. See Notifications.

RemediationAction

Append-only audit of remediation run against the directory. action (RemediationType), status (SUCCESS | FAILED), target, optional detail, performedBy, optional employeeId / alertId. See Remediation.

ExposureRegisterEntry

A GDPR exposure record. title, detectedAt, status (RegisterStatus), affectedCount, dataCategories, optional assessment and notifiedAt. Deadlines are computed, not stored. See Exposure Register.

ReportSchedule

A recurring report emailed to recipients. frequency (ScheduleFrequency), recipients, sections, enabled, lastSentAt. See Reports.

DashboardConfig / DashboardPreset

Per-user live layout (DashboardConfig, one per user) and saved presets (DashboardPreset, scope PERSONAL or COMPANY). Both store layout and widgets as JSON.

Enums

Enum	Values
AuthMethod	TOTP, EMAIL_OTP, PASSKEY
Severity	CRITICAL, HIGH, MEDIUM, LOW
AlertStatus	OPEN, ACKNOWLEDGED, RESOLVED
BreachSource	HIBP, MANUAL, DARK_WEB, STEALER_LOG
ArtifactKind	PASSWORD, COOKIE, TOKEN, AUTOFILL
DirectoryType	AZURE_AD, GOOGLE_WORKSPACE, LDAP, AWS_DIRECTORY, OKTA, SCIM
ApiProvider	HIBP, HIBP_STEALER, DEHASHED, LEAKCHECK, INTELX, SNUSBASE
ConnectStatus	ACTIVE, ERROR, PENDING
SyncJobStatus	PENDING, RUNNING, SUCCEEDED, FAILED
PresetScope	PERSONAL, COMPANY
NotificationChannel	WEBHOOK, SLACK, TEAMS, EMAIL
RemediationType	REVOKE_SESSIONS, FORCE_PASSWORD_RESET
RemediationStatus	SUCCESS, FAILED
RegisterStatus	ASSESSING, NOTIFIED, NOT_REQUIRED
ScheduleFrequency	WEEKLY, MONTHLY

Migrations

Migrations are checked into prisma/migrations/, applied with prisma migrate deploy (npm run db:migrate). The current history:

1. init
2. add_dashboard_config
3. add_preset_scope_and_active_preset
4. add_directory_connections
5. add_aws_directory_type
6. add_okta_scim_directory_types
7. add_api_credentials
8. add_webhooks
9. add_sync_jobs
10. add_schedule_config
11. add_stealer_log_artifacts
12. add_notification_channels
13. add_company_risk_weights
14. add_employee_mfa
15. add_remediation
16. add_siem_token
17. add_exposure_register
18. add_report_schedules
19. add_siem_push
20. add_alert_confidence
21. add_breach_record_sources
22. better_auth
23. two_factor_verification_fields
24. add_passkey
25. rbac_roles
26. rbac_audit_stepup
27. add_rate_limit_tables
28. add_sso_provider_and_policy
29. add_user_invitation_and_forced_password_change