Audit Log & Compliance Architecture

This page documents the technical implementation of admin audit logs, database-level triggers, compliance workflows, and automated cron reminders.

Audit Trail ArchitectureCopy Anchor Link

The Danvas audit trail tracks administrative actions to guarantee operational visibility and compliance. All critical operations wrap database mutations and audit logging in a single atomic transaction.

Database Trigger LoggingCopy Anchor Link

Danvas implements database-level logging to protect against auditing gaps. The Drizzle schema uses custom PostgreSQL triggers (packages/database/src/schema/audit_triggers.sql) to write log entries on direct database writes.

Audit Log SchemaCopy Anchor Link

Audit events are stored in the audit_logs table (packages/database/src/schema/audit.ts):

export const auditLogs = pgTable("audit_logs", {
  id: text("id").primaryKey(),
  teamId: text("team_id").notNull(),
  actorId: text("actor_id").notNull().references(() => users.clerkId),
  entityType: text("entity_type").notNull(), // e.g., "user", "incident", "form", "schedule"
  entityId: text("entity_id").notNull(),
  action: text("action").notNull(), // e.g., "user.invited", "schedule.published"
  previousValue: jsonb("previous_value"), // Pre-mutation snapshot
  newValue: jsonb("new_value"), // Post-mutation snapshot
  createdAt: timestamp("created_at").notNull().defaultNow(),
});

Log Transactions & SafetyCopy Anchor Link

logAudit() must run inside the same transaction as the mutation it describes. If a write succeeds but the call to logAudit() happens after the transaction commits, a crash between the two silently drops the audit record and breaks the compliance trail.

PII in Audit MetadataCopy Anchor Link

Audit metadata is plain JSON, so any value that holds a customer contact (email, phone) is a PII leak waiting to happen. Today the audit path stores metadata verbatim — the redaction is only enforced for the Sentry beforeSend hook and the PostHog captureSecureEvent wrapper. When you build an audit entry from user-supplied fields (incident notes, form submissions, the actor's contact), you should run them through scrubPII from @repo/security/pii before constructing the metadata payload. See Security & Compliance → PII Redaction at the Edge for the helper.

Registered Audit Log Action TypesCopy Anchor Link

The database registers the following action types in transactions:

Compliance Tracking ArchitectureCopy Anchor Link

The compliance system has two related stores owned by @repo/compliance: the scheduled-expectation compatibility projection (packages/compliance/ledger.ts) and the authoritative worked-shift obligation fact (packages/compliance/report-obligations.ts). Import these through @repo/compliance/*; scripts/validate-compliance-imports.ts rejects direct table access outside the package.

Compliance Seeding & Reconciliation LogicCopy Anchor Link

Expected compliance records are managed transaction-safely through a series of lifecycle hooks:

1. Schedule finalization (finalizePublishedSchedule):

   • Trigger: Manual and 7shifts publication paths finalize the schedule through packages/schedules/finalize-published-schedule.ts.
   • Behavior: seedFromSchedule writes type: manager compatibility rows and provisional manager_report candidates. It never seeds staff rows. Manager rows are one per employee/service day; shift stores primary-daypart attribution.
   • Revisions: pruneUnbackedManagerExpectations removes only unfiled manager expectations no longer backed by the revised week snapshot. Reopen uses the corresponding scoped cleanup.
   • Staff grain: Schedule finalization does not seed staff shift-report compliance (see ADR-0070).

2. Schedule Reopening Clean-up (cleanFromSchedule / reopenFinalizedSchedule):

   • Trigger: Admin reopens a published schedule back to draft status.
   • Behavior: Removes only unfiled/unbacked manager compatibility expectations in the scoped service week. Filed evidence is preserved.

3. Line-Up Publish Seeding (seedFromLineupPublish):

   • Trigger: Manager publishes a Daily Line-Up card (draft save does not seed).
   • Behavior:
     • Upserts one type: staff compliance row per assignee per service day (location + date), not per daypart.
     • Removes unfilled staff rows for assignees dropped from published lineups that day.
     • Materializes shift-task checklist instances for the daypart.
     • Records Closer from assignment isCloser (ADR-0073).
   • Dual obligations: A staff member on Lunch and Dinner still owes one end-of-shift report for that service day.

4. Account Linking Propagation (propagateEmployeeUserLink):

   • Trigger: Staff member links their newly created Clerk userId to an existing employee profile.
   • Behavior: Backfills the userId column on all unfiled compliance rows matching their employeeKey.

5. Worked-shift obligation reconciliation:

   • reconcileWorkedShiftReportObligationsFromCanonicalFacts loads canonical f_time_entries plus employee, location, and effective canonical-job facts for the exact team/location/service-date partition.
   • The service-day close path invokes this reconciler before closeout preflight; bounded DQ/backfill helpers use the same path. It records source IDs, qualifying jobs, a source digest, generationVersion, dueAt (next-day 4:00 a.m. local), and certificationStatus.
   • Reconciliation marks active obligations certified only when the labor evidence is complete and resolvable; otherwise they remain provisional, and disappeared obligations become superseded.

6. Report Submission Completion:

   • fileReport calls fileReportObligation, which matches an existing applicable obligation by team, location, service date, subject identity/type, and report type. A missing match returns no_applicable_obligation; report submission cannot create its own expectation.
   • A provisional match records a provisional submission. A certified match links the exact report and classifies it filed or late by dueAt; only then is the legacy compliance projection marked filed.

The stores have different completion semantics. compliance.filed is a compatibility signal for scheduled/lineup readers. Authoritative obligation views count certified obligations and distinguish unfiled, draft, filed, late, excused, cancelled, superseded, quarantined, and provisional. late is completed but not on time only when an exact report link and submission/finalization timing evidence are present after dueAt. An overdue row with no settled report remains unfulfilled. Filed and late rows with missing evidence are treated as missing by authoritative summaries, DQ, and exports; a different report cannot replace a settled report without a governed correction path.

Manager Tools → Compliance reads the authoritative report_obligations reader through getReportObligationServiceDaySummaries and getReportObligationDetailsForServiceDate. Its denominator counts only active, certified worked-shift-backed obligations: excused rows are reported separately and excluded from expected; provisional/uncertified candidates stay in DQ diagnostics rather than appearing as expected rows. Display states are on_time, late, pending, in_progress, and missing. Rows with a valid future dueAt are unsettled as pending or in_progress (submission evidence recorded); a valid overdue deadline without settled evidence is missing. Malformed rows with a missing or invalid dueAt fail closed as missing rather than staying pending, and surface in DQ diagnostics via the reported invalid-deadline count. Summary windows are bounded through the current service date using the authorized locations' stored timezones. Managers are scoped to their assigned locations — an empty assignment fails closed and reads nothing — while admins see the whole team.

Current compatibility readers and writers remain explicit: schedule and lineup publication write the projection; account-link propagation updates its legacy userId; /cron/check-compliance, /cron/report-reminder, and their Slack nudge helpers read it; authoritative obligation queries, closeout, reconciliation, DQ, exports, metrics, and the compliance dashboard use report_obligations. No compatibility retirement date is implied.

Compliance Reminder Cron JobsCopy Anchor Link

Automated check crons are deployed in apps/api/app/cron/ and authenticate via CRON_SECRET headers.

1. Weekly Active Check (/cron/check-compliance)Copy Anchor Link

• Schedule: Mondays at 14:00 UTC.
  • Behavior:
    1. Queries all compliance records where filed = false and remindedAt is null (or older than 1 hour).
    2. Sends instant Slack nudges to employees using @repo/slack routing.
    3. Updates remindedAt timestamp to prevent nudge spam.

2. Daily Report Reminder (/cron/report-reminder)Copy Anchor Link

• Schedule: Daily at 20:00 UTC.
• Behavior:
  1. Gathers all compliance records for the previous calendar day where filed = false.
  2. Compiles a summary list of missing staff shift reports and missing manager reports.
  3. Sends a digest report to the configured location management Slack channel.

RelatedCopy Anchor Link