ADR-020: Construction Diary Architecture

Status

Accepted. §3 (automatic events) is superseded in part by ADR-040 (planned, EPIC-21 UX restructure): events stay fire-and-forget, but carry versioned parameters in metadata, are de-duplicated at write time, and move from the diary to History.

Context

The Cornerstone application needs a construction diary (Bautagebuch) — a formal project documentation tool that combines manual user entries with automatic system-generated events. This is a common requirement in German construction project management, used for bank reporting, insurance claims, and dispute resolution.

Key design questions:

1. Schema flexibility: Manual entries have 5 different types (daily_log, site_visit, delivery, issue, general_note) with varying metadata fields. Automatic entries have 6 different types with varying context data. How do we model type-specific fields?
2. Automatic event capture: System events (status changes, budget breaches, schedule changes) should be logged automatically. How do we integrate this without coupling the diary to every service?
3. Signature storage: Daily logs and site visits require captured signatures. Where and how do we store them?
4. Photo integration: Diary entries need photo attachments. How do we leverage the existing photo infrastructure?

Decision

1. Single Table with JSON Metadata Column

All diary entries — both manual and automatic — are stored in a single diary_entries table. Type-specific structured fields are stored in a metadata TEXT column containing a JSON string.

Alternatives considered:

• Separate tables per entry type: Would create 11 tables (5 manual + 6 automatic) with largely overlapping columns. Over-engineered for <5 users; complicates queries that span all entries (the timeline view).
• Column-per-field with NULLs: Would add 15+ nullable columns to a single table, most of which are NULL for any given row. Cluttered schema, difficult to evolve.
• EAV (Entity-Attribute-Value): Flexible but queries become unwieldy and lose type safety.

Why JSON metadata: SQLite handles JSON well (json_extract for queries if needed). The metadata shape is validated at the application layer using TypeScript type guards. New entry types or metadata fields can be added without schema migrations. The trade-off — no database-level constraint on metadata structure — is acceptable because validation is centralized in one service and the user base is tiny.

2. Polymorphic Source Entity References

Automatic diary entries link back to their source entity via source_entity_type + source_entity_id columns. This follows the same polymorphic reference pattern used by document_links and photos tables.

No foreign key constraint exists on source_entity_id because it references different tables depending on source_entity_type. Referential integrity is enforced at the application layer. If a source entity is deleted, the diary entry remains as a historical record (intentionally — the diary is an immutable audit log for automatic events).

3. Fire-and-Forget Automatic Events

Automatic diary entries are created by a diaryEventService that is called from existing services (work item, invoice, milestone, budget, scheduling, subsidy) after their primary operation succeeds. The diary event creation:

• Runs inside the same database transaction where possible
• Never throws — errors are logged at warn level but do not propagate to the caller
• Never blocks or delays the triggering operation

This decoupled pattern means that a failure to create a diary entry never causes a user-facing error. The diary is a best-effort audit trail.

4. Signatures as Base64 Data URLs in Metadata

Signatures are captured as canvas drawings and stored as base64-encoded data URLs in the metadata JSON (within the signatureDataUrl field). Maximum size: 512 KB per signature (enforced server-side since the #2124/#2125 security review, which also restricts the type to data:image/(png|jpeg|webp) caps an entry at 10 signatures, and caps signerName at 300 characters after trimming).

Alternatives considered:

• Separate file storage: Would require a new file management subsystem. Overkill for a small PNG/SVG signature image.
• Separate signatures table with BLOB: Adds schema complexity for a simple use case.

Why inline base64: Signatures are small (typically 5-50 KB as PNG), created once and never modified. Storing them inline in metadata keeps the implementation simple — no file I/O, no cleanup on delete, no separate API endpoints. The 500 KB limit prevents abuse while accommodating high-resolution captures.

5. Existing Photo Infrastructure Reuse

Photo attachments use the existing photos table and /api/photos endpoints with entity_type = 'diary_entry'. The PhotoEntityType union already includes 'diary_entry'. No new photo endpoints, tables, or services are needed.

When a diary entry is deleted, the diary service must also delete associated photos (both database records and files on disk) via the photo service's cascade delete method.

6. Entry Immutability Rules

• Manual entries: Fully editable (title, body, metadata, entry_date). The entry_type and is_automatic flag cannot be changed after creation.
• Automatic entries: Cannot be updated or promoted. They can be deleted (story #808 superseded the original "cannot be deleted" rule).
• Signed entries: Locked only once saved (#2124) — see ADR-022 and the Schema page's diary immutability rules.

7. PDF Export

The diary export endpoint (GET /api/diary-entries/export) generates a PDF document server-side. The export:

• Accepts dateFrom and dateTo query parameters for date range filtering
• Includes all entry types (manual and automatic) within the range
• Embeds attached photos (thumbnails) and signatures inline
• Uses a pure JavaScript PDF generation library (no native dependencies)

Consequences

Easier

• Adding new entry types: Just add a value to the CHECK constraint (migration), a TypeScript type for the metadata, and validation logic. No structural schema change needed.
• Timeline queries: Single table makes it trivial to query all entries sorted by date.
• Photo attachments: Zero new infrastructure — reuses the battle-tested photo system.
• Auto events: Fire-and-forget pattern means no risk of diary bugs breaking core functionality.

Harder

• Metadata validation: No database-level enforcement of metadata structure. Must be thorough in the application layer. Type-specific validation logic lives in one place (diary service) but could become complex as entry types grow.
• Source entity orphaning: If a work item is deleted, its diary entries remain with a dangling source_entity_id. The UI must handle "source entity not found" gracefully. This is intentional — diary entries are historical records.
• Signature size: Base64 encoding adds ~33% overhead to binary data. A 50 KB PNG signature becomes ~67 KB in the JSON. Acceptable at this scale but would not be appropriate for a high-volume system.

Deviation Log

Date	Section	Deviation	Resolution
2026-10-01	6. Entry Immutability Rules	Said automatic entries cannot be deleted. Story #808 deliberately allows it (server/src/routes/diary.ts, asserted by diary.test.ts), and the section did not mention the signature lock.	Rule amended in place: automatic entries can be deleted; signed entries lock only once saved (#2124, see ADR-022). This table did not previously exist on this page and was created by this entry.
2026-10-01	4. Signatures as Base64 Data URLs in Metadata	PR #2152 architecture and security review. Stated a 500 KB maximum per signature that was never enforced.	Amended to the enforced 512 KB cap, with the image-type and 10-per-entry rules from the #2124/#2125 security review.
2026-10-01	4. Signatures as Base64 Data URLs in Metadata	Correction to the same-day signature-validation entry: signerName was documented as capped at 200 characters after trimming; the enforced cap is 300, because vendor names may be 200 characters and the composed signer name is vendor (signatory).	Changed 200 to 300 with that rationale.