# ADR-0007: Schema Extension — Phase 2 Missing Entities (Task 2.1)

**Date:** 2026-08-22  
**Status:** Accepted  
**Task:** 2.1 — Extend Schema with Missing Entities  
**Author:** Autonomous Agent Cycle

---

## Context

The Phase 2 completion criteria for ChronoGrid P2P require 13 total database tables (7 original Phase 1 tables + 6 new Phase 2 extension tables). The architecture review (ADL-006) identified 6 missing schema tables as a critical gap blocking Phase 3 (Flutter UI requires calendar/time-block entities) and Phase 4 (AI/automation requires ingestion and rule entities).

The canonical schema at `docs/API_CONTRACTS/schema.sql` is embedded at compile time via `include_str!` in `crates/chronogrid_core/src/db.rs`, making it the single source of truth for all database structure.

---

## Decision

### New Tables Added

Six new tables were added to `docs/API_CONTRACTS/schema.sql`:

#### 1. `time_blocks`
- **Purpose:** Calendar time-blocking entries linked to tasks. Represents a scheduled block of time on the calendar grid.
- **Key fields:** `id`, `namespace_id`, `task_id` (nullable FK → tasks, SET NULL), `title`, `start_time`, `end_time`, `color_hex`, `is_all_day`, `recurrence_rule`, `hlc_timestamp`
- **CRDT:** Supports `entity_type = 'time_block'` in `crdt_docs` table (documented in comment and tested).
- **Indexes:** `idx_time_blocks_namespace`, `idx_time_blocks_task`, `idx_time_blocks_start`

#### 2. `attachments`
- **Purpose:** Binary/file attachments linked to notes or ingestion logs.
- **Key fields:** `id`, `note_id` (nullable FK → notes, CASCADE), `ingestion_log_id` (nullable FK → ingestion_logs, CASCADE), `file_name`, `mime_type`, `file_size_bytes`, `storage_path`, `checksum_sha256`, `hlc_timestamp`
- **Design note:** `storage_path` is a relative path inside the encrypted vault directory; actual file bytes are stored outside SQLite to avoid BLOB size limits.
- **Indexes:** `idx_attachments_note`, `idx_attachments_ingestion`

#### 3. `calendar_feeds`
- **Purpose:** CalDAV external feed configuration per namespace.
- **Key fields:** `id`, `namespace_id` (FK → namespaces, CASCADE), `display_name`, `feed_url`, `username`, `encrypted_password_blob` (AES-GCM encrypted, key from keychain), `sync_interval_seconds`, `last_synced_at`, `is_enabled`, `hlc_timestamp`
- **Security:** Credentials are stored as encrypted blobs — never plaintext.
- **Indexes:** `idx_calendar_feeds_namespace`

#### 4. `node_peers`
- **Purpose:** P2P peer registry — NodeId, public key, last seen, swarm membership.
- **Key fields:** `id` (NodeId: Ed25519 public key hash hex), `public_key_bytes` (BLOB, 32 bytes), `swarm_key_fingerprint`, `last_seen_at`, `last_known_addrs` (JSON array of multiaddr strings), `is_trusted`
- **Design note:** No FK to `namespaces` — peers are global to the node, not namespace-scoped.
- **No `hlc_timestamp`:** Peer registry entries are managed by the networking layer, not the CRDT engine.
- **Indexes:** `idx_node_peers_swarm`, `idx_node_peers_last_seen`

#### 5. `automation_rules`
- **Purpose:** Local trigger/action rule definitions for the automation engine.
- **Key fields:** `id`, `namespace_id` (FK → namespaces, CASCADE), `name`, `trigger_type` (`task_created` | `task_status_changed` | `ingestion_received` | `schedule`), `trigger_config` (JSON), `action_type` (`set_field` | `create_task` | `send_notification` | `run_script`), `action_config` (JSON), `is_enabled`, `execution_count`, `last_executed_at`, `hlc_timestamp`
- **Indexes:** `idx_automation_rules_namespace`, `idx_automation_rules_trigger`

#### 6. `ingestion_configs`
- **Purpose:** IMAP account and file-watcher configuration per namespace.
- **Key fields:** `id`, `namespace_id` (FK → namespaces, CASCADE), `config_type` (`imap` | `file_watcher` | `hotkey`), IMAP-specific fields (`imap_host`, `imap_port`, `imap_username`, `encrypted_imap_password_blob`, `imap_mailbox`, `imap_use_tls`), file-watcher fields (`watch_path`, `watch_extensions` JSON array), `poll_interval_seconds`, `last_polled_at`, `hlc_timestamp`
- **Design note:** Single table covers multiple ingestion types via `config_type` discriminator; type-specific fields are nullable for non-applicable types.
- **Indexes:** `idx_ingestion_configs_namespace`, `idx_ingestion_configs_type`

---

## CRDT Compatibility

All 6 new tables include `hlc_timestamp TEXT NOT NULL` (except `node_peers`, which is managed by the networking layer and does not participate in CRDT sync). The `crdt_docs` table comment was updated to document `time_block` as a valid `entity_type` value alongside `task` and `note`.

---

## Code Changes

| File | Change |
|------|--------|
| `docs/API_CONTRACTS/schema.sql` | Added 6 new tables + 12 new indexes |
| `crates/chronogrid_core/src/db.rs` | Updated `EXPECTED_TABLES` from 7 to 13 entries |
| `crates/chronogrid_core/src/db.rs` | Added 8 new unit tests covering all Phase 2 tables |

---

## Test Coverage Added

- `schema_has_exactly_13_tables` — asserts total table count = 13
- `open_in_memory_creates_indexes` — expanded to cover all 17 indexes (5 Phase 1 + 12 Phase 2)
- `crud_time_blocks` — insert, read, FK SET NULL on task deletion
- `crud_attachments` — insert, read, CASCADE delete with note
- `crud_calendar_feeds` — insert, read, CASCADE delete with namespace
- `crud_node_peers` — insert, read, update `last_seen_at`
- `crud_automation_rules` — insert, read, CASCADE delete with namespace
- `crud_ingestion_configs` — IMAP + file-watcher configs, CASCADE delete
- `crdt_doc_time_block_entity_type` — verifies `time_block` entity_type in `crdt_docs`

---

## Consequences

- **Positive:** All Phase 2 completion criteria for schema are met (13 tables confirmed by verification script).
- **Positive:** `EXPECTED_TABLES` validation in `db.rs` now enforces all 13 tables on every database open, preventing schema drift.
- **Positive:** Existing Phase 1 tests are unaffected — all DDL uses `CREATE TABLE IF NOT EXISTS`.
- **Neutral:** `node_peers` intentionally omits `hlc_timestamp` as it is managed by `chronogrid_net`, not the CRDT engine.
- **Neutral:** `attachments.storage_path` stores relative paths; the vault directory root is resolved at runtime by the application layer.