🏠 Overview › ✅ Implementation Tasks ›✅ epic-receipt-capture-and-attachment-foundation-task-001

SQLite schema for claim-receipt association table

epic-receipt-capture-and-attachment-foundation-task-001 — Design and implement the SQLite database schema for the claim_receipt_attachments table, including columns for claim_id, receipt_storage_path, org_id, user_id, file_size_bytes, compressed_size_bytes, mime_type, created_at, and a local status flag for sync tracking. Apply proper indexes for claim-scoped queries.

critical priority low complexity database pending database specialist Tier 0

Acceptance Criteria

CREATE TABLE migration script for claim_receipt_attachments is added to the versioned migration system with a monotonically increasing version number
Table includes columns: id (INTEGER PRIMARY KEY AUTOINCREMENT), claim_id (TEXT NOT NULL), receipt_storage_path (TEXT NOT NULL), org_id (TEXT NOT NULL), user_id (TEXT NOT NULL), file_size_bytes (INTEGER NOT NULL), compressed_size_bytes (INTEGER NOT NULL), mime_type (TEXT NOT NULL DEFAULT 'image/jpeg'), created_at (TEXT NOT NULL DEFAULT (datetime('now'))), sync_status (TEXT NOT NULL DEFAULT 'pending' CHECK(sync_status IN ('pending','synced','failed')))
Composite index on (claim_id, created_at DESC) exists for claim-scoped chronological queries
Index on (sync_status) exists for efficient pending-sync queries
Migration runs without errors on a fresh SQLite database
Migration is idempotent — running it twice does not produce an error
Schema is documented with inline SQL comments explaining sync_status values and path convention
No breaking changes to existing tables — migration is additive only

Technical Requirements

frameworks
Flutter
sqflite or drift (whichever is the project's chosen SQLite library)
data models
ClaimReceiptAttachment
performance requirements
Index on (claim_id, created_at DESC) must support queries returning all receipts for a claim in under 5ms for up to 1000 rows
security requirements
receipt_storage_path must store only the relative Supabase Storage path, never a full signed URL (URLs expire and must not be cached)
No personally identifiable data beyond user_id and org_id stored in local SQLite

Execution Context

Execution Tier
Tier 0

Tier 0 - 440 tasks

Implementation Notes

Use the project's existing database versioning pattern — do not introduce a new migration runner. The sync_status column drives offline-first behaviour: 'pending' means inserted locally but not yet confirmed synced to Supabase, 'synced' means the Supabase row exists, 'failed' means a sync attempt failed and retry is needed. Store receipt_storage_path as the relative path only (e.g., receipts/{org_id}/{user_id}/{claim_id}/{filename}) matching the Supabase Storage bucket structure defined in task-004. This separation ensures signed URLs are always freshly generated on read.

Use TEXT for all IDs to match Supabase UUID strings. Use TEXT ISO-8601 for created_at rather than INTEGER epoch to keep the schema human-readable in debug tooling.

Testing Requirements

Unit test the migration itself: open an in-memory SQLite database, run the migration, then assert the table exists with the correct columns and types using PRAGMA table_info(claim_receipt_attachments). Assert all indexes exist using PRAGMA index_list. Run the migration twice and assert no exception is thrown (idempotency). These tests belong in the ClaimReceiptRepository test file created in task-003.

Component
Claim Receipt Repository
data low
Epic Risks (3)
high impact medium prob security

Supabase Storage RLS policies using org/user/claim path scoping may not enforce correctly if claim ownership is not present in the JWT or if path segments are constructed differently at upload vs. read time, leading to data leakage or access denial for legitimate users.

Mitigation & Contingency

Mitigation: Define and test RLS policies in isolation before wiring to app code. Write integration tests that assert cross-org and cross-user access is denied. Use service-role key only in edge functions, never in client code.

Contingency: If client-side RLS proves insufficient, route all storage reads through a Supabase Edge Function that validates ownership before generating signed URLs, adding a controlled server-side enforcement layer.

high impact medium prob technical

Aggressive image compression may reduce receipt legibility below the threshold required for financial auditing, causing claim rejections or compliance failures despite technically successful uploads.

Mitigation & Contingency

Mitigation: Define minimum legibility requirements with HLF finance team before implementation. Set compression targets conservatively (e.g., max 1MB, min 80% JPEG quality) and validate with sample receipt images. Provide compression statistics in verbose/debug mode.

Contingency: If post-compression quality is disputed by auditors, increase the quality floor at the cost of larger file sizes, and add a manual override allowing users to skip compression for PDFs and high-quality scans.

medium impact medium prob dependency

The Flutter image_picker package behaves differently on iOS 17+ (PHPicker) vs older Android (Intent-based), particularly for file types, permission flows, and PDF selection, which may cause platform-specific failures not caught in development.

Mitigation & Contingency

Mitigation: Test image picker integration on physical devices for both platforms early in the sprint. Pin the image_picker package version and review changelogs before updates. Write widget tests using mock file results for each platform branch.

Contingency: If PHPicker or Android Intent differences cause blocking issues, implement separate platform-specific picker delegates behind the unified interface, allowing platform-specific fixes without breaking the shared API.

Quick Links
All Tasks Execution Plan