> For clean Markdown of any page, append .md to the page URL.
> For a complete documentation index, see https://cla-demo.postmansolutions.com/llms.txt.
> For AI client integration (Claude Code, Cursor, etc.), connect to the MCP server at https://cla-demo.postmansolutions.com/_mcp/server.

# AP AutoExtract to Documents AI Database Migration from Postgres to MSSQL

1. Inventory (Postgres Features Used)

* Databases: talaria (app data), temporal\*, temporal\_visibility\* (\*Temporal OSS does NOT natively support SQL Server today; keep Postgres/MySQL for Temporal or isolate those DBs).
* Data types: SERIAL, VARCHAR, TEXT, JSONB, TIMESTAMPTZ, DECIMAL(p,s), UUID (as text), BOOL.
* Constraints: UNIQUE, FOREIGN KEY (some ON DELETE CASCADE), composite uniques.
* Views: vw\_logs\_engine, vw\_logs\_exchange, vw\_UserClientRoles, vw\_UserRoles, vw\_BlockedAddresses, vw\_ExchangeSubscriptions, vw\_Users, vw\_Clients, vw\_UserClients.
* “ON CONFLICT DO NOTHING” used in seed inserts.

2. Type Mapping (Postgres → SQL Server)

* SERIAL -> INT IDENTITY(1,1)
* VARCHAR(n) -> VARCHAR(n)
* TEXT -> NVARCHAR(MAX) (or VARCHAR(MAX) if ASCII only)
* JSONB -> NVARCHAR(MAX) (with optional computed columns + JSON\_VALUE indexes or redesign to relational tables)
* TIMESTAMPTZ -> DATETIME2(3) (store in UTC; enforce at app layer)
* DECIMAL(p,s) -> DECIMAL(p,s)
* BOOL -> BIT
* UUID stored as text -> UNIQUEIDENTIFIER (if you convert) or keep VARCHAR(48); prefer UNIQUEIDENTIFIER for indexing.
* NOW() default -> SYSUTCDATETIME()
* ON CONFLICT DO NOTHING -> MERGE pattern or IF NOT EXISTS (…) INSERT.

3. Table-by-Table Notes (talaria)
   clients

* JSONB config: store as NVARCHAR(MAX). If frequent key lookups, add persisted computed columns: e.g. config\_displayName AS JSON\_VALUE(\[config\_json], '\$.displayName') PERSISTED and index.
  users
* hashed\_password, salt, iterations unchanged.
* last\_logged\_on VARCHAR(24) -> consider DATETIME2 if actual timestamp.
  user\_client (bridge)
* Keep composite unique (user\_id, client\_id).
  roles, permissions, role\_permissions
* Straightforward translation; TEXT -> NVARCHAR(MAX) only if long descriptions; else VARCHAR(400).
  user\_roles, user\_client\_roles
* Maintain composite uniques; ensure indexed foreign keys.
  groups, user\_groups
* Same pattern.
  documents
* document\_hash UNIQUE; consider ADD UNIQUE INDEX.
  invoices
* Many monetary DECIMAL(10,2): OK.
* Stage/status frequently filtered: add nonclustered indexes.
  invoice\_snapshots
* Potentially large; partition by snapshot\_date (monthly) in Azure SQL Hyperscale or Managed Instance for growth.
  invoice\_line\_items
* DECIMAL(12,4) preserved.
  workflows, workflow\_logs
* workflow\_id (string) vs internal id: add nonclustered index on workflow\_id.
  engine\_logs, exchange\_logs
* payload JSONB -> NVARCHAR(MAX); if querying fields, consider extraction pipeline or separate detail table; heavy logging may justify moving to Azure Log Analytics instead of RDBMS.
  exchange\_blocked\_senders
* Index (client\_id, value).
  exchange\_inbox\_subscriptions/tokens
* Unique constraints map directly; DATETIME2 for timestamps.
  webhooks
* UNIQUE (client\_id, url) fine.
  feedback
* fields JSONB -> NVARCHAR(MAX).

4. Views Translation
   Replace LIMIT 1000 with TOP (1000). Example:
   CREATE OR ALTER VIEW vw\_logs\_engine AS
   SELECT TOP (1000)
   id,
   \[timestamp],
   log\_level AS level,
   message,
   client\_id,
   invoice\_id
   FROM engine\_logs
   ORDER BY \[timestamp] DESC;
   Ordering in a view without TOP is ignored in SQL Server; keep TOP to emulate snapshot list.

5. Sample T-SQL DDL Patterns (Representative)
   clients:
   CREATE TABLE dbo.clients (
   id INT IDENTITY(1,1) PRIMARY KEY,
   client\_id VARCHAR(48) UNIQUE,
   client\_name VARCHAR(100) UNIQUE,
   display\_name VARCHAR(100),
   contact\_name VARCHAR(100),
   contact\_email VARCHAR(100),
   config NVARCHAR(MAX),
   created\_on DATETIME2(3) NOT NULL CONSTRAINT DF\_clients\_created\_on DEFAULT SYSUTCDATETIME(),
   modified\_on DATETIME2(3) NOT NULL CONSTRAINT DF\_clients\_modified\_on DEFAULT SYSUTCDATETIME(),
   enabled BIT
   );
   users:
   CREATE TABLE dbo.users (
   id INT IDENTITY(1,1) PRIMARY KEY,
   user\_id VARCHAR(100) UNIQUE,
   username VARCHAR(100),
   first\_name VARCHAR(100),
   last\_name VARCHAR(100),
   hashed\_password VARCHAR(200),
   salt VARCHAR(24),
   iterations INT,
   email VARCHAR(100) UNIQUE,
   api\_key VARCHAR(48),
   created\_on DATETIME2(3) NOT NULL DEFAULT SYSUTCDATETIME(),
   modified\_on DATETIME2(3) NOT NULL DEFAULT SYSUTCDATETIME(),
   enabled BIT,
   last\_logged\_on VARCHAR(24),
   selected\_client\_name VARCHAR(100)
   );
   (Repeat pattern for others.)

6. JSONB Strategy Options
   Option A (Minimal): Store as NVARCHAR(MAX), no indexing—fastest migration.
   Option B (Hybrid): Create computed persisted columns for high-value keys, index them.
   Option C (Refactor): Normalize key substructures (e.g., stages config) into relational tables—higher effort, better query performance.

7. Index Strategy Additions

* invoices: INDEX IX\_invoices\_stage\_status (stage, status, client\_id)
* invoice\_line\_items: INDEX IX\_line\_items\_invoice (invoice\_id)
* workflows: INDEX IX\_workflows\_workflow\_id (workflow\_id)
* engine\_logs/exchange\_logs: filtered or partitioned indexes on timestamp for retention mgmt.

8. Temporal Engine Databases
   Do not migrate temporal / temporal\_visibility to SQL Server unless Temporal project adds support. Keep Postgres or move to MySQL if consolidation required. Isolate app schema from Temporal schema for clarity.

9. Migration Phases
   Phase 0: Governance

* Decide Azure SQL SKU (Business Critical vs General Purpose; consider Hyperscale for large logs).
* Enable AAD authentication; plan Managed Identity for app connectivity.

Phase 1: Assessment

* Export Postgres statistics (row counts, index usage).
* Classify JSONB access patterns.

Phase 2: Schema Translation

* Generate baseline T-SQL via tooling (pg2sql converters) then manually adjust types & constraints as above.
* Add SCD (system-versioned temporal tables) optionally for critical audit (e.g., invoices) if needed.

Phase 3: Prototype & Performance

* Load subset (5–10%) with Azure Data Factory or pg\_dump + custom ETL.
* Validate query plans parity; add missing indexes.

Phase 4: Full Data Migration

* Freeze writes (or implement dual-write / CDC using Debezium).
* Bulk copy order:
  1. clients, users, roles, permissions
  2. link tables (user\_client, role\_permissions, user\_roles, user\_client\_roles, groups, user\_groups)
  3. documents
  4. invoices
  5. invoice\_line\_items
  6. invoice\_snapshots
  7. workflows, workflow\_logs
  8. logs (optional; consider archive strategy)
* Use BULK INSERT / bcp / Azure Data Factory Copy. Disable nonclustered indexes during massive loads, then rebuild.

Phase 5: Cutover

* Deploy updated application config (connection string to Azure SQL; use Managed Identity).
* Run checksum validation (row counts + sample hash of key columns).
* Release.

Phase 6: Post-Cutover

* Monitor DTU / vCore utilization, query store, deadlocks.
* Implement automatic index tuning (review suggestions; do not blindly accept).

Rollback Plan

* Keep Postgres in read-only snapshot for N days.
* Have a reversible data sync script (if limited delta window) or accept downtime rollback by DNS/app setting revert.

10. Application Code Changes

* Replace Postgres-specific upserts (ON CONFLICT DO NOTHING) with:
  IF NOT EXISTS (SELECT 1 FROM dbo.clients WHERE client\_id = @client\_id)
  INSERT …
* Adjust any raw SQL quoting differences (double quotes → brackets or none).
* Replace NOW() with SYSUTCDATETIME().
* JSON operations moved to app layer (deserialize in Python) or use OPENJSON (if migrating to T-SQL usage later).

11. Security & Compliance

* Use Azure AD authentication; assign least-privilege roles (db\_datareader, db\_datawriter, custom for DDL).
* Store secrets (if any legacy) in Key Vault; application uses Managed Identity.
* Enable Transparent Data Encryption (default).
* Enable Auditing & Threat Detection.

12. Performance Considerations

* Parameterize all queries (already typical in drivers).
* Connection pooling via your Python driver (e.g., asyncpg replacement -> pyodbc / ODBC with pooling).
* Consider memory-optimized temp structures only if heavy temp table usage (not seen here).
* Offload large log tables to Azure Log Analytics or Event Hub pipeline.

13. Testing Checklist

* Referential integrity (count mismatches).
* Sample invoice lifecycle in new DB.
* Role/permission resolution queries.
* Views return expected columns & row caps.
* Performance baseline (p95 latency comparable or better).

14. Optional Enhancements

* Introduce system-versioned temporal tables for invoices & invoice\_line\_items.
* Partition large log tables by month.
* Materialized reporting tables for analytics instead of ad-hoc JSON parsing.

15. Summary Risk Flags

* JSONB heavy queries may degrade unless refactored or indexed logically.
* Temporal engine DB cannot be moved to SQL Server now—plan hybrid persistence.
* Large log growth could inflate Azure SQL costs: consider alternate sink.

JSON Mapping File:

```
{

    "metadata": {

      "sourceDatabase": "PostgreSQL",

      "targetDatabase": "Microsoft SQL Server (Azure SQL)",

      "schemaName": "dbo",

      "timezoneStrategy": "Store UTC in DATETIME2(3); enforce in application",

      "jsonStrategy": "Store JSONB as NVARCHAR(MAX); optional computed columns for indexed keys",

      "temporalRetention": "Consider system-versioned temporal tables for invoices & line items in later phase",

      "excludedDatabases": ["temporal", "temporal_visibility (retain on Postgres; Temporal not migrated)"]

    },

    "typeMappings": {

      "SERIAL": "INT IDENTITY(1,1)",

      "VARCHAR(n)": "VARCHAR(n)",

      "TEXT": "NVARCHAR(MAX)",

      "JSONB": "NVARCHAR(MAX)",

      "TIMESTAMPTZ": "DATETIME2(3)",

      "TIMESTAMP": "DATETIME2(3)",

      "DECIMAL(p,s)": "DECIMAL(p,s)",

      "BOOL": "BIT",

      "UUID(as text)": "UNIQUEIDENTIFIER (or keep VARCHAR(36/48) if not normalized)",

      "NOW()": "SYSUTCDATETIME()"

    },

    "globalDefaults": {

      "created_on": "DEFAULT SYSUTCDATETIME()",

      "modified_on": "DEFAULT SYSUTCDATETIME()",

      "date_uploaded": "DEFAULT SYSUTCDATETIME()",

      "snapshot_date": "DEFAULT SYSUTCDATETIME()"

    },

    "tables": {

      "clients": {

        "target": "dbo.clients",

        "columns": {

          "id": { "source": "SERIAL", "target": "INT IDENTITY(1,1)", "pk": true },

          "client_id": { "source": "VARCHAR(48)", "target": "VARCHAR(48)", "unique": true },

          "client_name": { "source": "VARCHAR(100)", "target": "VARCHAR(100)", "unique": true },

          "display_name": { "source": "VARCHAR(100)", "target": "VARCHAR(100)" },

          "contact_name": { "source": "VARCHAR(100)", "target": "VARCHAR(100)" },

          "contact_email": { "source": "VARCHAR(100)", "target": "VARCHAR(100)" },

          "config": { "source": "JSONB", "target": "NVARCHAR(MAX)", "indexedKeysCandidate": ["stages", "features"] },

          "created_on": { "source": "TIMESTAMPTZ", "target": "DATETIME2(3)" },

          "modified_on": { "source": "TIMESTAMPTZ", "target": "DATETIME2(3)" },

          "enabled": { "source": "BOOL", "target": "BIT" }

        },

        "indexes": [

          { "name": "UQ_clients_client_id", "columns": ["client_id"], "unique": true },

          { "name": "UQ_clients_client_name", "columns": ["client_name"], "unique": true }

        ],

        "notes": "Optionally add computed columns for JSON config if queried."

      },

      "users": {

        "columns": {

          "id": { "source": "SERIAL", "target": "INT IDENTITY", "pk": true },

          "user_id": { "source": "VARCHAR(100)", "target": "VARCHAR(100)", "unique": true },

          "username": { "source": "VARCHAR(100)", "target": "VARCHAR(100)" },

          "first_name": { "source": "VARCHAR(100)", "target": "VARCHAR(100)" },

          "last_name": { "source": "VARCHAR(100)", "target": "VARCHAR(100)" },

          "hashed_password": { "source": "VARCHAR(200)", "target": "VARCHAR(200)" },

          "salt": { "source": "VARCHAR(24)", "target": "VARCHAR(24)" },

          "iterations": { "source": "INT", "target": "INT" },

          "email": { "source": "VARCHAR(100)", "target": "VARCHAR(100)", "unique": true },

          "api_key": { "source": "VARCHAR(48)", "target": "VARCHAR(48)" },

          "created_on": { "source": "TIMESTAMPTZ", "target": "DATETIME2(3)" },

          "modified_on": { "source": "TIMESTAMPTZ", "target": "DATETIME2(3)" },

          "enabled": { "source": "BOOL", "target": "BIT" },

          "last_logged_on": { "source": "VARCHAR(24)", "target": "VARCHAR(24)" },

          "selected_client_name": { "source": "VARCHAR(100)", "target": "VARCHAR(100)", "fk": { "ref": "clients.client_name", "enforce": false (app-level) } }

        },

        "indexes": [

          { "name": "UQ_users_user_id", "columns": ["user_id"], "unique": true },

          { "name": "UQ_users_email", "columns": ["email"], "unique": true }

        ]

      },

      "user_client": {

        "columns": {

          "id": { "source": "SERIAL", "target": "INT IDENTITY", "pk": true },

          "user_id": { "source": "VARCHAR(100)", "target": "VARCHAR(100)", "fk": "users.user_id", "onDelete": "CASCADE" },

          "client_id": { "source": "VARCHAR(200)", "target": "VARCHAR(200)", "fk": "clients.client_id" }

        },

        "uniqueConstraints": [{ "name": "UQ_user_client", "columns": ["user_id", "client_id"] }],

        "indexes": [{ "name": "IX_user_client_user", "columns": ["user_id"] }]

      },

      "roles": {

        "columns": {

          "id": { "source": "VARCHAR(100)", "target": "VARCHAR(100)", "pk": true },

          "role_name": { "source": "TEXT", "target": "VARCHAR(150)" },

          "description": { "source": "TEXT", "target": "NVARCHAR(MAX)" },

          "system_role": { "source": "BOOL", "target": "BIT" },

          "created_on": { "source": "TIMESTAMPTZ", "target": "DATETIME2(3)" },

          "modified_on": { "source": "TIMESTAMPTZ", "target": "DATETIME2(3)" }

        }

      },

      "permissions": {

        "columns": {

          "id": { "source": "VARCHAR(100)", "target": "VARCHAR(100)", "pk": true },

          "name": { "source": "TEXT", "target": "VARCHAR(150)" },

          "description": { "source": "TEXT", "target": "NVARCHAR(MAX)" },

          "created_on": { "source": "TIMESTAMPTZ", "target": "DATETIME2(3)" },

          "modified_on": { "source": "TIMESTAMPTZ", "target": "DATETIME2(3)" }

        }

      },

      "role_permissions": {

        "columns": {

          "id": { "source": "SERIAL", "target": "INT IDENTITY", "pk": true },

          "role_id": { "source": "VARCHAR(100)", "target": "VARCHAR(100)", "fk": "roles.id", "onDelete": "CASCADE" },

          "permission_id": { "source": "VARCHAR(100)", "target": "VARCHAR(100)", "fk": "permissions.id", "onDelete": "CASCADE" }

        },

        "indexes": [

          { "name": "IX_role_permissions_role", "columns": ["role_id"] },

          { "name": "IX_role_permissions_perm", "columns": ["permission_id"] }

        ]

      },

      "user_roles": {

        "columns": {

          "id": { "source": "SERIAL", "target": "INT IDENTITY", "pk": true },

          "user_id": { "source": "VARCHAR(100)", "target": "VARCHAR(100)", "fk": "users.user_id", "onDelete": "CASCADE" },

          "role_id": { "source": "VARCHAR(100)", "target": "VARCHAR(100)", "fk": "roles.id" }

        },

        "uniqueConstraints": [{ "name": "UQ_user_role", "columns": ["user_id", "role_id"] }]

      },

      "user_client_roles": {

        "columns": {

          "id": { "source": "SERIAL", "target": "INT IDENTITY", "pk": true },

          "user_id": { "source": "VARCHAR(100)", "target": "VARCHAR(100)", "fk": "users.user_id", "onDelete": "CASCADE" },

          "client_id": { "source": "VARCHAR(200)", "target": "VARCHAR(200)", "fk": "clients.client_id" },

          "role_id": { "source": "VARCHAR(100)", "target": "VARCHAR(100)", "fk": "roles.id" }

        },

        "uniqueConstraints": [{ "name": "UQ_user_client_role", "columns": ["user_id", "client_id", "role_id"] }]

      },

      "groups": {

        "columns": {

          "id": { "source": "SERIAL", "target": "INT IDENTITY", "pk": true },

          "group_id": { "source": "VARCHAR(100)", "target": "VARCHAR(100)", "unique": true },

          "client_id": { "source": "VARCHAR(200)", "target": "VARCHAR(200)", "fk": "clients.client_id" },

          "name": { "source": "VARCHAR(100)", "target": "VARCHAR(100)" },

          "description": { "source": "TEXT", "target": "NVARCHAR(MAX)" },

          "created_on": { "source": "TIMESTAMPTZ", "target": "DATETIME2(3)" },

          "modified_on": { "source": "TIMESTAMPTZ", "target": "DATETIME2(3)" }

        }

      },

      "user_groups": {

        "columns": {

          "id": { "source": "SERIAL", "target": "INT IDENTITY", "pk": true },

          "user_id": { "source": "VARCHAR(100)", "target": "VARCHAR(100)", "fk": "users.user_id", "onDelete": "CASCADE" },

          "group_id": { "source": "VARCHAR(100)", "target": "VARCHAR(100)", "fk": "groups.group_id" }

        },

        "uniqueConstraints": [{ "name": "UQ_user_group", "columns": ["user_id", "group_id"] }]

      },

      "documents": {

        "columns": {

          "id": { "source": "SERIAL", "target": "INT IDENTITY", "pk": true },

          "document_hash": { "source": "VARCHAR(48)", "target": "VARCHAR(48)", "unique": true },

          "name": { "source": "VARCHAR(200)", "target": "VARCHAR(200)" },

          "blob_path": { "source": "VARCHAR(800)", "target": "VARCHAR(800)" },

          "client_id": { "source": "VARCHAR(100)", "target": "VARCHAR(100)", "fk": "clients.client_id" },

          "date_uploaded": { "source": "TIMESTAMPTZ", "target": "DATETIME2(3)" }

        }

      },

      "invoices": {

        "columns": {

          "id": { "source": "SERIAL", "target": "INT IDENTITY", "pk": true },

          "document_id": { "source": "INT", "target": "INT", "fk": "documents.id" },

          "invoice_number": { "source": "VARCHAR(200)", "target": "VARCHAR(200)" },

          "purchase_order_number": { "source": "VARCHAR(200)", "target": "VARCHAR(200)" },

          "payment_terms": { "source": "VARCHAR(200)", "target": "VARCHAR(200)" },

          "customer_name": { "source": "VARCHAR(200)", "target": "VARCHAR(200)" },

          "customer_address": { "source": "VARCHAR(800)", "target": "VARCHAR(800)" },

          "shipping_address": { "source": "VARCHAR(800)", "target": "VARCHAR(800)" },

          "vendor_name": { "source": "VARCHAR(200)", "target": "VARCHAR(200)" },

          "vendor_address": { "source": "VARCHAR(800)", "target": "VARCHAR(800)" },

          "vendor_id": { "source": "VARCHAR(100)", "target": "VARCHAR(100)" },

          "invoice_date": { "source": "TIMESTAMPTZ", "target": "DATETIME2(3)" },

          "due_date": { "source": "TIMESTAMPTZ", "target": "DATETIME2(3)" },

          "total_amt": { "source": "DECIMAL(10,2)", "target": "DECIMAL(10,2)" },

          "subtotal_amt": { "source": "DECIMAL(10,2)", "target": "DECIMAL(10,2)" },

          "tax_amt": { "source": "DECIMAL(10,2)", "target": "DECIMAL(10,2)" },

          "shipping_amt": { "source": "DECIMAL(10,2)", "target": "DECIMAL(10,2)" },

          "discount_amt": { "source": "DECIMAL(10,2)", "target": "DECIMAL(10,2)" },

          "discount_pct": { "source": "DECIMAL(10,2)", "target": "DECIMAL(10,2)" },

          "surcharge_amt": { "source": "DECIMAL(10,2)", "target": "DECIMAL(10,2)" },

          "stage": { "source": "VARCHAR(50)", "target": "VARCHAR(50)" },

          "status": { "source": "VARCHAR(50)", "target": "VARCHAR(50)" },

          "substatus": { "source": "VARCHAR(50)", "target": "VARCHAR(50)" },

          "client_id": { "source": "VARCHAR(100)", "target": "VARCHAR(100)", "fk": "clients.client_id" },

          "created_on": { "source": "TIMESTAMPTZ", "target": "DATETIME2(3)" },

          "modified_on": { "source": "TIMESTAMPTZ", "target": "DATETIME2(3)" },

          "date_filed": { "source": "TIMESTAMPTZ", "target": "DATETIME2(3)" },

          "date_exported": { "source": "TIMESTAMPTZ", "target": "DATETIME2(3)" }

        },

        "indexes": [

          { "name": "IX_invoices_document_id", "columns": ["document_id"] },

          { "name": "IX_invoices_stage_status", "columns": ["stage", "status", "client_id"] }

        ],

        "partitioningCandidate": true

      },

      "invoice_snapshots": {

        "columns": { "...": "mirrors invoices + snapshot metadata" },

        "partitioningCandidate": true,

        "indexes": [{ "name": "IX_invoice_snapshots_invoice_id", "columns": ["invoice_id"] }]

      },

      "invoice_line_items": {

        "columns": {

          "id": { "source": "SERIAL", "target": "INT IDENTITY", "pk": true },

          "invoice_id": { "source": "INT", "target": "INT", "fk": "invoices.id" },

          "line_item_id": { "source": "VARCHAR(48)", "target": "VARCHAR(48)" },

          "purchase_order_number": { "source": "VARCHAR(50)", "target": "VARCHAR(50)" },

          "description": { "source": "VARCHAR(200)", "target": "VARCHAR(200)" },

          "product_code": { "source": "VARCHAR(200)", "target": "VARCHAR(200)" },

          "account_id": { "source": "VARCHAR(200)", "target": "VARCHAR(200)" },

          "amount": { "source": "DECIMAL(12,4)", "target": "DECIMAL(12,4)" },

          "unit_price": { "source": "DECIMAL(12,4)", "target": "DECIMAL(12,4)" },

          "uom": { "source": "VARCHAR(10)", "target": "VARCHAR(10)" },

          "ordered_qty": { "source": "DECIMAL(10,2)", "target": "DECIMAL(10,2)" },

          "shipped_qty": { "source": "DECIMAL(10,2)", "target": "DECIMAL(10,2)" },

          "discount_amt": { "source": "DECIMAL(10,2)", "target": "DECIMAL(10,2)" },

          "discount_pct": { "source": "DECIMAL(10,2)", "target": "DECIMAL(10,2)" },

          "created_on": { "source": "TIMESTAMPTZ", "target": "DATETIME2(3)" },

          "modified_on": { "source": "TIMESTAMPTZ", "target": "DATETIME2(3)" }

        },

        "indexes": [{ "name": "IX_line_items_invoice", "columns": ["invoice_id"] }]

      },

      "workflows": {

        "columns": {

          "id": { "source": "SERIAL", "target": "INT IDENTITY", "pk": true },

          "parent_workflow_id": { "source": "INT", "target": "INT", "fk": "workflows.id", "nullable": true },

          "workflow_id": { "source": "VARCHAR(100)", "target": "VARCHAR(100)" },

          "document_id": { "source": "INT", "target": "INT", "fk": "documents.id" },

          "client_id": { "source": "VARCHAR(48)", "target": "VARCHAR(48)", "fk": "clients.client_id" },

          "pdf_path": { "source": "VARCHAR(200)", "target": "VARCHAR(200)" },

          "pdf_prefix": { "source": "VARCHAR(200)", "target": "VARCHAR(200)" },

          "status": { "source": "VARCHAR(48)", "target": "VARCHAR(48)" },

          "created_on": { "source": "TIMESTAMPTZ", "target": "DATETIME2(3)" },

          "modified_on": { "source": "TIMESTAMPTZ", "target": "DATETIME2(3)" }

        },

        "indexes": [{ "name": "IX_workflows_workflow_id", "columns": ["workflow_id"] }]

      },

      "workflow_logs": {

        "columns": {

          "id": { "source": "SERIAL", "target": "INT IDENTITY", "pk": true },

          "workflow_id": { "source": "INT", "target": "INT", "fk": "workflows.id" },

          "previous_status": { "source": "VARCHAR(48)", "target": "VARCHAR(48)" },

          "current_status": { "source": "VARCHAR(48)", "target": "VARCHAR(48)" },

          "reason": { "source": "VARCHAR(200)", "target": "VARCHAR(200)" },

          "created_on": { "source": "TIMESTAMPTZ", "target": "DATETIME2(3)" }

        }

      },

      "engine_logs": {

        "columns": {

          "id": { "source": "SERIAL", "target": "INT IDENTITY", "pk": true },

          "timestamp": { "source": "TIMESTAMPTZ", "target": "DATETIME2(3)" },

          "log_type": { "source": "TEXT", "target": "VARCHAR(100)" },

          "log_level": { "source": "TEXT", "target": "VARCHAR(32)" },

          "status": { "source": "TEXT", "target": "VARCHAR(32)" },

          "code": { "source": "INTEGER", "target": "INT" },

          "workflow_id": { "source": "TEXT", "target": "VARCHAR(100)" },

          "client_id": { "source": "TEXT", "target": "VARCHAR(100)" },

          "invoice_id": { "source": "TEXT", "target": "VARCHAR(100)" },

          "message": { "source": "TEXT", "target": "NVARCHAR(MAX)" },

          "module": { "source": "TEXT", "target": "VARCHAR(100)" },

          "operation_id": { "source": "UUID", "target": "UNIQUEIDENTIFIER" },

          "duration_ms": { "source": "TEXT", "target": "VARCHAR(32)" },

          "payload": { "source": "JSONB", "target": "NVARCHAR(MAX)" }

        },

        "retentionStrategy": "Move to Log Analytics optional"

      },

      "exchange_logs": { "columns": { "...": "similar to engine_logs (without invoice_id)" } },

      "exchange_blocked_senders": {

        "columns": {

          "id": { "source": "SERIAL", "target": "INT IDENTITY", "pk": true },

          "client_id": { "source": "VARCHAR(200)", "target": "VARCHAR(200)", "fk": "clients.client_id" },

          "value": { "source": "VARCHAR(200)", "target": "VARCHAR(200)" },

          "reason": { "source": "TEXT", "target": "NVARCHAR(MAX)" },

          "created_on": { "source": "TIMESTAMPTZ", "target": "DATETIME2(3)" }

        },

        "indexes": [{ "name": "IX_blocked_senders_client_value", "columns": ["client_id", "value"] }]

      },

      "exchange_inbox_subscriptions": {

        "columns": {

          "id": { "source": "SERIAL", "target": "INT IDENTITY", "pk": true },

          "client_id": { "source": "VARCHAR", "target": "VARCHAR(200)" },

          "inbox_address": { "source": "VARCHAR", "target": "VARCHAR(320)" },

          "subscription_id": { "source": "VARCHAR(255)", "target": "VARCHAR(255)", "unique": true },

          "event_actions_invoice_mark_as_read": { "source": "BOOLEAN", "target": "BIT", "default": "1" },

          "event_actions_invoice_move_to": { "source": "VARCHAR(255)", "target": "VARCHAR(255)" },

          "event_actions_other_mark_as_read": { "source": "BOOLEAN", "target": "BIT" },

          "event_actions_other_move_to": { "source": "VARCHAR(255)", "target": "VARCHAR(255)" },

          "created_at": { "source": "TIMESTAMPTZ", "target": "DATETIME2(3)" },

          "expires_at": { "source": "TIMESTAMPTZ", "target": "DATETIME2(3)", "nullable": true }

        },

        "uniqueConstraints": [{ "name": "UQ_client_inbox", "columns": ["client_id", "inbox_address"] }]

      },

      "exchange_inbox_tokens": {

        "columns": {

          "id": { "source": "SERIAL", "target": "INT IDENTITY", "pk": true },

          "subscription_id": { "source": "VARCHAR(255)", "target": "VARCHAR(255)", "fk": "exchange_inbox_subscriptions.subscription_id", "onDelete": "CASCADE" },

          "access_token": { "source": "TEXT", "target": "NVARCHAR(MAX)" },

          "refresh_token": { "source": "TEXT", "target": "NVARCHAR(MAX)" },

          "created_at": { "source": "TIMESTAMPTZ", "target": "DATETIME2(3)" },

          "expires_at": { "source": "TIMESTAMPTZ", "target": "DATETIME2(3)" }

        }

      },

      "webhooks": {

        "columns": {

          "id": { "source": "SERIAL", "target": "INT IDENTITY", "pk": true },

          "client_id": { "source": "VARCHAR(100)", "target": "VARCHAR(100)", "fk": "clients.client_id", "onDelete": "CASCADE" },

          "name": { "source": "VARCHAR(100)", "target": "VARCHAR(100)" },

          "url": { "source": "VARCHAR(100)", "target": "VARCHAR(300)" },

          "event_type": { "source": "VARCHAR(200)", "target": "VARCHAR(200)" }

        },

        "uniqueConstraints": [{ "name": "UQ_webhooks_client_url", "columns": ["client_id", "url"] }]

      },

      "feedback": {

        "columns": {

          "id": { "source": "SERIAL", "target": "INT IDENTITY", "pk": true },

          "topic": { "source": "TEXT", "target": "VARCHAR(150)" },

          "user_id": { "source": "VARCHAR(100)", "target": "VARCHAR(100)", "fk": "users.user_id" },

          "fields": { "source": "JSONB", "target": "NVARCHAR(MAX)" },

          "comments": { "source": "TEXT", "target": "NVARCHAR(MAX)" },

          "created_at": { "source": "TIMESTAMPTZ", "target": "DATETIME2(3)" }

        }

      }

    },

    "views": {

      "vw_logs_engine": {

        "sourceLimit": "LIMIT 1000",

        "target": "SELECT TOP (1000) id, [timestamp], log_level AS level, message, client_id, invoice_id FROM dbo.engine_logs ORDER BY [timestamp] DESC"

      },

      "vw_logs_exchange": {

        "target": "SELECT TOP (1000) id, [timestamp], log_level AS level, message, client_id, NULL AS invoice_id FROM dbo.exchange_logs ORDER BY [timestamp] DESC"

      },

      "vw_UserClientRoles": {

        "target": "SELECT u.username, c.client_name, r.role_name FROM dbo.user_client_roles ucr JOIN dbo.users u ON ucr.user_id = u.user_id JOIN dbo.clients c ON ucr.client_id = c.client_id JOIN dbo.roles r ON ucr.role_id = r.id ORDER BY u.username, c.client_name, r.role_name"

      },

      "vw_UserRoles": {

        "target": "SELECT u.username, r.role_name FROM dbo.user_roles ur JOIN dbo.users u ON ur.user_id = u.user_id JOIN dbo.roles r ON ur.role_id = r.id ORDER BY u.username, r.role_name"

      },

      "vw_BlockedAddresses": {

        "target": "SELECT c.client_name, ebs.value, ebs.reason, ebs.created_on FROM dbo.exchange_blocked_senders ebs JOIN dbo.clients c ON ebs.client_id = c.client_id ORDER BY c.client_name, ebs.value"

      },

      "vw_ExchangeSubscriptions": {

        "target": "SELECT * FROM dbo.exchange_inbox_subscriptions ORDER BY client_id, subscription_id, created_at"

      },

      "vw_Users": {

        "target": "SELECT u.username, u.email, u.selected_client_name FROM dbo.users u ORDER BY u.username"

      },

      "vw_Clients": {

        "target": "SELECT c.client_name, c.display_name, c.contact_name, c.contact_email FROM dbo.clients c ORDER BY c.display_name"

      },

      "vw_UserClients": {

        "target": "SELECT c.display_name AS client_display_name, u.username FROM dbo.user_client uc JOIN dbo.users u ON uc.user_id = u.user_id JOIN dbo.clients c ON uc.client_id = c.client_id ORDER BY c.client_name, u.username"

      }

    },

    "seedDataStrategy": {

      "approach": "Use MERGE or IF NOT EXISTS for idempotent inserts",

      "example": "IF NOT EXISTS (SELECT 1 FROM dbo.clients WHERE client_id=@client_id) INSERT ...",

      "conflictHandling": "Replace ON CONFLICT DO NOTHING with existence checks"

    },

    "migrationOrder": [

      "clients",

      "users",

      "roles",

      "permissions",

      "user_client",

      "role_permissions",

      "user_roles",

      "user_client_roles",

      "groups",

      "user_groups",

      "documents",

      "invoices",

      "invoice_line_items",

      "invoice_snapshots",

      "workflows",

      "workflow_logs",

      "engine_logs",

      "exchange_logs",

      "exchange_blocked_senders",

      "exchange_inbox_subscriptions",

      "exchange_inbox_tokens",

      "webhooks",

      "feedback",

      "views"

    ],

    "jsonRefactorOptions": {

      "config.clients": "Keep JSON; if query-heavy create table client_features(feature_name,value)",

      "engine_logs.payload": "Externalize to Log Analytics or blob archive for size reduction",

      "invoices.dynamicFields": "If growth continues, consider invoice_attributes (invoice_id, key, value)"

    },

    "performanceRecommendations": {

      "indexReview": "Enable Query Store; baseline top 20 queries",

      "partitioning": ["invoice_snapshots.snapshot_date", "engine_logs.timestamp", "exchange_logs.timestamp"],

      "compression": "Row compression on large line-item and snapshot tables",

      "batchLoad": "Disable nonclustered indexes during initial bulk load, rebuild after"

    },

    "security": {

      "encryption": "Transparent Data Encryption (default)",

      "auth": "Azure AD / Managed Identity",

      "secrets": "None stored in DB; use Key Vault externally",

      "rowLevelSecurityCandidate": ["invoices by client_id if multi-tenant requirement strengthens"]

    },

    "validation": {

      "rowCounts": "Compare Postgres vs SQL Server after load",

      "checksum": "Hash concatenation of key columns for sample sets",

      "referentialIntegrity": "Script FK orphan checks"

    },

    "rollback": {

      "strategy": "Keep Postgres read-only snapshot; switch connection string if abort",

      "windowHours": 24

    },

    "temporalEngineNote": "Retain Temporal DBs on supported Postgres backend; do not migrate until official support exists."

  }
```