Skip to content
This repository was archived by the owner on Jul 6, 2026. It is now read-only.

database-schema.md

Latest commit

 

History

History
358 lines (306 loc) · 15.7 KB

database-schema.md

File metadata and controls

358 lines (306 loc) · 15.7 KB
Raw

🗄 Loopless — Database Schema

PostgreSQL 16 + pgvector extension. Migrations are EF Core code-first (see backend/src/Loopless.Infrastructure/Persistence/Migrations/).

FS-3 / M5.3 deliverable. Crow's-foot ERD rendered natively by Mermaid v10+. Schema is normalized to 3NF (justification below). Migration strategy at the bottom.

Table summary (19 entities)

Table Purpose Notable columns
users Authentication + display identity for all roles email UK, role, keycloak_id UK, is_deleted
freelancer_profiles 1:1 extension for freelancer-role users bio, hourly_rate, availability, proficiencies (JSONB), github_username
enterprise_profiles 1:1 extension for enterprise-role users company_name, industry, typical_needs (JSONB)
projects Freelancer-owned workspace (commits, files, links, summaries) freelancer_id FK, status, standup_enabled, required_skills (text[])
project_invitations Either party invites the other onto a project project_id, enterprise_id, freelancer_id (nullable), initiated_by, status
project_files Uploaded artifacts stored in S3/MinIO project_id FK, file_name, file_url, file_size
project_links External URLs attached to a project project_id FK, url, title, description
project_tasks Kanban-style task tracker per project project_id FK, title, status, assignee_id
project_task_audit_logs Audit trail per task lifecycle change task_id FK, action, actor_id, from_status, to_status
github_commits Cached commits pulled by Hangfire from the linked GitHub repo project_id FK, sha UK, message, author_name, committed_at
project_summaries Versioned AI-generated project summaries (RAG output) project_id FK, content, model_used, generated_at
standups Daily async check-ins (3 questions + blocker sentiment) project_id FK, freelancer_id FK, blocker_sentiment (async — may be null for recently submitted standups)
conversations Direct-message thread between two users participant_one FK, participant_two FK, last_message_at
messages Individual message in a conversation conversation_id FK, sender_id FK, content, file_url, email_notification_sent_at
notifications Platform-pushed in-app notifications user_id FK, title, body, action_url, is_read
content_flags User-reported content for moderation queue reporter_id FK, entity_type, entity_id, reason, status
embeddings vector(1536) for semantic matching (polymorphic) entity_type, entity_id, embedding
audit_trails Generic mutation log for compliance user_id FK, entity_type, entity_id, action, old_values, new_values
skills Catalog (read-only seed) of selectable proficiencies name UK, category

All tables include id UUID PK, created_at, updated_at (nullable on UPDATE), and a soft-delete is_deleted BOOLEAN where soft-delete is meaningful.

Indexes

Table Index Type Reason
users email, keycloak_id btree UNIQUE Login + Keycloak sub lookup
projects freelancer_id, status btree Owner-scoped queries + status filters
project_invitations (project_id, enterprise_id), (project_id, freelancer_id), freelancer_id, status btree Both sides of invitation lookup + queue filter
freelancer_profiles proficiencies GIN JSONB skill containment queries
embeddings "Vector" vector_cosine_ops HNSW (m=16, ef_construction=64) ANN semantic search via <=> operator. HNSW chosen over IVFFlat for better recall-to-latency at scale with no manual index rebuilds. m=16 controls the number of bi-directional links per node (higher = better recall, more memory); ef_construction=64 controls build-time search width (higher = better quality graph, slower build).
standups (project_id, created_at) btree composite Timeline pagination
messages (conversation_id, created_at) btree composite Chat history pagination
notifications (user_id, created_at DESC), (user_id, is_read) btree composite Unread badge + feed
github_commits (project_id, committed_at DESC), sha btree Latest commit feed + dedup on sync
audit_trails (entity_type, entity_id, created_at DESC) btree Per-entity history

Before/after EXPLAIN ANALYZE evidence for the four hottest queries in sql-optimization.md.

Crow's-Foot ERD

Pre-rendered fallback for viewers without Mermaid support: erd-crowfoot.svg (generated via @mermaid-js/mermaid-cli).

Cardinality notation (Mermaid v10+ renders crow's-foot glyphs directly):

Mermaid Reads as
||--|| exactly one ↔ exactly one
||--o| one ↔ zero-or-one (1:1 optional)
||--o{ one ↔ zero-or-many (1:N)
||--|{ one ↔ one-or-many (1:N required)
}o--o{ zero-or-many ↔ zero-or-many (N:M) 
erDiagram
  users {
    uuid id PK
    varchar email UK
    varchar name
    varchar role
    varchar keycloak_id UK
    varchar avatar_url
    boolean is_deleted
    timestamptz created_at
    timestamptz updated_at
  }

  freelancer_profiles {
    uuid user_id PK
    text bio
    decimal hourly_rate
    varchar availability
    jsonb proficiencies
    varchar github_username
    text portfolio_url
    text linkedin_url
    text location
    text title
  }

  enterprise_profiles {
    uuid user_id PK
    varchar company_name
    varchar industry
    jsonb typical_needs
    text description
    text company_size
    text location
    text website
  }

  projects {
    uuid id PK
    uuid freelancer_id FK
    varchar title
    text description
    varchar github_repo_url
    varchar status
    boolean standup_enabled
    boolean standup_reminders_enabled
    text_array required_skills
    boolean is_deleted
    timestamptz created_at
  }

  project_invitations {
    uuid id PK
    uuid project_id FK
    uuid enterprise_id FK
    uuid freelancer_id FK
    varchar initiated_by
    varchar status
    timestamptz invited_at
  }

  project_files {
    uuid id PK
    uuid project_id FK
    varchar file_name
    text file_url
    bigint file_size
    timestamptz uploaded_at
  }

  project_links {
    uuid id PK
    uuid project_id FK
    text url
    varchar title
    text description
  }

  project_tasks {
    uuid id PK
    uuid project_id FK
    uuid assignee_id FK
    varchar title
    text description
    varchar status
    timestamptz created_at
  }

  project_task_audit_logs {
    uuid id PK
    uuid task_id FK
    uuid actor_id FK
    varchar action
    varchar from_status
    varchar to_status
    timestamptz created_at
  }

  github_commits {
    uuid id PK
    uuid project_id FK
    varchar sha UK
    text message
    varchar author_name
    timestamptz committed_at
    timestamptz synced_at
  }

  project_summaries {
    uuid id PK
    uuid project_id FK
    text content
    varchar model_used
    timestamptz generated_at
  }

  standups {
    uuid id PK
    uuid project_id FK
    uuid freelancer_id FK
    text did_yesterday
    text will_today
    text blockers
    varchar blocker_sentiment
    timestamptz created_at
  }

  conversations {
    uuid id PK
    uuid participant_one FK
    uuid participant_two FK
    timestamptz created_at
    timestamptz last_message_at
  }

  messages {
    uuid id PK
    uuid conversation_id FK
    uuid sender_id FK
    text content
    text file_url
    timestamptz read_at
    timestamptz email_notification_sent_at
    timestamptz created_at
  }

  notifications {
    uuid id PK
    uuid user_id FK
    varchar title
    text body
    text action_url
    boolean is_read
    timestamptz created_at
  }

  content_flags {
    uuid id PK
    uuid reporter_id FK
    varchar entity_type
    uuid entity_id
    text reason
    varchar status
    timestamptz created_at
  }

  embeddings {
    uuid id PK
    varchar entity_type
    uuid entity_id
    vector embedding
    timestamptz created_at
  }

  audit_trails {
    uuid id PK
    uuid user_id FK
    varchar entity_type
    uuid entity_id
    varchar action
    jsonb old_values
    jsonb new_values
    timestamptz created_at
  }

  skills {
    uuid id PK
    varchar name UK
    varchar category
  }

  users ||--o| freelancer_profiles : "extends_as_freelancer"
  users ||--o| enterprise_profiles : "extends_as_enterprise"
  users ||--o{ projects : "owns"
  users ||--o{ standups : "submits"
  users ||--o{ notifications : "receives"
  users ||--o{ audit_trails : "actor_of"
  users ||--o{ messages : "sends"
  users ||--o{ content_flags : "reports"
  users ||--o{ project_invitations : "invited_as_enterprise"
  users |o--o{ project_invitations : "invited_as_freelancer"
  users ||--o{ project_tasks : "assigned"
  users ||--o{ project_task_audit_logs : "performed_by"
  users ||--o{ conversations : "participant_one"
  users ||--o{ conversations : "participant_two"

  projects ||--o{ project_invitations : "has"
  projects ||--o{ project_files : "has"
  projects ||--o{ project_links : "has"
  projects ||--o{ project_tasks : "has"
  projects ||--o{ github_commits : "syncs"
  projects ||--o{ project_summaries : "versioned_summary"
  projects ||--o{ standups : "tracks"

  project_tasks ||--o{ project_task_audit_logs : "audited_by"
  conversations ||--|{ messages : "contains"
Loading

Polymorphic relationships (intentionally outside the diagram to keep crow's-foot semantics clean):

  • embeddings.entity_type ∈ {"user", "project", "skill"}, joined to entity_id of the matching table.
  • audit_trails.entity_type ∈ any table name, joined to entity_id.
  • content_flags.entity_type ∈ {"project", "message", "standup", "user"}.

3NF Justification

A relation is in 3NF when every non-key attribute is:

  1. Atomic (1NF)
  2. Fully functionally dependent on the entire primary key (2NF)
  3. Non-transitively dependent on the primary key (3NF)
Table 1NF 2NF 3NF Notes
users ✅ ✅ (single-column PK) ✅ keycloak_id is alternate key, not a derived column
freelancer_profiles ✅ — JSONB proficiencies stores structured documents treated as a single value at the relational layer (queried via GIN) ✅ (PK = user_id) ✅ Separated from users to avoid sparse columns for enterprise rows
enterprise_profiles ✅ ✅ ✅ Same rationale as above
projects ✅ ✅ ✅ required_skills is text[] (relational-safe array, no nested objects)
project_invitations ✅ ✅ ✅ No derived columns; status enum changes are append-only via audit log
project_files ✅ ✅ ✅ file_url is canonical; signed URLs are computed per-request
project_links ✅ ✅ ✅ —
project_tasks ✅ ✅ ✅ Status enum is intentionally not denormalized to project
project_task_audit_logs ✅ ✅ ✅ Append-only
github_commits ✅ ✅ ✅ Cache table; sha unique per repo lifetime
project_summaries ✅ ✅ ✅ Versioned; older rows are never updated
standups ✅ ✅ ✅ blocker_sentiment populated asynchronously by AnalyzeBlockerSentimentConsumer after standup write; null until the consumer processes the standups.submitted event
conversations ✅ ✅ ✅ last_message_at is denormalized for conversation-list query performance — documented exception, kept consistent via a domain event on Message insert
messages ✅ ✅ ✅ email_notification_sent_at is denormalized to avoid scanning a separate dispatch log — same documented-exception pattern
notifications ✅ ✅ ✅ —
content_flags ✅ ✅ ✅ Polymorphic FK does not violate 3NF — referential integrity is enforced in the Application layer
embeddings ✅ ✅ ✅ Polymorphic on (entity_type, entity_id)
audit_trails ✅ ✅ ✅ JSONB diffs are atomic at the relational layer
skills ✅ ✅ ✅ —

Two intentional 3NF exceptions, both documented:

  1. conversations.last_message_at is denormalized to avoid MAX(created_at) GROUP BY conversation_id on every conversation-list page. Maintained by Message-insert event handler.
  2. messages.email_notification_sent_at is denormalized so UnreadMessageEmailJob can WHERE email_notification_sent_at IS NULL rather than joining a dispatch log. Append-only update keeps consistency simple.

Both are explicit trade-offs — flagged here so future maintainers do not "re-normalize" them and regress the hot-path queries.

Migration Strategy

Aspect Approach
Tool EF Core 10 code-first migrations (backend/src/Loopless.Infrastructure/Persistence/Migrations/)
Generation dotnet ef migrations add <Name> --project src/Loopless.Infrastructure --startup-project src/Loopless.Api
Apply (dev) Container entrypoint runs db.Database.MigrateAsync() on startup; Testcontainers does the same per integration-test class
Apply (prod) One-shot Kubernetes init container runs dotnet Loopless.Api migrate before the API deployment scales up; rolling pods never run migrations concurrently
Immutability rule Applied migrations are immutable — never edit a migration that has shipped to staging or main. New change = new migration. (See .cursorrules for rationale.)
Naming Verb-noun + module: AddProjectsAndInvitations, AddBlockerSentimentAndEmailToggle, etc. Timestamp prefix is auto-generated, used as ordering key.
Down() symmetry Every Up() ships a working Down() that fully reverses it. CI exercises this via dotnet ef migrations script --idempotent round-trip.
Data fixes Schema changes ship as migrations; one-off data fixes ship as guarded SQL blocks in migrations (e.g. IF EXISTS / WHERE NOT EXISTS) — never as ad-hoc psql commands against prod.
Backups devops/scripts/server/backup-postgres.sh runs daily at 02:15 UTC via cron, 7-day local retention, restorable in one `gunzip
Rollback Restore from latest backup + replay forward; or dotnet ef database update <PreviousMigrationName> if no data drift since the bad migration shipped. Decision tree in docs/operations-runbook.md.
PR review Every migration PR requires: (1) schema diff visible in the PR description, (2) before/after EXPLAIN ANALYZE if it touches a hot index, (3) Down() reviewed alongside Up().

Related Documents