Skip to navigation
Account Payable to Fluxx HLD

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.
  1. 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.
  1. 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).

  1. 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.

  2. 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.)

  3. 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.

  4. 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.

  1. 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.

  2. 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.
  1. 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).
  1. 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.
  1. 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.
  1. 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).
  1. 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.
  1. 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: