feat: Email ingestion database schema and types (#149) #154

New Issue

egullickson · 2026-02-13T03:51:43Z

egullickson commented

2026-02-13 03:51:43 +00:00

Relates to #149

Scope

Create the database migrations and TypeScript types for the email ingestion feature.

Database Tables

email_ingestion_queue: id, email_id (UNIQUE), sender_email, user_id, received_at, subject, status (pending/processing/completed/failed), processing_result JSONB, error_message, retry_count, created_at, updated_at
pending_vehicle_associations: id, user_id, record_type (fuel_log/maintenance_record), extracted_data JSONB, document_id (FK documents), status (pending/resolved/expired), created_at, resolved_at

Email Templates (seed data)

receipt_processed: Confirmation when email receipt is successfully processed
receipt_failed: Error notification when processing fails
receipt_pending_vehicle: Notification when multi-vehicle user needs to select vehicle

TypeScript Types

EmailIngestionQueueRecord, PendingVehicleAssociation
EmailIngestionStatus, PendingAssociationStatus
Webhook payload types for Resend email.received event

Files

backend/src/features/email-ingestion/migrations/001_create_email_ingestion_tables.sql
backend/src/features/email-ingestion/migrations/002_create_email_templates.sql
backend/src/features/email-ingestion/domain/email-ingestion.types.ts

Acceptance Criteria

email_ingestion_queue table created with UNIQUE constraint on email_id
pending_vehicle_associations table created with proper FKs
3 new email templates seeded
All TypeScript types defined

Relates to #149 ## Scope Create the database migrations and TypeScript types for the email ingestion feature. ### Database Tables - `email_ingestion_queue`: id, email_id (UNIQUE), sender_email, user_id, received_at, subject, status (pending/processing/completed/failed), processing_result JSONB, error_message, retry_count, created_at, updated_at - `pending_vehicle_associations`: id, user_id, record_type (fuel_log/maintenance_record), extracted_data JSONB, document_id (FK documents), status (pending/resolved/expired), created_at, resolved_at ### Email Templates (seed data) - `receipt_processed`: Confirmation when email receipt is successfully processed - `receipt_failed`: Error notification when processing fails - `receipt_pending_vehicle`: Notification when multi-vehicle user needs to select vehicle ### TypeScript Types - `EmailIngestionQueueRecord`, `PendingVehicleAssociation` - `EmailIngestionStatus`, `PendingAssociationStatus` - Webhook payload types for Resend `email.received` event ### Files - `backend/src/features/email-ingestion/migrations/001_create_email_ingestion_tables.sql` - `backend/src/features/email-ingestion/migrations/002_create_email_templates.sql` - `backend/src/features/email-ingestion/domain/email-ingestion.types.ts` ## Acceptance Criteria - [ ] email_ingestion_queue table created with UNIQUE constraint on email_id - [ ] pending_vehicle_associations table created with proper FKs - [ ] 3 new email templates seeded - [ ] All TypeScript types defined

egullickson added the

labels 2026-02-13 03:52:46 +00:00

egullickson added this to the Sprint 2026-02-02 milestone 2026-02-13 03:52:56 +00:00

egullickson referenced this issue

2026-02-13 03:54:19 +00:00

feat: Email Receipt Ingestion via Resend Webhooks #149

egullickson added

and removed

labels 2026-02-13 03:56:29 +00:00

egullickson commented

2026-02-13 04:01:29 +00:00

Milestone: Email Ingestion Database Schema and Types

Phase: Execution | Agent: Developer | Status: PASS

Completed

backend/src/features/email-ingestion/migrations/001_create_email_ingestion_tables.sql -- email_ingestion_queue (UUID PK, UNIQUE email_id, status CHECK, JSONB processing_result, retry_count) and pending_vehicle_associations (UUID PK, record_type CHECK, documents FK with ON DELETE SET NULL, status CHECK) with indexes and triggers
backend/src/features/email-ingestion/migrations/002_create_email_templates.sql -- Extended email_templates CHECK constraint with 3 new keys; seeded receipt_processed, receipt_failed, receipt_pending_vehicle templates with full HTML bodies and ON CONFLICT upsert
backend/src/features/email-ingestion/domain/email-ingestion.types.ts -- EmailIngestionQueueRecord, PendingVehicleAssociation interfaces; EmailIngestionStatus, PendingAssociationStatus, EmailRecordType enums; Resend webhook payload types (ResendWebhookEvent, ResendWebhookEventData, ResendEmailAttachment); EmailProcessingResult and ExtractedReceiptData
backend/src/_system/migrations/run-all.ts -- Registered features/email-ingestion in MIGRATION_ORDER after notifications

Validation

TypeScript type-check: PASS (0 errors)
ESLint: PASS (0 errors, 650 pre-existing warnings)
Commit: 877f844 feat: add email ingestion database schema and types (refs #154)

Verdict: PASS | Next: Proceed with remaining #149 sub-issues

## Milestone: Email Ingestion Database Schema and Types **Phase**: Execution | **Agent**: Developer | **Status**: PASS ### Completed - `backend/src/features/email-ingestion/migrations/001_create_email_ingestion_tables.sql` -- email_ingestion_queue (UUID PK, UNIQUE email_id, status CHECK, JSONB processing_result, retry_count) and pending_vehicle_associations (UUID PK, record_type CHECK, documents FK with ON DELETE SET NULL, status CHECK) with indexes and triggers - `backend/src/features/email-ingestion/migrations/002_create_email_templates.sql` -- Extended email_templates CHECK constraint with 3 new keys; seeded receipt_processed, receipt_failed, receipt_pending_vehicle templates with full HTML bodies and ON CONFLICT upsert - `backend/src/features/email-ingestion/domain/email-ingestion.types.ts` -- EmailIngestionQueueRecord, PendingVehicleAssociation interfaces; EmailIngestionStatus, PendingAssociationStatus, EmailRecordType enums; Resend webhook payload types (ResendWebhookEvent, ResendWebhookEventData, ResendEmailAttachment); EmailProcessingResult and ExtractedReceiptData - `backend/src/_system/migrations/run-all.ts` -- Registered features/email-ingestion in MIGRATION_ORDER after notifications ### Validation - TypeScript type-check: PASS (0 errors) - ESLint: PASS (0 errors, 650 pre-existing warnings) - Commit: `877f844` feat: add email ingestion database schema and types (refs #154) *Verdict*: PASS | *Next*: Proceed with remaining #149 sub-issues

egullickson referenced this issue from a commit

2026-02-13 16:34:29 +00:00

feat: add email ingestion database schema and types (refs #154)

egullickson referenced a pull request that will close this issue

2026-02-13 16:34:49 +00:00

feat: Maintenance Receipt Upload with OCR Auto-populate (#16) #161

egullickson closed this issue

2026-02-13 22:19:46 +00:00

Sign in to join this conversation.