-- ============================================================================= -- Communication — Enterprise Communication Platform (ECP), Phase 1 -- Migration: 20260712 — Conversation & Comment Platform -- Database: Tenant business DB (PostgreSQL variant — see companion _SqlServer.sql for the -- SQL Server variant; both define the identical logical schema) -- Plan: /Users/venkatv/.claude/plans/in-legacy-app-we-virtual-catmull.md -- -- Tables (Phase 1): -- TCOMMENTTHREAD (1) — supersedes GB5Framework's TCOMMENT (see plan's "Why a new table" note; -- TCOMMENT/GetComment/SaveComment/DeleteComment/CommentHistory* have no -- callers outside GB5Framework — safe cutover, old endpoints marked -- [Obsolete] rather than deleted) -- TCOMMENTMENTION (1) — explicit @mention rows, notification-audience source #1 -- TCOMMENTREACTION (1) — small fixed reaction-type enum (like/love/laugh/insightful) -- -- Comment attachments: no new table — reuse existing TATTACHMENT polymorphic pattern, pointed at -- TCOMMENTTHREAD.COMMENTTHREADID via the MENTITY row seeded below. -- -- PK generation: application-generated (AutoNumber via GB5Shared.GenerateAutoNumber.AutoNumber, -- keyed "COMMENTTHREAD"/"COMMENTMENTION"/"COMMENTREACTION") — NOT SERIAL/IDENTITY, so the same -- INSERT SQL constant works unmodified against both PostgreSQL and SQL Server (no per-engine -- RETURNING/OUTPUT branching needed in CommunicationDAL), matching the DXP module's precedent. -- -- Column casing convention: ALL CAPS (matches GB5 SQL standard) -- Constraint naming: PK_{TABLE}, DF_{TABLE}_{COLUMN}, CK_{TABLE}_{COLUMN}, -- IX_{TABLE}_{COLUMNS}, UX_{TABLE}_{COLUMNS} -- Tenant scoping: TENANTID/DATABASENAME/DATABASETYPE carried directly on every table per the -- plan's design (mirrors CollabSpace's CollabSession precedent), in addition to -- the existing OBJECTTYPEID/OBJECTID/OUID/BIZTRANSACTIONID scoping columns -- TCOMMENT already proved out. -- ============================================================================= -- ───────────────────────────────────────────────────────────────────────────── -- 1. TCOMMENTTHREAD -- Threaded comment/message row, polymorphic via OBJECTTYPEID+OBJECTID -> MENTITY.ENTITYID. -- PARENTCOMMENTTHREADID: -1 = root comment (no parent). -- ROOTCOMMENTTHREADID: denormalized root id for fast whole-tree fetch (a root row points to -- its own COMMENTTHREADID). -- COMMENTSTATUS: 1=open 2=resolved 3=archive (business lifecycle of the conversation) -- ACCESSTYPE: 1=public 2=private (private requires PRIVATEUSERGROUPID membership) -- SECURITYMARKID: -> MSECURITYGROUPDETAIL.SECURITYGROUPDETAILID; -1 = no classification -- required. Reuses the existing MSECURITYGROUP/MECMRIGHTS framework model instead of an -- ad-hoc SECURITYLEVEL byte (see plan's "Access, security level & notification routing"). -- STATUS: row-level active/deleted flag (1=Active 2=Deleted), distinct from COMMENTSTATUS. -- ───────────────────────────────────────────────────────────────────────────── CREATE TABLE IF NOT EXISTS TCOMMENTTHREAD ( COMMENTTHREADID INTEGER PRIMARY KEY, -- application-generated (AutoNumber, key "COMMENTTHREAD") — not SERIAL, see header PARENTCOMMENTTHREADID INTEGER NOT NULL DEFAULT -1, ROOTCOMMENTTHREADID INTEGER NOT NULL, OBJECTTYPEID INTEGER NOT NULL, -- -> MENTITY.ENTITYID OBJECTID INTEGER NOT NULL, USERID INTEGER NOT NULL, -- author COMMENTTEXT TEXT NOT NULL, COMMENTSTATUS SMALLINT NOT NULL DEFAULT 1, -- 1=open 2=resolved 3=archive ACCESSTYPE SMALLINT NOT NULL DEFAULT 1, -- 1=public 2=private PRIVATEUSERGROUPID INTEGER NOT NULL DEFAULT -1, SECURITYMARKID INTEGER NOT NULL DEFAULT -1, -- -> MSECURITYGROUPDETAIL.SECURITYGROUPDETAILID BIZTRANSACTIONID INTEGER NOT NULL DEFAULT -1, OUID INTEGER NOT NULL DEFAULT -1, TENANTID INTEGER NOT NULL DEFAULT -1, DATABASENAME VARCHAR(100) NOT NULL, DATABASETYPE SMALLINT NOT NULL DEFAULT 0, -- 0=SQL 1=Oracle 2=PostGre 3=MySQL VERSION SMALLINT NOT NULL DEFAULT 0, STATUS SMALLINT NOT NULL DEFAULT 1, -- 1=Active 2=Deleted CREATEDBYID INTEGER NOT NULL DEFAULT -1, CREATEDON TIMESTAMP NOT NULL DEFAULT NOW(), MODIFIEDBYID INTEGER NOT NULL DEFAULT -1, MODIFIEDON TIMESTAMP NOT NULL DEFAULT NOW(), CONSTRAINT CHK_TCOMMENTTHREAD_COMMENTSTATUS CHECK (COMMENTSTATUS IN (1, 2, 3)), CONSTRAINT CHK_TCOMMENTTHREAD_ACCESSTYPE CHECK (ACCESSTYPE IN (1, 2)), CONSTRAINT CHK_TCOMMENTTHREAD_STATUS CHECK (STATUS IN (1, 2)) ); CREATE INDEX IF NOT EXISTS IX_TCOMMENTTHREAD_OBJECTTYPEID_OBJECTID ON TCOMMENTTHREAD (OBJECTTYPEID, OBJECTID); CREATE INDEX IF NOT EXISTS IX_TCOMMENTTHREAD_PARENTCOMMENTTHREADID ON TCOMMENTTHREAD (PARENTCOMMENTTHREADID); CREATE INDEX IF NOT EXISTS IX_TCOMMENTTHREAD_ROOTCOMMENTTHREADID ON TCOMMENTTHREAD (ROOTCOMMENTTHREADID); CREATE INDEX IF NOT EXISTS IX_TCOMMENTTHREAD_SECURITYMARKID ON TCOMMENTTHREAD (SECURITYMARKID) WHERE SECURITYMARKID <> -1; -- ───────────────────────────────────────────────────────────────────────────── -- 2. TCOMMENTMENTION -- Explicit @mention on a thread — notification-audience source #1 (see plan's -- "Notification audience" section: mentions + prior participants + MSUBSCRIPTION). -- ───────────────────────────────────────────────────────────────────────────── CREATE TABLE IF NOT EXISTS TCOMMENTMENTION ( COMMENTMENTIONID INTEGER PRIMARY KEY, -- application-generated (AutoNumber, key "COMMENTMENTION") COMMENTTHREADID INTEGER NOT NULL REFERENCES TCOMMENTTHREAD (COMMENTTHREADID), MENTIONEDUSERID INTEGER NOT NULL, ISNOTIFIED BOOLEAN NOT NULL DEFAULT FALSE, NOTIFIEDON TIMESTAMP, TENANTID INTEGER NOT NULL DEFAULT -1, DATABASENAME VARCHAR(100) NOT NULL, DATABASETYPE SMALLINT NOT NULL DEFAULT 0, STATUS SMALLINT NOT NULL DEFAULT 1, CREATEDBYID INTEGER NOT NULL DEFAULT -1, CREATEDON TIMESTAMP NOT NULL DEFAULT NOW(), MODIFIEDBYID INTEGER NOT NULL DEFAULT -1, MODIFIEDON TIMESTAMP NOT NULL DEFAULT NOW(), CONSTRAINT CHK_TCOMMENTMENTION_STATUS CHECK (STATUS IN (1, 2)), CONSTRAINT UX_TCOMMENTMENTION_THREAD_USER UNIQUE (COMMENTTHREADID, MENTIONEDUSERID) ); CREATE INDEX IF NOT EXISTS IX_TCOMMENTMENTION_MENTIONEDUSERID_ISNOTIFIED ON TCOMMENTMENTION (MENTIONEDUSERID, ISNOTIFIED); -- ───────────────────────────────────────────────────────────────────────────── -- 3. TCOMMENTREACTION -- REACTIONTYPE: 1=like 2=love 3=laugh 4=insightful (small fixed enum for v1 — not free emoji) -- ───────────────────────────────────────────────────────────────────────────── CREATE TABLE IF NOT EXISTS TCOMMENTREACTION ( COMMENTREACTIONID INTEGER PRIMARY KEY, -- application-generated (AutoNumber, key "COMMENTREACTION") COMMENTTHREADID INTEGER NOT NULL REFERENCES TCOMMENTTHREAD (COMMENTTHREADID), USERID INTEGER NOT NULL, REACTIONTYPE SMALLINT NOT NULL, -- 1=like 2=love 3=laugh 4=insightful TENANTID INTEGER NOT NULL DEFAULT -1, DATABASENAME VARCHAR(100) NOT NULL, DATABASETYPE SMALLINT NOT NULL DEFAULT 0, STATUS SMALLINT NOT NULL DEFAULT 1, CREATEDBYID INTEGER NOT NULL DEFAULT -1, CREATEDON TIMESTAMP NOT NULL DEFAULT NOW(), MODIFIEDBYID INTEGER NOT NULL DEFAULT -1, MODIFIEDON TIMESTAMP NOT NULL DEFAULT NOW(), CONSTRAINT CHK_TCOMMENTREACTION_REACTIONTYPE CHECK (REACTIONTYPE IN (1, 2, 3, 4)), CONSTRAINT CHK_TCOMMENTREACTION_STATUS CHECK (STATUS IN (1, 2)), CONSTRAINT UX_TCOMMENTREACTION_THREAD_USER_TYPE UNIQUE (COMMENTTHREADID, USERID, REACTIONTYPE) ); CREATE INDEX IF NOT EXISTS IX_TCOMMENTREACTION_COMMENTTHREADID ON TCOMMENTREACTION (COMMENTTHREADID); -- ============================================================================= -- MENTITY seed — Communication module's polymorphic object-type registration. -- REVISED after live-DB verification: MENTITY on the real GB5DEMO database is a 50-column -- reflection-based entity registry (GB5Solution/Tools/EntityCreationTool's target), not the -- simple 8-column lookup this seed originally assumed. That tool auto-allocates ENTITYID via -- MAUTONUMBER, which cannot be pinned to our already-deployed hardcoded constant -- (GB5Shared.GB5Constant.Constant.EntityConstant.OBJECTCOMMENTTHREAD = -1383500001), so we -- insert directly here instead of running the tool. Values for the ~40 reflection-only columns -- (BASECLASSID, MODULEID, DBOBJECTID, ASSEMBLYID, etc.) are copied verbatim from -- EntityCreationTool/appsettings.json's own "EntityDefaults" config block — the same defaults -- the tool itself would use for a freshly-created entity — with -1 for MODULEID/DBOBJECTID/ -- ASSEMBLYID specifically (no real module/dbobject/assembly registration backs these rows, -- since Communication is plain Dapper DAL with no dynamic entity/reflection loading). -- ============================================================================= INSERT INTO MENTITY ( ENTITYID, ENTITYCODE, ENTITYNAME, BASECLASSID, SECURITYLEVEL, ENTITYTYPE, ISPERSIST, SCOPE, PROJECTID, MODULEID, DBOBJECTID, ENTITYNATURE, SECTION, IMPLEMENTATIONTYPE, ASSEMBLYID, ISCODEAPPLICABLE, MAXCODESIZE, DEFAULTSIZE, ISMULTICATEGORY, ISAUTO, ISMANUAL, ISFORMULA, ORGANIZATIONUNITTYPE, ENTITYDEFINEDIN, REMARKS, ISREFERAL, ENTITYCATEGORY, SORTORDER, STATUS, VERSION, SOURCETYPE, CREATEDBYID, CREATEDON, MODIFIEDBYID, MODIFIEDON, POCOTODTOLINKID, POCOTOPICKLISTLINKID, CATEGORYMEMBERID, ISADDRESS, ISCONTACT, ISSEARCH, SAVEDEPLOYMENTID, DELETEDEPLOYMENTID, ADDONDBOBJECTID, MENUID, WEBFORMID, DISPLAYWEBFORMID, ISICEMAPREQUIRED, ISMULTITENANT, PICKLISTID ) SELECT -1383500001, 'COMMENTTHREAD', 'Comment Thread', -1, 1, 0, 0, 0, -1, -1, -1, 5, '', 0, -1, 1, 10, 10, 1, 1, 0, 1, 0, 'CommunicationDAL.DTOs.CommentThreadDTO, CommunicationDAL', '', 0, 0, 9999, 1, 1, 1, -1, NOW(), -1, NOW(), NULL, NULL, -1, 1, 1, 0, '-1', '-1', -1, -1, -1, -1, 0, 1, -1 WHERE NOT EXISTS (SELECT 1 FROM MENTITY WHERE ENTITYID = -1383500001); -- ============================================================================= -- MAUTONUMBER seeds — required before CommentThreadBLL/CommentReactionBLL can allocate any -- IDs (AutoNumber.GetNumberAsync throws "AutoNumber entry not found" without a row here). -- ============================================================================= INSERT INTO MAUTONUMBER (ENTITYID, ENTITYCODE, AUTOID) SELECT -1383500001, 'COMMENTTHREAD', -1499999999 WHERE NOT EXISTS (SELECT 1 FROM MAUTONUMBER WHERE ENTITYCODE = 'COMMENTTHREAD'); INSERT INTO MAUTONUMBER (ENTITYID, ENTITYCODE, AUTOID) SELECT -1383500002, 'COMMENTREACTION', -1499999999 WHERE NOT EXISTS (SELECT 1 FROM MAUTONUMBER WHERE ENTITYCODE = 'COMMENTREACTION'); INSERT INTO MAUTONUMBER (ENTITYID, ENTITYCODE, AUTOID) SELECT -1383500003, 'COMMENTMENTION', -1499999999 WHERE NOT EXISTS (SELECT 1 FROM MAUTONUMBER WHERE ENTITYCODE = 'COMMENTMENTION'); -- ============================================================================= -- MEVENTTYPE seeds — Communication audit events. Column list verified against the real -- SAVE_EVENTTYPE INSERT in GB5Framework/FrameworkDAL/Query/EventType/EventTypeQB.cs (the offline -- schema-registry entry for MEVENTTYPE only showed the base columns; DataSync's own migration's -- seed omitted several of them — this corrects that by using the full column list every real -- MEVENTTYPE insert in the codebase actually writes). EVENTTYPEID values match Constant.cs -- EventTypeConstant's Communication block — verify against live MEVENTTYPE before running. -- ============================================================================= INSERT INTO MEVENTTYPE ( EVENTTYPEID, EVENTTYPECODE, EVENTTYPENAME, TYPE, EVENTTYPENATURE, SECURITYLEVEL, CRITICALITY, LINKEDFORMID, NOTIFICATION, ALERT, SECTION, ENTITYID, SORTORDER, STATUS, SOURCETYPE, VERSION, CREATEDBYID, CREATEDON, MODIFIEDBYID, MODIFIEDON, EVENTCATEGORY, ISBIZDOMAIN, ISAUDIT, ISTELEMETRY, ISCOMPLIANCE, RETENTIONDAYS, STORAGETIER ) SELECT * FROM (VALUES (-1383500001, 'COMMENT.THREAD.CREATED', 'Comment Thread Created', 1, 1, 0, 1, -1, 0, 0, '', -1383500001, 0, 1, 1, 0, -1, NOW(), -1, NOW(), 1, 1, 1, 0, 0, 365, 1), (-1383500002, 'COMMENT.THREAD.REPLIED', 'Comment Thread Replied', 1, 1, 0, 1, -1, 0, 0, '', -1383500001, 0, 1, 1, 0, -1, NOW(), -1, NOW(), 1, 1, 1, 0, 0, 365, 1), (-1383500003, 'COMMENT.THREAD.STATUS', 'Comment Thread Status Changed', 1, 1, 0, 1, -1, 0, 0, '', -1383500001, 0, 1, 1, 0, -1, NOW(), -1, NOW(), 1, 1, 1, 0, 0, 365, 1), (-1383500004, 'COMMENT.THREAD.DELETED', 'Comment Thread Deleted', 1, 1, 0, 1, -1, 0, 0, '', -1383500001, 0, 1, 1, 0, -1, NOW(), -1, NOW(), 1, 1, 1, 0, 0, 365, 1), (-1383500005, 'COMMENT.MENTIONED', 'User Mentioned in Comment', 1, 1, 0, 1, -1, 0, 0, '', -1383500001, 0, 1, 1, 0, -1, NOW(), -1, NOW(), 1, 1, 1, 0, 0, 180, 1), (-1383500006, 'COMMENT.REACTED', 'Comment Reacted', 1, 1, 0, 1, -1, 0, 0, '', -1383500001, 0, 1, 1, 0, -1, NOW(), -1, NOW(), 1, 1, 1, 0, 0, 180, 1), (-1383500007, 'COMMENT.REACTION.REMOVED', 'Comment Reaction Removed', 1, 1, 0, 1, -1, 0, 0, '', -1383500001, 0, 1, 1, 0, -1, NOW(), -1, NOW(), 1, 1, 1, 0, 0, 180, 1) ) AS v(EVENTTYPEID, EVENTTYPECODE, EVENTTYPENAME, TYPE, EVENTTYPENATURE, SECURITYLEVEL, CRITICALITY, LINKEDFORMID, NOTIFICATION, ALERT, SECTION, ENTITYID, SORTORDER, STATUS, SOURCETYPE, VERSION, CREATEDBYID, CREATEDON, MODIFIEDBYID, MODIFIEDON, EVENTCATEGORY, ISBIZDOMAIN, ISAUDIT, ISTELEMETRY, ISCOMPLIANCE, RETENTIONDAYS, STORAGETIER) WHERE NOT EXISTS (SELECT 1 FROM MEVENTTYPE WHERE MEVENTTYPE.EVENTTYPEID = v.EVENTTYPEID); -- ============================================================================= -- END OF MIGRATION 20260712_Communication_Phase1_Schema (PostgreSQL variant) -- =============================================================================