SilverlogDatabase

Postgres database for Silverlog. This repo owns the schema (database-first) — the .NET backend connects to it and scaffolds its models from these tables; it does not generate or own the schema itself.

Prerequisites

• Docker + Docker Compose
• dbmate (brew install dbmate) — the migration tool used to create/update tables
• psql version 17.6+ recommended (optional, for manually inspecting the database). dbmate uses your locally installed pg_dump/psql to regenerate db/schema.sql; an older client can produce or fail to load a dump containing \restrict/\unrestrict meta-commands that only 17.6+ understands.

Local setup

1. Copy .env.example to .env and fill in real values (ask a teammate for shared local dev credentials, or just pick your own — this file is git-ignored and never committed).
2. Start Postgres:

   docker compose up -d

3. Apply all migrations (creates the tables):

   dbmate up

4. (Optional) Load sample/test data. psql doesn't read .env itself, so export the variables into your shell first:

   set -a && source .env && set +a
   psql "$DATABASE_URL" -f db/seed.sql

   Safe to re-run any time — it truncates and reloads. All seeded rows use obviously-fake values (e.g. @example.test emails) so they're never mistaken for real data.

See docs/docker-guide.md for a fuller explanation of the Docker/Postgres setup.

Connecting from the .NET backend

The database is just standard Postgres reachable over TCP — nothing .NET-specific is required on this side. From the backend repo:

1. Add the Npgsql EF Core provider:

   dotnet add package Npgsql.EntityFrameworkCore.PostgreSQL
   dotnet add package Microsoft.EntityFrameworkCore.Design

2. Connection string shape (values come from this repo's .env — get the actual password from whoever set up your local .env, don't hardcode it):

   Host=localhost;Port=5432;Database=silverlog;Username=silverlog;Password=<your local password>
   

3. Scaffold C# models from the existing tables (database-first):

   dotnet ef dbcontext scaffold "Host=localhost;Port=5432;Database=silverlog;Username=silverlog;Password=<your local password>" Npgsql.EntityFrameworkCore.PostgreSQL -o Models --context SilverlogDbContext

   Re-run this command whenever the schema changes here, to regenerate the models.

Important: the role enum

users.role is a native Postgres ENUM type (user_role, values admin/student), not a plain text column. Npgsql needs this mapped explicitly or scaffolding/queries against it will fail. Before scaffolding, register the enum mapping:

var dataSourceBuilder = new NpgsqlDataSourceBuilder(connectionString);
dataSourceBuilder.MapEnum<UserRole>("user_role");
var dataSource = dataSourceBuilder.Build();

where UserRole is a C# enum with members matching the Postgres labels (Admin, Student) — Npgsql matches them case-insensitively by default.

Schema

This section is a hand-maintained summary for quick reference. The definitive, always-current schema is db/schema.sql (auto-regenerated by dbmate on every migration) — if this section and that file ever disagree, trust db/schema.sql.

users
  user_id  (PK, bigint identity)
  email    (text, not null; unique case-insensitively via index on lower(email))
  name     (text, not null)
  role     (user_role enum: 'admin' | 'student', not null, default 'student')

entries
  entry_id    (PK, bigint identity)
  user_id     (FK -> users.user_id, ON DELETE CASCADE, indexed)
  entry_text  (text, not null)
  entry_date  (timestamptz, not null, default now())

codes
  code_id          (PK, bigint identity)
  wem_code         (text, unique, not null)
  wem_description  (text, not null)

activities
  activity_id  (PK, bigint identity)
  entry_id     (FK -> entries.entry_id, ON DELETE CASCADE)
  code_id      (FK -> codes.code_id, ON DELETE RESTRICT, indexed)
  unique (entry_id, code_id) -- also serves as the index for entry_id lookups

Relationships:

users ──(cascade)──< entries ──(cascade)──< activities >──(restrict)── codes

• Deleting a user deletes all their entries (and, transitively, those entries' activities).
• Deleting a code is blocked while any activity still references it.

Schema changes

This repo is the source of truth for the schema. To change it:

dbmate new <descriptive_migration_name>
# edit the generated file in db/migrations/, then:
dbmate up

Never hand-edit db/schema.sql — it's auto-regenerated by dbmate on every migration. Commit both the new migration file and the updated schema.sql.