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=disableManaged 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.pemPOSTGRES_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 modulesKey Tables
| Table | Description |
|---|---|
time_record | Canonical parent record for work, absence, and break entries |
time_record_work | Work-specific canonical detail |
time_record_absence | Absence-specific canonical detail |
time_record_break | Break-specific canonical detail |
time_record_allocation | Project and cost-center splits for work records |
time_record_approval_decision | Approval history attached to canonical records |
approval_request | Approval request state, including canonical linkage during rollout |
payroll_export_config | Active export configuration per org and target |
payroll_wage_type_mapping | Mapping from Z8 categories to target-specific payroll codes |
payroll_export_job | Export job tracking with status and output metadata |
payroll_export_sync_record | Individual 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 pushCanonical 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_periodandabsence_entryremain important operational sources.approval_requestcan point at legacy entities while also carryingcanonicalRecordIdduring the canonical model rollout.time_recordis 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, andtime_record_breakcapture record-kind-specific fields without duplicating the base record shape. time_record_allocationadds project or cost-center allocation for work records.time_record_approval_decisionstores 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_configstores the active export configuration for one organization and one export target.payroll_wage_type_mappingmaps 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_jobstores filters, execution mode, status, and output metadata for each export run.payroll_export_sync_recordtracks 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.