DMS TAttachment System

Three integrated capabilities: a template-driven path resolution engine, a one-time Alfresco migration program, and a bulk file ingestion tool. All share the same IStorageProvider abstraction and TATTACHMENT table.

Files Created / Modified

Work Stream 1 — Framework

Work Streams 2 + 3 — DMS Module

Token Registry

All 18 system tokens have keys prefixed with _. They are resolved by SystemTokenResolver from static context (date, login, template master data).

Dynamic context tokens

Any key in ContextDictionary is resolved by DictionaryTokenResolver. Supports dot-notation for nested objects:

// ContextDictionary sent from FE:
{ "poNumber": "PO-2026-001", "vendor": { "vendorCode": "V042" } }

// In template:
{PONo}=poNumber|{VendorCode}=vendor.vendorCode

Template Format

Templates are pipe-separated pairs of {DisplayLabel}=resolverKey. The display label is cosmetic; only the resolver key matters.

-- Folder template (stored in MDOCUMENTSETDETAIL.LOCATIONTEMPLATE)
{Tenant}=_tenantCode|{Entity}=_entityCode|{Year}=_year|{Month}=_month|{ObjectId}=_objectId
→ Result: ACME/PURCHASEORDER/2026/07/12345/

-- File template (stored in MDOCUMENTSETDETAIL.DOCUMENTNAMETEMPLATE)
{DocType}=_docTypeCode|{SlNo}=_slNo|{Version}=_version|{Ext}=_ext
→ Result: GRN_001_1.pdf

-- Display name template (stored in MDOCUMENTSETDETAIL.DISPLAYNAMETEMPLATE)
{DocTypeName}=_docTypeName|{Date}=_yyyymmdd|{Version}=_version
→ Result: Goods Receipt Note - 20260704 - 1

Assembly rules

Sanitise rules

All resolved values are passed through SystemTokenResolver.Sanitise(): replaces / \ : * ? " < > | \s with _, strips leading/trailing underscores. null / empty → "_". Unresolved tokens → "_UNKNOWN_".

Phase 1 / Phase 2 Flow

Phase 2 trigger

AttachmentResolutionEnvelope now carries ObjectHeaderTypeId and HeaderRowGuid. After BulkResolveObjectIdAsync, AttachmentResolutionService calls IAttachmentPathResolutionService.ResolvePhase2Async within the same transaction.

// In AttachmentResolutionService.ResolveAsync — after BulkResolveObjectIdAsync:
if (envelope.ObjectHeaderTypeId > 0 && envelope.HeaderRowGuid != Guid.Empty)
{
    await _pathResolution
        .ResolvePhase2Async(envelope.ObjectHeaderTypeId, envelope.HeaderRowGuid, login, tx, ct)
        .ConfigureAwait(false);
}

Resolution Priority

Default templates (appsettings / AttachmentPathSettings)

DefaultFolderTemplate: "{TenantCode}=_tenantCode|{EntityCode}=_entityCode|{Year}=_year|{Month}=_month|{ObjectId}=_objectId"
DefaultFileTemplate:   "{DocTypeCode}=_docTypeCode|{SlNo}=_slNo|{Version}=_version|{Ext}=_ext"

Upload Service

IAttachmentUploadService.UploadAsync orchestrates the full upload pipeline in 7 steps:

Alfresco Migration — Architecture

Entity table discovery (no config table)

MENTITY already links to DBOBJECT (table name) and DBOBJECTFIELDS (PK column). The migration queries this at runtime — no TATTACHMENT_MIGRATION_CONFIG table needed.

-- EntityTableInfoDTO populated by:
SELECT
    E.ENTITYID    AS EntityId,  E.ENTITYCODE AS EntityCode,
    DO.DBOBJECTNAME  AS SourceTable,
    DF.DBFIELDNAME   AS PKColumn
FROM   DBO.MENTITY        E
JOIN   DBO.DBOBJECT       DO ON DO.DBOBJECTID = E.DBOBJECTID
JOIN   DBO.DBOBJECTFIELDS DF ON DF.DBOBJECTID = DO.DBOBJECTID
                             AND DF.ISPRIMARYKEY = 1
WHERE  E.ENTITYID = @ObjectTypeId

MCOMMONCONFIG keys

Migration Flow

Dry run

When DryRun=true, steps 7–10 are skipped. Log entry status → 4=Skipped. Lets admins verify file counts before committing writes.

Log status codes

Migration Schema

CREATE TABLE DBO.TATTACHMENT_MIGRATION_LOG (
    MigrationLogId  BIGINT        IDENTITY PRIMARY KEY,
    ObjectTypeId    INT           NOT NULL,
    ObjectId        INT           NOT NULL,
    AlfrescoNodeId  NVARCHAR(200),
    DisplayFileName NVARCHAR(500),
    AttachmentId    INT           NULL,   -- set after successful TATTACHMENT insert
    Status          TINYINT       NOT NULL DEFAULT 0,
    ErrorMessage    NVARCHAR(2000),
    StartedOn       DATETIME,
    CompletedOn     DATETIME,
    ClientId        INT           NOT NULL
);

New columns on TATTACHMENT:

Bulk Ingestion — Modes

Manifest CSV Format

FilePath,ObjectTypeId,ObjectId,DocumentSetDetailId,DisplayName,Tags,ContextJson
/data/attachments/grn_001.pdf,101,12345,4,Goods Receipt PO-001,GRN,"{""poNumber"":""PO-001""}"
/data/attachments/invoice.pdf,101,12345,5,Supplier Invoice,,
/data/attachments/report.xlsx,102,67890,8,Monthly Report,,

Folder Convention

Source folder layout: {SourceFolder}/{EntityCode}/{ObjectId}/

/attachments/
  PURCHASEORDER/
    12345/
      invoice.pdf
      grn.pdf
  SALESORDER/
    67890/
      order-confirmation.pdf

EntityCode is resolved to ObjectTypeId via SELECT ENTITYID FROM MENTITY WHERE ENTITYCODE=@Code. Unknown codes log a warning and skip that folder.

API Endpoints

FrameworkSL — Template Attachment

DMSSL — Migration

DMSSL — Bulk Ingestion

Stored Procedures

Path Resolution (migration: 20260605_TATTACHMENT_PathResolution.sql)

• SP_ATTACHMENT_LOAD_TEMPLATEJoins MDOCUMENTSETDETAIL + MDOCUMENTSET + MDOCUMENTTYPE + MMODULE; returns AttachmentTemplateData
• SP_ATTACHMENT_GET_NEXT_SLNO_VERSIONMAX(SLNO)+1 and MAX(VERSION)+1 scoped by ObjectId or RowGuid
• SP_ATTACHMENT_INSERTFull TATTACHMENT insert; SCOPE_IDENTITY() returned as AttachmentId
• SP_ATTACHMENT_GET_DEFERREDReturns ISPATHDEFERRED=1 rows for a given ObjectHeaderTypeId + HeaderRowGuid where OBJECTID > 0
• SP_ATTACHMENT_UPDATE_RESOLVED_PATHBulk update via AttachmentPathResolutionTVP; clears ISPATHDEFERRED=0
• SP_DOCUMENTSET_VALIDATE_TEMPLATEConfig-time uniqueness warnings for template conflicts

Migration (migration: 20260605_TATTACHMENT_Migration.sql)

• SP_MIGRATION_LOG_INSERTInserts a TATTACHMENT_MIGRATION_LOG row (Status=0=Pending); returns SCOPE_IDENTITY()
• SP_MIGRATION_LOG_UPDATEUpdates Status, AttachmentId, ErrorMessage, CompletedOn
• SP_MIGRATION_STATUSAggregated counts (Total, Done, Failed, Pending, InProgress, Skipped) by ObjectTypeId

Bulk Ingestion (migration: 20260605_TBULK_INGESTION.sql)

• SP_INGESTION_JOB_INSERTInserts TBULK_INGESTION_JOB; returns JobId
• SP_INGESTION_JOB_UPDATEUpdates Status, TotalFiles, ProcessedFiles, FailedFiles, CompletedOn
• SP_INGESTION_JOB_GETReturns BulkIngestionStatusDTO for a JobId
• SP_INGESTION_LOG_INSERTInserts TBULK_INGESTION_LOG row (per-file result)

DI Registration

FrameworkSL/Program.cs

// Attachment path settings + storage provider
builder.Services.Configure<AttachmentPathSettings>(
    builder.Configuration.GetSection(AttachmentPathSettings.Section));
builder.Services.AddGB5Storage(
    builder.Configuration.GetSection("StorageConfiguration"));

// IAttachmentUploadService, ITemplateResolutionEngine,
// IAttachmentPathResolutionService auto-registered via assembly scan

DMSSL/Program.cs

// Named HttpClient for Alfresco downloads (10-min timeout)
builder.Services.AddHttpClient("alfresco", client => {
    client.Timeout = TimeSpan.FromMinutes(10);
});

// Attachment settings (used by BulkIngestionBLL → IAttachmentUploadService)
builder.Services.Configure<AttachmentPathSettings>(
    builder.Configuration.GetSection(AttachmentPathSettings.Section));

// Assembly scan includes FrameworkBLL + FrameworkDAL for IAttachmentUploadService etc.
var dmsBLLAssembly       = Assembly.Load("DMSBLL");
var dmsDALAssembly       = Assembly.Load("DMSDAL");
var frameworkBLLAssembly = Assembly.Load("FrameworkBLL");
var frameworkDALAssembly = Assembly.Load("FrameworkDAL");

builder.Services.Scan(scan => scan
    .FromAssemblies(dmsBLLAssembly, dmsDALAssembly,
                    frameworkBLLAssembly, frameworkDALAssembly)
    .AddClasses(c => c.Where(t => !t.IsAbstract && !t.IsInterface))
    .AsImplementedInterfaces()
    .WithScopedLifetime());

StorageConfiguration (appsettings / Vault)

"StorageConfiguration": {
    "Provider": "Network",          // or "S3"
    "NetworkBasePath": "/mnt/attachments"
}