How Documents are Stored

Polecat stores documents as JSON in SQL Server 2025 using the native json data type.

Document Table Structure

Each document type gets its own table with the prefix pc_doc_:

CREATE TABLE dbo.pc_doc_user (
    id uniqueidentifier NOT NULL,
    data json NOT NULL,
    type nvarchar(250) NULL,
    last_modified datetimeoffset NOT NULL DEFAULT SYSDATETIMEOFFSET(),
    created datetimeoffset NOT NULL DEFAULT SYSDATETIMEOFFSET(),
    dotnet_type nvarchar(500) NULL,
    CONSTRAINT pk_pc_doc_user PRIMARY KEY (id)
);

ID Column Types

The id column type varies based on the document's ID property:

.NET Type	SQL Server Type
Guid	uniqueidentifier
string	nvarchar(250)
int	int
long	bigint

Optional Columns

Additional columns are added based on document configuration:

Soft Deletes

is_deleted bit NOT NULL DEFAULT 0,
deleted_at datetimeoffset NULL

Guid Versioning (IVersioned)

guid_version uniqueidentifier NULL

Numeric Revisions (IRevisioned)

version int NOT NULL DEFAULT 0

Conjoined Tenancy

tenant_id nvarchar(250) NOT NULL DEFAULT 'DEFAULT'

The primary key becomes composite: PRIMARY KEY (tenant_id, id).

Metadata Tracking

correlation_id nvarchar(250) NULL,
causation_id nvarchar(250) NULL

JSON Storage

The data column uses SQL Server 2025's native json type. This provides:

• Server-side validation -- Invalid JSON is rejected at the database level
• JSON_VALUE() -- Extract scalar values for WHERE clauses
• JSON_QUERY() -- Extract objects and arrays
• JSON_MODIFY() -- Partial updates without full document rewrite
• Efficient storage -- Compact representation compared to nvarchar(max)

Upsert Strategy

Polecat uses SQL Server's MERGE statement for upsert operations:

MERGE pc_doc_user AS target
USING (VALUES (@id, @data, @type, ...)) AS source (id, data, type, ...)
ON target.id = source.id
WHEN MATCHED THEN UPDATE SET data = source.data, ...
WHEN NOT MATCHED THEN INSERT (id, data, type, ...) VALUES (...);