Database and Migrations

Database and Migrations

Schema — 12 tables across 4 layers (UUID PKs throughout)

• Identity: tenants, sites, users, user_site_access
• Pipeline: transactions (the "batch"), files, file_step_history
• Support: error_catalog (global), activity_log
• Extraction: document_types (global), invoice_line_items, item_categories (global)

Notable entity → table mappings

• FileRecord → files (avoids clashing with System.IO.File)
• Transaction → transactions (exposed in the API as "batch" / /batches)
• InvoiceLineItem → invoice_line_items, ItemCategory → item_categories

Migration-based — no hand-written SQL

The schema is managed entirely via EF Core migrations. The initial migration (InitialCreate) creates all 12 tables, indexes, and FKs. The migrations are effectively your "database script".

🔎 Why no schema.sql? Repos that ship raw SQL usually don't use an ORM. To change the schema here, create a new migration — never hand-edit the DB:

dotnet ef migrations add <Name> --project DocAnalytics.Data --startup-project DocAnalytics.Api

Connection string fields

Host=localhost;Port=5432;Database=docanalytics;Username=postgres;Password=YOUR_LOCAL_PW

Field	Meaning	Typical local value
Host	Where PostgreSQL runs	localhost
Port	Listening port	5432
Database	DB name	docanalytics
Username	A PostgreSQL login role	postgres
Password	That role's password	whatever you set during install

Seed data

Loads automatically on first run (DbSeeder.SeedAsync, which also applies pending migrations). Creates 2 tenants (Acme + Globex) with sites, users, batches, files, step history, line items, categories, and an activity-log entry — enough to exercise every feature and prove tenant + site isolation. Seed users & credentials are listed in API Reference.

Resetting the DB (seeder is idempotent)

The seeder short-circuits with if (await db.Tenants.AnyAsync()) return;. So if you change seed data, you must reset first:

dotnet ef database drop   --project DocAnalytics.Data --startup-project DocAnalytics.Api --force
dotnet ef database update --project DocAnalytics.Data --startup-project DocAnalytics.Api
dotnet run --project DocAnalytics.Api   # re-seeds on startup

⚠️ After a reset, log in again for a fresh token — an old token points to GUIDs that no longer exist, so calls return empty. See FAQ and Troubleshooting.