Skip to Content
Z8Z8 Docs
Technical Docs

Database Schema

Core tables, canonical time records, and Drizzle ORM configuration

Core Tables

The database uses Drizzle ORM on PostgreSQL with organization-scoped tables across auth, workforce, time, export, and audit domains.

Runtime database connections use individual environment variables rather than a single connection string:

POSTGRES_HOST=postgres.example.com
POSTGRES_PORT=5432
POSTGRES_DB=z8
POSTGRES_USER=z8
POSTGRES_PASSWORD=<password>
POSTGRES_SSL_MODE=disable

Managed PostgreSQL providers may require TLS. For providers that document sslmode=verify-full sslrootcert=/path/to/provider-ca.pem, use:

POSTGRES_SSL_MODE=verify-full
POSTGRES_SSL_ROOT_CERT_PATH=/etc/postgres-certs/provider-ca.pem

POSTGRES_SSL_CA_CERT can be used instead when the CA certificate PEM content is supplied through the secret store rather than mounted as a file.

  • users - Authentication and account-level fields
  • organizations - Tenant boundary for all scoped data
  • members - Organization membership and role assignment
  • employees - Employee profiles linked to users inside an organization
  • teams - Team structure and membership
  • work_period - Legacy work-period source data used by operational flows and exports
  • absence_entry - Legacy absence requests and approved absence records
  • approval_request - Approval workflow state for time and absence changes
  • time_record - Canonical time record for normalized work, absence, and break history
  • time_record_work / time_record_absence / time_record_break - Canonical detail tables by record kind
  • time_record_allocation - Project or cost-center splits for canonical work records
  • time_record_approval_decision - Approval actions attached directly to canonical records
  • payroll_export_config / payroll_wage_type_mapping / payroll_export_job / payroll_export_sync_record - Payroll export configuration and execution state
  • notifications / push_subscriptions / audit_logs - Delivery, realtime, and compliance trails

Schema Modularization

The schema is organized into domain-specific modules for maintainability:

src/db/schema/
├── index.ts              # Re-exports all schemas
├── auth.ts               # Authentication (users, sessions, accounts)
├── organization.ts       # Organizations, employees, memberships
├── team.ts               # Teams and team members
├── time-tracking.ts      # Legacy work periods and tracking
├── time-record.ts        # Canonical time records and allocations
├── approval.ts           # Approval requests
├── absence.ts            # Absence categories and entries
├── payroll-export.ts     # Payroll export config, jobs, and sync records
└── ...                   # Additional domain modules

Key Tables

TableDescription
time_recordCanonical parent record for work, absence, and break entries
time_record_workWork-specific canonical detail
time_record_absenceAbsence-specific canonical detail
time_record_breakBreak-specific canonical detail
time_record_allocationProject and cost-center splits for work records
time_record_approval_decisionApproval history attached to canonical records
approval_requestApproval request state, including canonical linkage during rollout
payroll_export_configActive export configuration per org and target
payroll_wage_type_mappingMapping from Z8 categories to target-specific payroll codes
payroll_export_jobExport job tracking with status and output metadata
payroll_export_sync_recordIndividual record sync status for API exports

Schema Generation

# Generate Better Auth schema
pnpm --dir apps/webapp run auth:generate

# Push Drizzle schema changes
pnpm --dir apps/webapp drizzle-kit push

Canonical Time Model

Recent time-tracking work centers on a canonical time-record model that normalizes work records, absence records, approval transitions, and downstream payroll/export processing.

The important relationship is:

  • work_period and absence_entry remain important operational sources.
  • approval_request can point at legacy entities while also carrying canonicalRecordId during the canonical model rollout.
  • time_record is the normalized parent record used to represent one scoped unit of tracked time inside an organization.
  • Detail tables such as time_record_work, time_record_absence, and time_record_break capture record-kind-specific fields without duplicating the base record shape.
  • time_record_allocation adds project or cost-center allocation for work records.
  • time_record_approval_decision stores the approval trail directly on the canonical record timeline.

This layout keeps approval, categorization, allocation, and payroll processing aligned around a shared record identity while preserving compatibility with older operational tables where needed.

Payroll Export Architecture

Payroll export data stays organization-scoped end to end.

  • payroll_export_config stores the active export configuration for one organization and one export target.
  • payroll_wage_type_mapping maps work categories, absence categories, and special categories to payroll codes. The organization owns one mapping set that serves every export target. Each row has one code column per target, and each target's export reads only its own column.
  • payroll_export_job stores filters, execution mode, status, and output metadata for each export run.
  • payroll_export_sync_record tracks record-level outcomes for API-based targets so partial success, retry, and audit review are possible.

The schema also reflects the split between file-based and API-based export targets:

  • File formatters produce artifacts such as DATEV, Lexware, Sage, and CSV-style SuccessFactors output.
  • API exporters push normalized attendance and absence data into systems such as Personio, SuccessFactors, and Workday, with target-specific maturity and sync semantics.

That distinction matters because file outputs primarily care about a generated artifact, while API exports need per-record status, external IDs, and retryability.

On this page