🏠 Overview › ✅ Implementation Tasks ›✅ epic-periodic-summaries-foundation-task-007

Local SQLite cache schema for offline summary access

epic-periodic-summaries-foundation-task-007 — Design and create the local SQLite table (via drift or sqflite) that mirrors the structure of periodic_summaries for offline read access. Include columns for last_synced_at, is_stale, and organisation_id. Write migration scripts that handle schema upgrades without data loss. This schema is the foundation for the SummaryCacheRepository.

high priority low complexity database pending database specialist Tier 1

Acceptance Criteria

A local SQLite table named `periodic_summaries_cache` is created with columns: id (TEXT PRIMARY KEY), organisation_id (TEXT NOT NULL), period_type (TEXT NOT NULL), period_start (TEXT NOT NULL), period_end (TEXT NOT NULL), summary_data (TEXT NOT NULL — JSON blob), last_synced_at (INTEGER NOT NULL — Unix ms), is_stale (INTEGER NOT NULL DEFAULT 0 — boolean), created_at (INTEGER NOT NULL)
A composite index exists on (organisation_id, period_type, period_start) to support efficient lookups by org and period
Migration version 1 → 2 (and any future version) runs without data loss on an existing database that has rows in the table
Schema is defined using drift DAOs/table classes (preferred) or sqflite raw SQL; the choice is consistent with the rest of the project
A migration test asserts that applying the migration on a pre-populated database preserves all existing rows with correct field values
The schema file is covered by a Dart test that creates an in-memory database, verifies all columns exist with correct types, and inserts + queries a sample row successfully
organisation_id is never nullable — the schema enforces NOT NULL at the database level
A `CHECK (is_stale IN (0, 1))` constraint is present on the is_stale column

Technical Requirements

frameworks
Flutter
drift (recommended) or sqflite
flutter_test
data models
periodic_summaries (Supabase remote schema mirror)
SummaryPeriodModel
performance requirements
Table lookup by (organisation_id, period_type) must complete in under 10 ms on a device with 10 000 cached rows
Migration must complete in under 500 ms regardless of row count
security requirements
organisation_id must be enforced NOT NULL at schema level — no row may exist without an org owner
summary_data JSON blob must not contain raw PII beyond what is already stored in Supabase; minimise stored fields to aggregated metrics only
Database file must reside in the app's private storage directory (not accessible to other apps)

Execution Context

Execution Tier
Tier 1

Tier 1 - 540 tasks

Can start after Tier 0 completes

Implementation Notes

Use drift if the rest of the project already depends on it — its type-safe query builder and migration API (`MigrationStrategy.onUpgrade`) are far safer than raw sqflite SQL strings. Define the table as a drift `Table` class so the schema is a single source of truth. Store dates as Unix milliseconds (INTEGER) rather than ISO strings to avoid locale-dependent parsing bugs. The `summary_data` column should store the full serialised `SummaryPeriodModel` JSON so the cache layer never needs to re-parse Supabase responses.

Mark `is_stale` as an integer (0/1) rather than a Dart bool because SQLite has no native boolean type — drift's `BoolColumn` handles this transparently. When writing migration scripts, always add new columns with DEFAULT values so existing rows remain valid without a full table rebuild.

Testing Requirements

Write flutter_test unit tests (in-memory database) covering: (1) schema creation succeeds and all columns exist with correct types; (2) INSERT of a valid row succeeds and SELECT returns the same values; (3) INSERT with null organisation_id throws a database exception; (4) is_stale CHECK constraint rejects values other than 0 and 1; (5) migration from v1 to v2 preserves existing rows. Use drift's in-memory database or sqflite's in-memory factory so tests run without a device. Target 100% statement coverage of the migration logic.

Component
Summary Cache Repository
data low
Dependencies (1)
Create and apply a PostgreSQL migration that adds the periodic_summaries table with columns for organisation_id, period_type (weekly/monthly/quarterly), period_start, period_end, session_count, total_hours, generated_at, and a JSONB payload column for flexible aggregation data. Include appropriate indexes on organisation_id and period boundaries for query performance. epic-periodic-summaries-foundation-task-001
Epic Risks (3)
high impact medium prob security

Supabase RLS policies for aggregation views are more complex than single-table policies. A misconfigured policy could silently allow a coordinator in one organisation to see data from another, causing a data breach and breaking trust with participating organisations.

Mitigation & Contingency

Mitigation: Write automated RLS integration tests that create two separate organisations with distinct data, then assert that queries authenticated as org-A users return only org-A rows. Run these tests in CI on every PR touching the database layer.

Contingency: If an RLS bypass is discovered post-deployment, immediately disable the periodic summaries feature flag, revoke affected sessions, audit access logs, notify affected organisations, and patch the policy before re-enabling.

medium impact medium prob technical

Activity records may span multiple sessions types, proxy registrations, and coordinator bulk entries. Incorrect JOIN logic or missing filters in the aggregation query could double-count sessions or omit activity types, producing inaccurate summaries that erode user trust.

Mitigation & Contingency

Mitigation: Build a fixture dataset covering all activity registration paths (direct, proxy, bulk) and assert expected aggregated counts in integration tests before any UI consumes the repository.

Contingency: If inaccurate counts are reported post-launch, mark affected summaries as invalidated in the database and re-trigger generation once the query is corrected. Communicate transparently to affected users via an in-app banner.

low impact low prob integration

The local cache must be invalidated when a new summary arrives via push notification. If the push token is stale or the FCM/APNs delivery is delayed, the device may show an outdated summary for an extended period, confusing users who see different numbers online versus offline.

Mitigation & Contingency

Mitigation: Implement a TTL on cached summaries (max 48 hours) so stale data is auto-cleared even without a push notification. Also trigger cache refresh on app foreground if the current period's summary is older than 24 hours.

Contingency: Provide a manual pull-to-refresh on the summary card that bypasses the cache and fetches directly from Supabase when a network connection is available.

Quick Links
All Tasks Execution Plan