Ryan Chen
800c6fef7f
Phase 01: Foundation - 2 plan(s) in 2 wave(s) - 1 parallel, 1 sequential - Ready for execution
209 lines
8.7 KiB
Markdown
209 lines
8.7 KiB
Markdown
---
|
|
phase: 01-foundation
|
|
plan: 01
|
|
type: execute
|
|
wave: 1
|
|
depends_on: []
|
|
files_modified:
|
|
- blueprints/email/__init__.py
|
|
- blueprints/email/models.py
|
|
- blueprints/email/crypto_service.py
|
|
- .env.example
|
|
- migrations/models/XX_YYYYMMDDHHMMSS_add_email_tables.py
|
|
autonomous: true
|
|
|
|
must_haves:
|
|
truths:
|
|
- "Database tables for email_accounts, email_sync_status, and emails exist in PostgreSQL"
|
|
- "IMAP credentials are encrypted when stored and decrypted when retrieved"
|
|
- "Fernet encryption key can be generated and validated on app startup"
|
|
artifacts:
|
|
- path: "blueprints/email/models.py"
|
|
provides: "EmailAccount, EmailSyncStatus, Email Tortoise ORM models"
|
|
min_lines: 80
|
|
contains: "class EmailAccount(Model)"
|
|
- path: "blueprints/email/crypto_service.py"
|
|
provides: "EncryptedTextField and Fernet key validation"
|
|
min_lines: 40
|
|
exports: ["EncryptedTextField", "validate_fernet_key"]
|
|
- path: ".env.example"
|
|
provides: "FERNET_KEY environment variable example"
|
|
contains: "FERNET_KEY="
|
|
- path: "migrations/models/"
|
|
provides: "Database migration for email tables"
|
|
pattern: "*_add_email_tables.py"
|
|
key_links:
|
|
- from: "blueprints/email/models.py"
|
|
to: "blueprints/email/crypto_service.py"
|
|
via: "EncryptedTextField import"
|
|
pattern: "from.*crypto_service import EncryptedTextField"
|
|
- from: "blueprints/email/models.py"
|
|
to: "blueprints/users/models.py"
|
|
via: "ForeignKeyField to User"
|
|
pattern: 'fields\\.ForeignKeyField\\("models\\.User"'
|
|
---
|
|
|
|
<objective>
|
|
Establish database foundation and credential encryption for email ingestion system.
|
|
|
|
Purpose: Create the data layer that stores email account configuration, sync tracking, and email metadata. Implement secure credential storage using Fernet symmetric encryption so IMAP passwords can be safely stored and retrieved.
|
|
|
|
Output: Tortoise ORM models for email entities, encrypted password field implementation, database migration, and environment configuration.
|
|
</objective>
|
|
|
|
<execution_context>
|
|
@/Users/ryanchen/.claude/get-shit-done/workflows/execute-plan.md
|
|
@/Users/ryanchen/.claude/get-shit-done/templates/summary.md
|
|
</execution_context>
|
|
|
|
<context>
|
|
@.planning/PROJECT.md
|
|
@.planning/ROADMAP.md
|
|
@.planning/STATE.md
|
|
@.planning/phases/01-foundation/01-RESEARCH.md
|
|
@blueprints/users/models.py
|
|
@blueprints/conversation/models.py
|
|
@.env.example
|
|
</context>
|
|
|
|
<tasks>
|
|
|
|
<task type="auto">
|
|
<name>Task 1: Create email blueprint with encrypted Tortoise ORM models</name>
|
|
<files>
|
|
blueprints/email/__init__.py
|
|
blueprints/email/models.py
|
|
blueprints/email/crypto_service.py
|
|
</files>
|
|
<action>
|
|
Create `blueprints/email/` directory with three files following existing blueprint patterns:
|
|
|
|
**1. crypto_service.py** - Implement Fernet encryption for credentials:
|
|
- Create `EncryptedTextField` class extending `fields.TextField`
|
|
- Override `to_db_value()` to encrypt strings before database storage
|
|
- Override `to_python_value()` to decrypt strings when loading from database
|
|
- Load FERNET_KEY from environment variable in `__init__`
|
|
- Raise ValueError if FERNET_KEY is missing or invalid
|
|
- Add `validate_fernet_key()` function that tests encrypt/decrypt cycle
|
|
- Follow pattern from RESEARCH.md Example 2 (line 581-619)
|
|
|
|
**2. models.py** - Create three Tortoise ORM models following existing patterns:
|
|
|
|
`EmailAccount`:
|
|
- UUIDField primary key
|
|
- ForeignKeyField to models.User (related_name="email_accounts")
|
|
- email_address CharField(255) unique
|
|
- display_name CharField(255) nullable
|
|
- imap_host CharField(255)
|
|
- imap_port IntField default=993
|
|
- imap_username CharField(255)
|
|
- imap_password EncryptedTextField() - transparently encrypted
|
|
- is_active BooleanField default=True
|
|
- last_error TextField nullable
|
|
- created_at/updated_at DatetimeField with auto_now_add/auto_now
|
|
- Meta: table = "email_accounts"
|
|
|
|
`EmailSyncStatus`:
|
|
- UUIDField primary key
|
|
- ForeignKeyField to EmailAccount (related_name="sync_status", unique=True)
|
|
- last_sync_date DatetimeField nullable
|
|
- last_message_uid IntField default=0
|
|
- message_count IntField default=0
|
|
- consecutive_failures IntField default=0
|
|
- last_failure_date DatetimeField nullable
|
|
- updated_at DatetimeField auto_now
|
|
- Meta: table = "email_sync_status"
|
|
|
|
`Email`:
|
|
- UUIDField primary key
|
|
- ForeignKeyField to EmailAccount (related_name="emails")
|
|
- message_id CharField(255) unique, indexed (RFC822 Message-ID)
|
|
- subject CharField(500)
|
|
- from_address CharField(255)
|
|
- to_address TextField
|
|
- date DatetimeField
|
|
- body_text TextField nullable
|
|
- body_html TextField nullable
|
|
- chromadb_doc_id CharField(255) nullable
|
|
- created_at DatetimeField auto_now_add
|
|
- expires_at DatetimeField (auto-set to created_at + 30 days)
|
|
- Override async save() to auto-set expires_at if not set
|
|
- Meta: table = "emails"
|
|
|
|
Follow conventions from blueprints/conversation/models.py and blueprints/users/models.py.
|
|
|
|
**3. __init__.py** - Create empty blueprint registration file:
|
|
- Create Quart Blueprint named "email_blueprint" with url_prefix="/api/email"
|
|
- Import models for Tortoise ORM registration
|
|
- Add comment: "Routes will be added in Phase 2"
|
|
|
|
Use imports matching existing patterns: `from tortoise import fields`, `from tortoise.models import Model`.
|
|
</action>
|
|
<verify>
|
|
- `cat blueprints/email/crypto_service.py` shows EncryptedTextField class with to_db_value/to_python_value methods
|
|
- `cat blueprints/email/models.py` shows three model classes with correct field definitions
|
|
- `python -c "from blueprints.email.models import EmailAccount, EmailSyncStatus, Email; print('Models import OK')"` succeeds
|
|
- `grep -r "EncryptedTextField" blueprints/email/models.py` shows import and usage in EmailAccount.imap_password
|
|
</verify>
|
|
<done>Three model files exist with EmailAccount having encrypted password field, all models follow Tortoise ORM conventions, imports resolve without errors</done>
|
|
</task>
|
|
|
|
<task type="auto">
|
|
<name>Task 2: Add FERNET_KEY to environment configuration and generate migration</name>
|
|
<files>
|
|
.env.example
|
|
migrations/models/XX_YYYYMMDDHHMMSS_add_email_tables.py
|
|
</files>
|
|
<action>
|
|
**1. Update .env.example:**
|
|
- Add section header: `# Email Integration`
|
|
- Add FERNET_KEY with generation instructions:
|
|
```
|
|
# Email Encryption Key (32-byte URL-safe base64)
|
|
# Generate with: python -c "from cryptography.fernet import Fernet; print(Fernet.generate_key().decode())"
|
|
FERNET_KEY=your-fernet-key-here
|
|
```
|
|
|
|
**2. Generate Aerich migration:**
|
|
Run `aerich migrate --name add_email_tables` inside Docker container to create migration for email_accounts, email_sync_status, and emails tables.
|
|
|
|
The migration will be auto-generated based on the Tortoise ORM models defined in Task 1.
|
|
|
|
If Docker environment not running, use: `docker compose -f docker-compose.dev.yml exec raggr aerich migrate --name add_email_tables`
|
|
|
|
Verify migration file created in migrations/models/ with timestamp prefix.
|
|
</action>
|
|
<verify>
|
|
- `grep FERNET_KEY .env.example` shows encryption key configuration
|
|
- `ls migrations/models/*_add_email_tables.py` shows migration file exists
|
|
- `cat migrations/models/*_add_email_tables.py` shows CREATE TABLE statements for email_accounts, email_sync_status, emails
|
|
</verify>
|
|
<done>FERNET_KEY documented in .env.example with generation command, migration file exists with email table definitions</done>
|
|
</task>
|
|
|
|
</tasks>
|
|
|
|
<verification>
|
|
After task completion:
|
|
1. Run `python -c "from blueprints.email.crypto_service import validate_fernet_key; import os; os.environ['FERNET_KEY']='test'; validate_fernet_key()"` - should raise ValueError for invalid key
|
|
2. Run `python -c "from cryptography.fernet import Fernet; import os; os.environ['FERNET_KEY']=Fernet.generate_key().decode(); from blueprints.email.crypto_service import validate_fernet_key; validate_fernet_key(); print('✓ Encryption validated')"` - should succeed
|
|
3. Check `aerich history` shows new migration in list
|
|
4. Run `aerich upgrade` to apply migration (creates tables in database)
|
|
5. Verify tables exist: `docker compose -f docker-compose.dev.yml exec postgres psql -U raggr -d raggr -c "\dt email*"` - should list three tables
|
|
</verification>
|
|
|
|
<success_criteria>
|
|
- EmailAccount model has encrypted imap_password field that uses EncryptedTextField
|
|
- EmailSyncStatus model tracks last sync state with unique foreign key to EmailAccount
|
|
- Email model stores message metadata with 30-day expiration logic in save()
|
|
- EncryptedTextField transparently encrypts/decrypts using Fernet
|
|
- validate_fernet_key() function can detect invalid or missing keys
|
|
- Database migration exists and can create three email tables
|
|
- .env.example documents FERNET_KEY with generation command
|
|
- All models follow existing codebase conventions (snake_case, async patterns, field types)
|
|
</success_criteria>
|
|
|
|
<output>
|
|
After completion, create `.planning/phases/01-foundation/01-01-SUMMARY.md`
|
|
</output>
|