ADR 020 Construction Diary Architecture

ADR-020: Construction Diary Architecture

Status

Accepted

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: 500 KB per signature.

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: Completely immutable. Cannot be updated or deleted. They serve as an audit trail.

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.