KnowledgeIngestionService — DDL appendix

Generated from the architecture model

This page is rendered from model/**/*.yaml by tools/docs_model during mkdocs build. Edit the YAML, not this page; hand-written design lives on the linked service, platform and plane pages.

Dialect shown: SQL Server (nvarchar, uniqueidentifier, datetimeoffset). Migrations are authored as FluentMigrator classes in the service's DatabaseModel.Migrations project; the TenantId column and the NHibernate tenant filter follow canonical ADR-0100 in SharedDb mode.

dbo.KnowledgeSources

Owner: KnowledgeSource

CREATE TABLE [dbo].[KnowledgeSources] (
    [SourceId] uniqueidentifier NOT NULL,
    [TenantId] nvarchar(64) NOT NULL,
    [ProjectId] nvarchar(64) NULL,
    [Kind] int NOT NULL,
    [Reference] nvarchar(1024) NOT NULL,
    [ConnectionId] uniqueidentifier NULL,
    [Extractor] nvarchar(32) NOT NULL,
    [Classification] nvarchar(16) NOT NULL,
    [AclMappingJson] nvarchar(max) NOT NULL,
    [SyncPolicyJson] nvarchar(2000) NOT NULL,
    [State] int NOT NULL,
    [LastSnapshotId] uniqueidentifier NULL,
    [Version] rowversion NOT NULL,
    CONSTRAINT [PK_KnowledgeSources] PRIMARY KEY ([SourceId])
);
CREATE UNIQUE INDEX [UX_Sources_Tenant_Reference] ON [dbo].[KnowledgeSources] ([TenantId], [Reference]);

Column	Type	Null	Key	Description
SourceId	uniqueidentifier	no	PK
TenantId	nvarchar(64)	no
ProjectId	nvarchar(64)	yes
Kind	int	no
Reference	nvarchar(1024)	no
ConnectionId	uniqueidentifier	yes
Extractor	nvarchar(32)	no
Classification	nvarchar(16)	no
AclMappingJson	nvarchar(max)	no
SyncPolicyJson	nvarchar(2000)	no
State	int	no
LastSnapshotId	uniqueidentifier	yes
Version	rowversion	no

dbo.Collections

Owner: Collection

CREATE TABLE [dbo].[Collections] (
    [CollectionId] uniqueidentifier NOT NULL,
    [TenantId] nvarchar(64) NOT NULL,
    [ProjectId] nvarchar(64) NOT NULL,
    [Name] nvarchar(128) NOT NULL,
    [SourceIdsJson] nvarchar(max) NOT NULL,
    [EmbeddingProfileVersion] nvarchar(32) NOT NULL,
    CONSTRAINT [PK_Collections] PRIMARY KEY ([CollectionId])
);
CREATE UNIQUE INDEX [UX_Collections_Project_Name] ON [dbo].[Collections] ([ProjectId], [Name]);

Column	Type	Null	Key	Description
CollectionId	uniqueidentifier	no	PK
TenantId	nvarchar(64)	no
ProjectId	nvarchar(64)	no
Name	nvarchar(128)	no
SourceIdsJson	nvarchar(max)	no
EmbeddingProfileVersion	nvarchar(32)	no

dbo.SourceSnapshots

Owner: SourceSnapshot

CREATE TABLE [dbo].[SourceSnapshots] (
    [SnapshotId] uniqueidentifier NOT NULL,
    [SourceId] uniqueidentifier NOT NULL,
    [TenantId] nvarchar(64) NOT NULL,
    [SourceVersion] nvarchar(128) NOT NULL,
    [State] int NOT NULL,
    [DocumentCount] int NOT NULL DEFAULT (0),
    [ChunkCount] int NOT NULL DEFAULT (0),
    [ContentRef] nvarchar(512) NOT NULL,
    [TakenAt] datetimeoffset NOT NULL,
    [PublishedAt] datetimeoffset NULL,
    CONSTRAINT [PK_SourceSnapshots] PRIMARY KEY ([SnapshotId])
);
ALTER TABLE [dbo].[SourceSnapshots] ADD CONSTRAINT [FK_SourceSnapshots_KnowledgeSources_SourceId] FOREIGN KEY ([SourceId]) REFERENCES [dbo].[KnowledgeSources] ([SourceId]);
CREATE UNIQUE INDEX [UX_Snapshots_Source_Version] ON [dbo].[SourceSnapshots] ([SourceId], [SourceVersion]);

Column	Type	Null	Key	Description
SnapshotId	uniqueidentifier	no	PK
SourceId	uniqueidentifier	no	FK → KnowledgeSources.SourceId
TenantId	nvarchar(64)	no
SourceVersion	nvarchar(128)	no
State	int	no
DocumentCount	int	no
ChunkCount	int	no
ContentRef	nvarchar(512)	no
TakenAt	datetimeoffset	no
PublishedAt	datetimeoffset	yes

dbo.Documents

Owner: Document

CREATE TABLE [dbo].[Documents] (
    [DocumentId] uniqueidentifier NOT NULL,
    [SnapshotId] uniqueidentifier NOT NULL,
    [TenantId] nvarchar(64) NOT NULL,
    [Path] nvarchar(1024) NOT NULL,
    [MediaType] nvarchar(128) NOT NULL,
    [ContentHash] nvarchar(128) NOT NULL,
    [MetadataJson] nvarchar(max) NOT NULL,
    [AclScopeJson] nvarchar(2000) NOT NULL,
    [Classification] nvarchar(16) NOT NULL,
    CONSTRAINT [PK_Documents] PRIMARY KEY ([DocumentId])
);
ALTER TABLE [dbo].[Documents] ADD CONSTRAINT [FK_Documents_SourceSnapshots_SnapshotId] FOREIGN KEY ([SnapshotId]) REFERENCES [dbo].[SourceSnapshots] ([SnapshotId]);
CREATE INDEX [IX_Documents_Snapshot_Path] ON [dbo].[Documents] ([SnapshotId], [Path]);

Column	Type	Null	Key	Description
DocumentId	uniqueidentifier	no	PK
SnapshotId	uniqueidentifier	no	FK → SourceSnapshots.SnapshotId
TenantId	nvarchar(64)	no
Path	nvarchar(1024)	no
MediaType	nvarchar(128)	no
ContentHash	nvarchar(128)	no
MetadataJson	nvarchar(max)	no
AclScopeJson	nvarchar(2000)	no
Classification	nvarchar(16)	no

dbo.Chunks

Owner: Chunk · Retention: until the snapshot is superseded and its retention window passes; deleted with the source (D31)

CREATE TABLE [dbo].[Chunks] (
    [ChunkId] uniqueidentifier NOT NULL,
    [DocumentId] uniqueidentifier NOT NULL,
    [TenantId] nvarchar(64) NOT NULL,
    [Ordinal] int NOT NULL,
    [ContentHash] nvarchar(128) NOT NULL,
    [TokenCount] int NOT NULL,
    [TrustTier] int NOT NULL,
    [MetadataJson] nvarchar(2000) NOT NULL,
    CONSTRAINT [PK_Chunks] PRIMARY KEY ([ChunkId])
);
ALTER TABLE [dbo].[Chunks] ADD CONSTRAINT [FK_Chunks_Documents_DocumentId] FOREIGN KEY ([DocumentId]) REFERENCES [dbo].[Documents] ([DocumentId]);
CREATE UNIQUE INDEX [UX_Chunks_Document_Ordinal] ON [dbo].[Chunks] ([DocumentId], [Ordinal]);

Column	Type	Null	Key	Description
ChunkId	uniqueidentifier	no	PK
DocumentId	uniqueidentifier	no	FK → Documents.DocumentId
TenantId	nvarchar(64)	no
Ordinal	int	no
ContentHash	nvarchar(128)	no
TokenCount	int	no
TrustTier	int	no
MetadataJson	nvarchar(2000)	no