Create plan for PostgreSQL support in core service

[geoffjay]

Context

The core service (#211) initially uses SQLite for simplicity, but future deployments may require PostgreSQL for better concurrency, scalability, and multi-instance support.

Summary

A plan needs to be created for adding PostgreSQL as an alternative database backend for the core service. This should be a separate effort tracked under its own plan.

Considerations

• Abstract the storage layer behind a trait to support multiple backends
• Use sqlx's compile-time query checking with both SQLite and PostgreSQL
• Consider using feature flags (sqlite default, postgres optional)
• Migration files need to be maintained for both dialects or use a compatible subset
• Connection pooling configuration differences between SQLite and PostgreSQL
• Environment-based database URL selection (DATABASE_URL)
• Impact on deployment complexity (PostgreSQL requires an external service)
• Consider whether other services (orchestrator, notify) should also support PostgreSQL

Notes

This issue is intentionally a plan marker — the actual implementation should be broken down into its own set of issues when the time comes. The core service should be designed with this future migration in mind by using trait-based storage abstractions from the start.

Dependencies

• Blocked by Scaffold the core service crate #212, Add user model and SQLite storage to core service #213, Add organization model and memberships to core service #214, Add authentication endpoints to core service #215 (core service must be functional with SQLite first)

[geoffjay]

✅ Triage Complete

Field	Value
Type	research
Complexity	medium
Milestone	v0.3.3 — Core Service & Multi-tenancy
Labels	plan, research, architecture, needs-decision, complexity:medium

Notes:

• This is an intentional plan marker — no implementation code is expected. The deliverable is a design document or ADR and a set of sub-issues for future work.
• Type is research: the PostgreSQL migration approach (trait abstractions, feature flags, dialect compatibility, connection pooling, multi-service scope) requires investigation before implementation can be broken down.
• architecture applied: the body explicitly asks "Consider whether other services (orchestrator, notify) should also support PostgreSQL" — this is a cross-service design concern that affects the entire persistence layer.
• needs-decision applied: several unresolved architectural choices must be made before any sub-issues can be created:
  • Trait-based storage abstraction vs. compile-time feature flags
  • Single migration set (compatible SQL subset) vs. per-dialect migration files
  • Which services adopt PostgreSQL support alongside core (orchestrator? notify? all?)
  • Whether DATABASE_URL env var becomes a first-class config concern across all services
• Complexity is medium for the planning work itself (design doc + sub-issue creation). The eventual implementation will be complexity:large (cross-crate, feature flags, dual-dialect migrations, connection pool config).
• Milestone v0.3.3 is correct — this planning must happen alongside the core service SQLite implementation so trait abstractions are designed in from the start rather than retrofitted.
• No duplicates found.