ADR-002: Database

Status: Accepted Date: 2026-02-07

Context

The platform needs to store relational data (projects, members, roles), hierarchical data (timelines > sections > items), graph-like data (character/faction relationships), flexible metadata (custom character fields), and support full-text search.

Decision

PostgreSQL with EF Core as the ORM.

Rationale

• Handles relational data natively (projects, members, permissions)
• JSONB columns for flexible metadata (character custom fields like clan, generation, class)
• Recursive CTEs for hierarchical data (nested factions, sub-items)
• Self-joins handle relationship graph traversal at the expected scale (hundreds to low thousands of nodes per project)
• Built-in full-text search (tsvector/tsquery) sufficient for MVP
• Mature EF Core provider (Npgsql)
• Row-level security available as a future option if needed

Alternatives Considered

• MongoDB - Good for flexible document schemas but weaker for relational queries and the permission model. The relational aspects of the data model outweigh the document flexibility needs
• Neo4j - Excellent for graph traversal but overkill at the expected scale. Adds operational complexity (separate database to manage) without meaningful performance benefit for hundreds of nodes
• Supabase - PostgreSQL + auth + real-time + storage bundled. Attractive but introduces platform coupling. The team preferred keeping infrastructure decisions independent
• Firebase - Real-time built-in but limited query capabilities. Poor fit for the complex permission model

Consequences

• JSONB used for flexible fields (character metadata, changelog entries)
• Full-text search starts with PostgreSQL built-in; may upgrade to dedicated search engine later (see team discussion items)
• EF Core migrations manage schema changes
• Need PostgreSQL hosting (cloud-managed or self-hosted, pending team decision)