Database Schema

Core Tables

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

• 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

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 target-specific payroll codes.
• 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.