Documentation Index
Fetch the complete documentation index at: https://mintlify.com/juescoryisus/QualityDocD/llms.txt
Use this file to discover all available pages before exploring further.
QualityDocD deliberately stores data in three different database engines, a pattern known as polyglot persistence. This is not complexity for its own sake — each engine was chosen because it is genuinely better suited to its workload than any single-database alternative. SQL Server 2022 is the authoritative system of record for all transactional document data. PostgreSQL 16 stores structured audit trails and serves the multi-tenant Node.js API, where Drizzle ORM manages the schema. MongoDB 7 holds denormalized document metadata with a full-text index, enabling sub-millisecond search queries that would be expensive to run against a normalized relational schema. When a document changes state, each of these three stores is updated in sequence, with SQL Server always written first.
SQL Server 2022 — Primary Application Store
SQL Server is the single source of truth for the .NET MVC application. All business logic in DocumentService.cs reads and writes SQL Server via Entity Framework Core using the AppDbContext. Transactions, referential integrity, and cascading deletes are enforced at the database level. Schema changes are tracked through EF Core migrations stored in QualityDocD/Migrations/SqlServer/.
Documents Table
Users Table
DocumentApprovals Table
AuditLogs Table
The Documents table is the heart of the system. Every field in the Document class maps to a column here, with Status stored as a string via EF Core’s HasConversion<string>() configuration. Document codes follow the QD-XXXX format generated at creation time.| Column | Type | Notes |
|---|
Id | int | Primary key, auto-increment |
Code | string | Unique; format QD-0001, QD-0042, etc. |
Title | string | Required |
Description | string | Required |
Category | string | Used for filtering and compliance grouping |
Standard | string | Quality standard reference (e.g. ISO 9001) |
Version | int | Increments when a file attachment is replaced; starts at 1 |
Status | string | Stored as string enum: Draft, UnderReview, PendingChanges, UnderSecondReview, Approved, Rejected, Obsolete |
StoredFileName | string | Guid-based filename on disk under wwwroot/uploads/ |
OriginalFileName | string | Original filename as uploaded by the user |
FileExtension | string | Normalized lowercase extension (e.g. .pdf) |
ContentType | string | MIME type |
FileSizeBytes | long | File size in bytes |
Tags | string | Comma-separated tag string |
IsPublic | bool | Whether the document is visible without authentication |
MongoMetadataId | string? | MongoDB ObjectId of the mirrored DocumentMeta document |
CreatedAt | DateTime | UTC timestamp set on creation |
UpdatedAt | DateTime? | UTC timestamp set on every update |
ApprovedAt | DateTime? | Set when Status transitions to Approved |
RejectedAt | DateTime? | Set when Status transitions to Rejected |
ExpiresAt | DateTime? | Optional expiry date for the document |
CreatedByUserId | int (FK) | References Users.Id with DeleteBehavior.Restrict |
User accounts are stored in SQL Server and managed exclusively by the .NET application. The Role column defaults to "Viewer" at the database level.| Column | Type | Notes |
|---|
Id | int | Primary key, auto-increment |
Username | string | Unique index |
Email | string | Unique index |
PasswordHash | string | Hashed credential |
Role | string | Database default "Viewer"; valid values: Admin, Manager, Reviewer, Editor, Viewer |
Department | string | Organizational department |
IsActive | bool | Database default true; soft-disable without deleting |
CreatedAt | DateTime | UTC timestamp |
LastLoginAt | DateTime? | Set on successful login |
Each row represents one reviewer’s assignment and decision for a specific document. When a document is resubmitted after PendingChanges, all previous approval rows are deleted and a fresh set is created.| Column | Type | Notes |
|---|
Id | int | Primary key, auto-increment |
DocumentId | int (FK) | References Documents.Id with DeleteBehavior.Cascade |
ReviewerId | int (FK) | References Users.Id with DeleteBehavior.Restrict |
ApprovalOrder | int | Sequential order starting at 1 (first reviewer, second reviewer, etc.) |
Status | string | Stored as string enum: Pending, Approved, Rejected, RequestChanges |
Comments | string? | Required when Status is RequestChanges or Rejected |
CreatedAt | DateTime | UTC timestamp when reviewer was assigned |
ReviewedAt | DateTime? | UTC timestamp when the reviewer submitted their decision |
SQL Server holds an in-app audit trail for every state change and download action. This table feeds the audit report available to Admin and Manager roles.| Column | Type | Notes |
|---|
Id | int | Primary key, auto-increment |
DocumentId | int (FK) | References Documents.Id with DeleteBehavior.Cascade |
UserId | int? (FK) | References Users.Id with DeleteBehavior.SetNull; nullable to preserve logs if user is deleted |
Action | string | E.g. "Created", "Updated", "StatusChange", "Downloaded" |
OldValue | string? | Previous state or value (e.g. "Draft") |
NewValue | string? | New state or value (e.g. "UnderReview") |
IpAddress | string? | Remote IP address of the request |
CreatedAt | DateTime | UTC timestamp |
PostgreSQL 16 — Audit Store and Node.js API
PostgreSQL serves two distinct consumers and hosts two logically separate sets of tables within the same server. The .NET application writes audit entries to PostgreSQL via EF Core’s AuditDbContext. The Node.js API owns its own tables, managed entirely by Drizzle ORM with schema definitions in node-api/schema/. Migrations for each consumer are stored separately: EF Core migrations in QualityDocD/Migrations/AuditDb/ and Drizzle schema in node-api/schema/.
The AuditDbContext manages three tables on the PostgreSQL side of the .NET application. These tables provide a compliance-grade audit trail separated from the primary transactional database.audit_entries — Cross-database mirror of every document state change| Column | Type | Notes |
|---|
Id | int | Primary key |
DocumentId | int | References the SQL Server document ID (not a FK — cross-db reference) |
DocumentCode | string | Denormalized QD-XXXX code for query convenience |
DocumentTitle | string? | Denormalized title |
UserId | int? | References the SQL Server user ID |
Username | string? | Denormalized username |
Action | string | E.g. "Created", "StatusChange", "Approved", "Rejected" |
OldValue | string? | Previous state |
NewValue | string? | New state |
IpAddress | string? | Remote IP address |
CreatedAt | DateTime | UTC timestamp; indexed for range queries |
compliance_records — Aggregated compliance snapshot| Column | Type | Notes |
|---|
Category + Standard | string | Composite unique index |
Approved / Draft / UnderReview / Obsolete / Total | int | Document counts per status group |
LastUpdated | DateTime | When the aggregate was last refreshed |
access_logs — Per-document view and download tracking| Column | Type | Notes |
|---|
DocumentId | int | Which document was accessed |
Username | string? | Who accessed it |
IpAddress | string? | From where |
Action | string | Default "view" |
AccessedAt | DateTime | Indexed timestamp |
The Node.js API owns four tables in PostgreSQL. All are scoped to a companyId to enforce multi-tenancy. Schema is defined in node-api/schema/.companies — Top-level tenant records| Column | Type | Notes |
|---|
id | serial | Primary key |
name | text | Company display name |
slug | text | Unique URL-safe identifier |
createdAt | timestamp with timezone | Creation timestamp |
users — API user accounts (separate from .NET Users table)| Column | Type | Notes |
|---|
id | serial | Primary key |
companyId | integer (FK) | References companies.id |
name | text | Display name |
email | text | Unique across all companies |
passwordHash | text | Hashed credential |
role | text enum | VIEWER, COMMENTER, CONTRIBUTOR, OPERATOR, COMPANY_ADMIN, SUPER_ADMIN; default VIEWER |
createdAt | timestamp with timezone | Creation timestamp |
documents — Logical document entities| Column | Type | Notes |
|---|
id | serial | Primary key |
companyId | integer (FK) | References companies.id |
title | text | Document title |
format | text | Default "pdf" |
createdBy | integer (FK) | References users.id |
createdAt | timestamp with timezone | Creation timestamp |
document_versions — Versioned content snapshots| Column | Type | Notes |
|---|
id | serial | Primary key |
documentId | integer (FK) | References documents.id |
companyId | integer (FK) | References companies.id |
majorVersion | integer | Major version number; default 1 |
minorVersion | integer | Minor version number; default 0 |
versionNumber | text | Human-readable string (e.g. "1.0", "2.1") |
status | text enum | draft, current, obsolete |
contentUrl | text? | Optional URL to file content |
contentText | text? | Extracted plain-text content for indexing |
approvedBy | integer? (FK) | References users.id |
approvedAt | timestamp with timezone? | Approval timestamp |
createdBy | integer (FK) | References users.id |
createdAt | timestamp with timezone | Creation timestamp |
search_index — Pre-computed token index for fast full-text queries| Column | Type | Notes |
|---|
id | serial | Primary key |
documentId | integer (FK) | References documents.id |
versionId | integer (FK) | References document_versions.id |
companyId | integer (FK) | References companies.id |
titleTokens | text[] | Array of tokenized title terms |
bodyTokens | text[] | Array of tokenized body terms |
tokens | jsonb | Full token payload including positions and weights |
indexedAt | timestamp with timezone | When this index entry was last computed |
The .NET AuditDbContext writes are wrapped in a try/catch block that silently swallows failures: /* PostgreSQL no disponible — continúa sin bloquear */. This is an intentional design decision — a PostgreSQL outage must never block a document state change in SQL Server. Audit entries may be lost during a PostgreSQL outage, so monitoring PostgreSQL availability is essential for compliance workflows.
MongoDB 7 — Full-Text Metadata Store
MongoDB stores a denormalized snapshot of every document’s metadata in the document_metadata collection inside the qualitydoc_meta database. This collection is the backend for both the search microservice (called by DocumentService.SearchAsync() via HTTP) and the Node.js API. The MongoDbContext creates all required indexes at startup using EnsureIndexes(), including a weighted full-text index.
When the search microservice is unavailable, DocumentService.SearchAsync() automatically falls back to a SQL Server LIKE query using Title.Contains(), Description.Contains(), Code.Contains(), and Tags.Contains(). Results are capped at 50 documents. This fallback is logged at the Warning level: "Search service no disponible: {Message}. Usando SQL Server.".
Data Consistency: The Dual-Write Pattern
QualityDocD uses a synchronous-then-async dual-write pattern. SQL Server is always written first within the main transaction. After the SQL Server save succeeds, the service writes to PostgreSQL audit and syncs to MongoDB (via HTTP POST to the search microservice). Neither of these secondary writes is part of a distributed transaction.
SQL Server write (synchronous, authoritative)
_sql.SaveChangesAsync() commits the document state change and creates an AuditLog row in SQL Server. This is the only write that can cause the HTTP request to fail — if it throws, no secondary writes are attempted.
PostgreSQL audit write (async, non-blocking)
SyncPgAuditAsync() writes an AuditEntry row to PostgreSQL. This is wrapped in a try/catch that logs a warning and continues on failure. The SQL Server AuditLog serves as the fallback if this write is lost.
MongoDB sync (async, non-blocking)
SyncSearchServiceAsync() sends an HTTP POST to the Node.js search service, which upserts the DocumentMeta document in MongoDB. Failure is logged at Warning level and does not affect the HTTP response. Search results may be stale by one event in the rare case of a sync failure.
HTTP Request
│
▼
SQL Server ──────────────────── Source of Truth (must succeed)
│
├──► PostgreSQL audit_entries ── Non-blocking, best-effort
│
└──► MongoDB document_metadata ─ Non-blocking via HTTP to Search Service
Because the secondary writes are not transactional with the SQL Server write, it is possible for PostgreSQL or MongoDB to be temporarily out of sync with SQL Server after a partial failure. In practice, SQL Server’s AuditLogs table and Documents table are always consistent with each other. If you observe discrepancies in search results or audit reports, the SQL Server data is the authoritative source.