QualityDocD Database Engines: Setup and Migrations

QualityDocD uses three databases, each serving a distinct purpose. SQL Server 2022 is the primary relational store for documents, users, approval workflows, and audit logs. PostgreSQL 16 holds the structured audit trail and is also used by the Node.js API for companies, users, and document versioning via Drizzle ORM. MongoDB 7 stores rich document metadata and powers full-text search. All three engines are defined in docker-compose.infra.yml, start with health checks, and persist data in named Docker volumes. The application layer handles schema creation automatically on first startup — no manual SQL is required for a standard deployment.

Databases

SQL Server 2022
PostgreSQL 16
MongoDB 7

Container Details

Property	Value
Container name	`qualitydoc_sqlserver`
Image	`mcr.microsoft.com/mssql/server:2022-latest`
Host port	`1433`
Edition (`MSSQL_PID`)	`Express` (default)
Named volume	`sqlserver_data` → `/var/opt/mssql`
Performance SQL dir	`/docker-entrypoint-sql/` (mounted from `./db/sql`)

Automatic Migration

The .NET application (qualitydoc_app) calls db.Database.MigrateAsync() in Program.cs on every startup. EF Core checks the __EFMigrationsHistory table and applies any pending migrations automatically — no manual steps needed on first run.The initial migration is located at:

QualityDocD/Migrations/SqlServer/20260606202353_InitialSqlServer.cs

It creates the following tables in the QualityDocDB database:

Users — accounts with roles (VIEWER, COMMENTER, CONTRIBUTOR, OPERATOR, COMPANY_ADMIN, SUPER_ADMIN)
Documents — document records with status, versioning, file metadata, and category
DocumentApprovals — approval workflow entries linking documents and reviewers
AuditLogs — change history for document lifecycle events

The seed data script (db/sql/01_sqlserver_speed.sql) is mounted read-only into the container at /docker-entrypoint-sql/ for manual use. It inserts test users, ten sample documents across all statuses, approval records, and audit log entries.

Manual Migration Command

If you need to apply migrations outside of Docker (e.g., targeting a remote SQL Server), run:

dotnet ef database update --context AppDbContext

Health Check

healthcheck:
  test: ["CMD-SHELL", "/opt/mssql-tools18/bin/sqlcmd -S localhost -U sa -P '${SQLSERVER_SA_PASSWORD}' -Q 'SELECT 1' -No -C"]
  interval: 15s
  timeout:  10s
  retries:  8
  start_period: 30s

SQL Server takes longer to initialize than the other engines. The 30-second start_period and 8 retries allow up to ~150 seconds total before Docker marks the container unhealthy.

Verify Connectivity

docker exec qualitydoc_sqlserver \
  /opt/mssql-tools18/bin/sqlcmd \
  -S localhost -U sa -P 'QualityDoc2026!' \
  -Q 'SELECT name FROM sys.databases' -No -C

Backup and Restore

# Using sqlcmd inside the container
docker exec qualitydoc_sqlserver \
  /opt/mssql-tools18/bin/sqlcmd \
  -S localhost -U sa -P 'QualityDoc2026!' \
  -Q "BACKUP DATABASE QualityDocDB TO DISK='/var/opt/mssql/QualityDocDB.bak' WITH FORMAT"

You can also connect to localhost,1433 with SQL Server Management Studio (SSMS) or Azure Data Studio using sa and the password from your .env file.

Roles Migration

db/sql/03_roles_update.sql expands the Users.Role constraint from 3 legacy values (Admin, Manager, Reviewer) to the current 6-role model. Run it manually after deploying an environment that was initialized with an older schema:

-- db/sql/03_roles_update.sql
USE QualityDocDB;
GO

ALTER TABLE Users DROP CONSTRAINT IF EXISTS CK_Users_Role;
GO

ALTER TABLE Users ADD CONSTRAINT CK_Users_Role
  CHECK (Role IN ('VIEWER','COMMENTER','CONTRIBUTOR','OPERATOR','COMPANY_ADMIN','SUPER_ADMIN'));
GO

UPDATE Users SET Role = 'SUPER_ADMIN'   WHERE Role = 'Admin';
UPDATE Users SET Role = 'COMPANY_ADMIN' WHERE Role = 'Manager';
UPDATE Users SET Role = 'OPERATOR'      WHERE Role = 'Reviewer';
UPDATE Users SET Role = 'CONTRIBUTOR'   WHERE Role = 'Editor';
UPDATE Users SET Role = 'VIEWER'        WHERE Role = 'Viewer';
GO

docker exec -i qualitydoc_sqlserver \
  /opt/mssql-tools18/bin/sqlcmd \
  -S localhost -U sa -P 'QualityDoc2026!' \
  -i /docker-entrypoint-sql/03_roles_update.sql -No -C

Container Details

Property	Value
Container name	`qualitydoc_postgres`
Image	`postgres:16-alpine`
Host port	`5432`
Database	`qualitydoc_audit` (from `PG_DB`)
Username	`qualitydoc` (from `PG_USER`)
Named volume	`postgres_data` → `/var/lib/postgresql/data`
Performance SQL dir	`/docker-entrypoint-sql/` (mounted from `./db/sql`)

Two Clients, One Database

PostgreSQL is used by two separate services:

.NET AuditDbContext — manages the audit_entries table via EF Core. Migrations are in QualityDocD/Migrations/AuditDb/20260606165215_InitialAuditPostgres.cs.
Node.js API (Drizzle ORM) — manages companies, users, documents, document_versions, and search_index tables. Schema definitions live in node-api/schema/.

EF Core Migration (Audit Schema)

The .NET app also runs MigrateAsync() for the AuditDbContext, creating the audit_entries table in PostgreSQL on first startup alongside the SQL Server migration.To run only the PostgreSQL audit migration manually:

dotnet ef database update --context AuditDbContext

Node.js Schema (Drizzle ORM)

The Node.js API uses Drizzle ORM to manage its tables. Push the schema to the running database:

# Run inside the node-api container
docker exec qualitydoc_node_api npm run db:push

Schema files:

node-api/schema/companies.ts
node-api/schema/users.ts
node-api/schema/documents.ts
node-api/schema/search-index.ts
node-api/schema/index.ts

Health Check

healthcheck:
  test: ["CMD-SHELL", "pg_isready -U ${PG_USER:-qualitydoc} -d ${PG_DB:-qualitydoc_audit}"]
  interval: 10s
  timeout:  5s
  retries:  5
  start_period: 10s

PostgreSQL initializes quickly; it is typically healthy within 10–15 seconds.

Verify Connectivity

docker exec qualitydoc_postgres \
  psql -U qualitydoc -d qualitydoc_audit -c '\dt'

Backup and Restore

docker exec qualitydoc_postgres \
  pg_dump -U qualitydoc qualitydoc_audit \
  > qualitydoc_audit_backup.sql

Seed Data

db/sql/02_postgressql_speed.sql is mounted in the container at /docker-entrypoint-sql/. It populates audit_entries with 31 sample rows covering document creation, status transitions, approvals, and download events across all test users. Run it manually to load the sample data:

docker exec -i qualitydoc_postgres \
  psql -U qualitydoc -d qualitydoc_audit \
  -f /docker-entrypoint-sql/02_postgressql_speed.sql

Container Details

Property	Value
Container name	`qualitydoc_mongodb`
Image	`mongo:7`
Host port	`27017`
Database	`qualitydoc_meta` (from `MONGO_DB`)
Admin username	`mongoadmin` (from `MONGO_USER`)
Named volume	`mongodb_data` → `/data/db`
Init script	`db/mongodb/init.js` → `/docker-entrypoint-initdb.d/01_init.js`

Automatic Initialization

db/mongodb/init.js runs automatically the first time the container starts (when the data volume is empty). It:

Switches to the qualitydoc_meta database
Creates a qualitydoc user with readWrite permissions scoped to qualitydoc_meta
Creates the document_metadata collection
Creates the following indexes:

Index name	Field(s)	Type	Notes
`idx_documentId`	`documentId`	Unique	One metadata doc per document ID
`idx_code`	`code`	Unique	Document code lookup
`idx_category`	`category`	Standard	Filter by category
`idx_tags`	`tags`	Standard	Tag-based filtering
`idx_status`	`status`	Standard	Status filtering
`idx_fulltext`	`title`, `description`, `tags`, `standard`	Text (weighted)	Powers `$text` search queries

The full-text index uses field weights to rank results: title (10) > tags (5) > standard (3) > description (1).

// Excerpt from db/mongodb/init.js
db.document_metadata.createIndex(
  { title: 'text', description: 'text', tags: 'text', standard: 'text' },
  {
    name:    'idx_fulltext',
    weights: { title: 10, tags: 5, standard: 3, description: 1 }
  }
);

The init script only runs on first container start. If the mongodb_data volume already contains data, the script is skipped. To re-run initialization, remove the volume first with docker compose -f docker-compose.infra.yml down -v.

document_metadata Collection

The document_metadata collection is populated by the Node.js API and Search Service when documents are approved. Each document generates one metadata document containing fields such as documentId, code, title, description, category, standard, tags, status, and contentText.The contentText field alongside title enables $text search queries issued by the Search Service when the .NET app calls its /search endpoint.

Health Check

healthcheck:
  test: ["CMD", "mongosh", "--quiet", "--eval", "db.adminCommand('ping').ok"]
  interval: 15s
  timeout:  10s
  retries:  5
  start_period: 20s

Verify Connectivity

docker exec qualitydoc_mongodb \
  mongosh --quiet \
  -u mongoadmin -p 'QualityDoc_Mongo_2026!' \
  --authenticationDatabase admin \
  --eval "db.getSiblingDB('qualitydoc_meta').document_metadata.countDocuments()"

Backup and Restore

docker exec qualitydoc_mongodb \
  mongodump \
  -u mongoadmin -p 'QualityDoc_Mongo_2026!' \
  --authenticationDatabase admin \
  --db qualitydoc_meta \
  --out /tmp/mongo_backup

docker cp qualitydoc_mongodb:/tmp/mongo_backup ./mongo_backup

Migration Summary

Database	Migration tool	Triggered by	Migration location
SQL Server (`QualityDocDB`)	EF Core (`AppDbContext`)	.NET app startup (`MigrateAsync`)	`QualityDocD/Migrations/SqlServer/`
PostgreSQL (`qualitydoc_audit`)	EF Core (`AuditDbContext`)	.NET app startup (`MigrateAsync`)	`QualityDocD/Migrations/AuditDb/`
PostgreSQL (Node.js tables)	Drizzle ORM	Manual: `npm run db:push`	`node-api/schema/`
MongoDB (`qualitydoc_meta`)	Init script	First container start	`db/mongodb/init.js`

Resetting All Data

The command below permanently deletes all three database volumes (sqlserver_data, postgres_data, mongodb_data). Every table, collection, and stored file reference will be erased. There is no undo — make a backup first if you need to preserve any data.

# Stop infra and delete all named volumes
docker compose -f docker-compose.infra.yml down -v

# Optionally also stop the app layer first
docker compose -f docker-compose.apps.yml down
docker compose -f docker-compose.portal.yml down

# Then restart everything from scratch
docker compose -f docker-compose.infra.yml up -d
docker compose -f docker-compose.apps.yml up -d
docker compose -f docker-compose.portal.yml up -d

On next startup, EF Core migrations re-create all SQL Server and PostgreSQL schemas, and the MongoDB init script re-creates the document_metadata collection with all indexes. The .NET app also re-seeds the default test users into SQL Server. Using setup.ps1 on Windows, the equivalent is:

.\setup.ps1 -Mode All -ResetData

Getting Started

Core Concepts

User Guide

Deployment

QualityDocD Database Engines: Setup and Migrations

Databases

Container Details

Automatic Migration

Manual Migration Command

Health Check

Verify Connectivity

Backup and Restore

Roles Migration

Container Details

Two Clients, One Database

EF Core Migration (Audit Schema)

Node.js Schema (Drizzle ORM)

Health Check

Verify Connectivity

Backup and Restore

Seed Data

Container Details

Automatic Initialization

document_metadata Collection

Health Check

Verify Connectivity

Backup and Restore

Migration Summary

Resetting All Data

Build docs developers (and LLMs) love

Getting Started

Core Concepts

User Guide

Deployment

Documentation Index

​Databases

​Container Details

​Automatic Migration

​Manual Migration Command

​Health Check

​Verify Connectivity

​Backup and Restore

​Roles Migration

​Container Details

​Two Clients, One Database

​EF Core Migration (Audit Schema)

​Node.js Schema (Drizzle ORM)

​Health Check

​Verify Connectivity

​Backup and Restore

​Seed Data

​Container Details

​Automatic Initialization

​document_metadata Collection

​Health Check

​Verify Connectivity

​Backup and Restore

​Migration Summary

​Resetting All Data

Build docs developers (and LLMs) love

Databases

Container Details

Automatic Migration

Manual Migration Command

Health Check

Verify Connectivity

Backup and Restore

Roles Migration

Container Details

Two Clients, One Database

EF Core Migration (Audit Schema)

Node.js Schema (Drizzle ORM)

Health Check

Verify Connectivity

Backup and Restore

Seed Data

Container Details

Automatic Initialization

document_metadata Collection

Health Check

Verify Connectivity

Backup and Restore

Migration Summary

Resetting All Data