-- ============================================================================= -- PIE — Product Intelligence Engine -- Migration: 001 — Initial Schema -- -- Conventions: CLAUDE-DB-SCHEMA.md adapted for PostgreSQL -- • Table names: UPPERCASE, M/T/L/S prefix, no underscores (pie schema separates namespace) -- • Column names: UPPERCASE, no underscores -- • PKs: UUID (app-generated) — NOTE: deviates from CLAUDE-DB-SCHEMA.md INT rule. -- INT migration pending once AutoNumber constants are added for PIE entities. -- • Standard fields on M/T tables: VERSION, STATUS, SORTORDER, CREATEDBYID (INT), -- CREATEDON, MODIFIEDBYID (INT), MODIFIEDON, SOURCETYPE, TENANTID (INT) -- • BOOLEAN → SMALLINT (0/1); TIMESTAMPTZ → TIMESTAMP; TINYINT → SMALLINT -- • Named constraints: PK_, FK_, UK_, CK_ -- • No GO terminators (PostgreSQL uses semicolons) -- -- STATUS values (standard): 0=Pending, 1=Active, 2=Deleted, 3=Amended, 4=Inactive, 5=Archived -- MPIEPROFILE STATUS (PIE-specific): 0=Pending, 1=Draft, 2=Active/Published, 3=Deprecated -- SOURCETYPE values: 1=Framework, 2=Devadmin, 3=Impadmin, 4=Admin, 5=User -- ============================================================================= CREATE SCHEMA IF NOT EXISTS pie; -- ============================================================================= -- MPIEMODULE — Product/module master (e.g. MM, QMS, PIE) -- ============================================================================= CREATE TABLE pie.MPIEMODULE ( -- PK PIEMODULEID UUID NOT NULL, -- BUSINESS COLUMNS PIEMODULECODE VARCHAR(20) NOT NULL, PIEMODULENAME VARCHAR(200) NOT NULL, PIEMODULEGROUP VARCHAR(50) NOT NULL, DESCRIPTION VARCHAR(1000), ICONKEY VARCHAR(50), ISSTANDALONE SMALLINT NOT NULL DEFAULT 0, -- 0 No, 1 Yes CMSCONTENTREF VARCHAR(200), -- STANDARD FIELDS VERSION SMALLINT NOT NULL DEFAULT 0, STATUS SMALLINT NOT NULL DEFAULT 1, SORTORDER SMALLINT NOT NULL DEFAULT 9999, CREATEDBYID INT NOT NULL DEFAULT -1, CREATEDON TIMESTAMP NOT NULL DEFAULT NOW(), MODIFIEDBYID INT NOT NULL DEFAULT -1, MODIFIEDON TIMESTAMP NOT NULL DEFAULT NOW(), SOURCETYPE SMALLINT NOT NULL DEFAULT 5, TENANTID INT NOT NULL DEFAULT -1, CONSTRAINT PK_MPIEMODULE_PIEMODULEID PRIMARY KEY (PIEMODULEID), CONSTRAINT UK_MPIEMODULE_CODE_TENANT UNIQUE (PIEMODULECODE, TENANTID), CONSTRAINT UK_MPIEMODULE_NAME_TENANT UNIQUE (PIEMODULENAME, TENANTID), CONSTRAINT CK_MPIEMODULE_ISSTANDALONE CHECK (ISSTANDALONE IN (0, 1)), CONSTRAINT CK_MPIEMODULE_STATUS CHECK (STATUS IN (0, 1, 2, 3, 4, 5)), CONSTRAINT CK_MPIEMODULE_SOURCETYPE CHECK (SOURCETYPE IN (1, 2, 3, 4, 5)) ); -- ============================================================================= -- MPIEMODULEDEPENDENCY — Module prerequisite graph (detail of MPIEMODULE) -- ============================================================================= CREATE TABLE pie.MPIEMODULEDEPENDENCY ( PIECONFIGDEPENDENCYID UUID NOT NULL, PIEMODULEID UUID NOT NULL, DEPENDSONMODULEID UUID NOT NULL, DEPENDENCYTYPE SMALLINT NOT NULL DEFAULT 1, -- 1 Required, 2 Recommended TENANTID INT NOT NULL DEFAULT -1, CONSTRAINT PK_MPIEMODULEDEPENDENCY_PIECONFIGDEPENDENCYID PRIMARY KEY (PIECONFIGDEPENDENCYID), CONSTRAINT FK_MPIEMODULEDEPENDENCY_PIEMODULEID FOREIGN KEY (PIEMODULEID) REFERENCES pie.MPIEMODULE(PIEMODULEID), CONSTRAINT FK_MPIEMODULEDEPENDENCY_DEPENDSONMODULEID FOREIGN KEY (DEPENDSONMODULEID) REFERENCES pie.MPIEMODULE(PIEMODULEID), CONSTRAINT CK_MPIEMODULEDEPENDENCY_DEPENDENCYTYPE CHECK (DEPENDENCYTYPE IN (1, 2)), CONSTRAINT CK_MPIEMODULEDEPENDENCY_NOSELFREF CHECK (PIEMODULEID <> DEPENDSONMODULEID) ); -- ============================================================================= -- MPIECONFIGITEM — Configuration item master -- ============================================================================= CREATE TABLE pie.MPIECONFIGITEM ( PIECONFIGITEMID UUID NOT NULL, PIEMODULEID UUID NOT NULL, PIECONFIGITEMCODE VARCHAR(20) NOT NULL, PIECONFIGITEMNAME VARCHAR(200) NOT NULL, ITEMCATEGORY VARCHAR(50) NOT NULL, DESCRIPTION VARCHAR(1000), ISMANDATORY SMALLINT NOT NULL DEFAULT 1, -- 0 No, 1 Yes DEFAULTVALUE VARCHAR(200), ESTIMATEDHOURS NUMERIC(9, 4) NOT NULL DEFAULT 0, CMSCONTENTREF VARCHAR(200), VERSION SMALLINT NOT NULL DEFAULT 0, STATUS SMALLINT NOT NULL DEFAULT 1, SORTORDER SMALLINT NOT NULL DEFAULT 9999, CREATEDBYID INT NOT NULL DEFAULT -1, CREATEDON TIMESTAMP NOT NULL DEFAULT NOW(), MODIFIEDBYID INT NOT NULL DEFAULT -1, MODIFIEDON TIMESTAMP NOT NULL DEFAULT NOW(), SOURCETYPE SMALLINT NOT NULL DEFAULT 5, TENANTID INT NOT NULL DEFAULT -1, CONSTRAINT PK_MPIECONFIGITEM_PIECONFIGITEMID PRIMARY KEY (PIECONFIGITEMID), CONSTRAINT UK_MPIECONFIGITEM_CODE_MODULE_TENANT UNIQUE (PIEMODULEID, PIECONFIGITEMCODE, TENANTID), CONSTRAINT FK_MPIECONFIGITEM_PIEMODULEID FOREIGN KEY (PIEMODULEID) REFERENCES pie.MPIEMODULE(PIEMODULEID), CONSTRAINT CK_MPIECONFIGITEM_ISMANDATORY CHECK (ISMANDATORY IN (0, 1)), CONSTRAINT CK_MPIECONFIGITEM_STATUS CHECK (STATUS IN (0, 1, 2, 3, 4, 5)), CONSTRAINT CK_MPIECONFIGITEM_SOURCETYPE CHECK (SOURCETYPE IN (1, 2, 3, 4, 5)) ); -- ============================================================================= -- MPIEVARIANT — Configuration variant master -- ============================================================================= CREATE TABLE pie.MPIEVARIANT ( PIEVARIANTID UUID NOT NULL, PIEMODULEID UUID NOT NULL, PIEVARIANTCODE VARCHAR(20) NOT NULL, PIEVARIANTNAME VARCHAR(200) NOT NULL, DESCRIPTION VARCHAR(1000), PLAINLANGUAGEDESC VARCHAR(1000), ISDEFAULT SMALLINT NOT NULL DEFAULT 0, -- 0 No, 1 Yes CMSCONTENTREF VARCHAR(200), VERSION SMALLINT NOT NULL DEFAULT 0, STATUS SMALLINT NOT NULL DEFAULT 1, SORTORDER SMALLINT NOT NULL DEFAULT 9999, CREATEDBYID INT NOT NULL DEFAULT -1, CREATEDON TIMESTAMP NOT NULL DEFAULT NOW(), MODIFIEDBYID INT NOT NULL DEFAULT -1, MODIFIEDON TIMESTAMP NOT NULL DEFAULT NOW(), SOURCETYPE SMALLINT NOT NULL DEFAULT 5, TENANTID INT NOT NULL DEFAULT -1, CONSTRAINT PK_MPIEVARIANT_PIEVARIANTID PRIMARY KEY (PIEVARIANTID), CONSTRAINT UK_MPIEVARIANT_CODE_MODULE_TENANT UNIQUE (PIEMODULEID, PIEVARIANTCODE, TENANTID), CONSTRAINT FK_MPIEVARIANT_PIEMODULEID FOREIGN KEY (PIEMODULEID) REFERENCES pie.MPIEMODULE(PIEMODULEID), CONSTRAINT CK_MPIEVARIANT_ISDEFAULT CHECK (ISDEFAULT IN (0, 1)), CONSTRAINT CK_MPIEVARIANT_STATUS CHECK (STATUS IN (0, 1, 2, 3, 4, 5)), CONSTRAINT CK_MPIEVARIANT_SOURCETYPE CHECK (SOURCETYPE IN (1, 2, 3, 4, 5)) ); -- ============================================================================= -- MPIEVARIANTIMPLICATION — Implication rules for variants (detail of MPIEVARIANT) -- ============================================================================= CREATE TABLE pie.MPIEVARIANTIMPLICATION ( PIEVARIANTIMPLICATIONID UUID NOT NULL, PIEVARIANTID UUID NOT NULL, IMPLICATIONTYPE SMALLINT NOT NULL, -- 1 AddConfigItem, 2 RemoveConfigItem, 3 AddTrainingTopic, 4 AddUATScenario, 5 AddMigrationObject, 6 UpdateEffortDelta REFID UUID, REFCODE VARCHAR(20), EFFORTDELTAHOURS NUMERIC(9, 4) NOT NULL DEFAULT 0, DESCRIPTION VARCHAR(500), RISKNOTE VARCHAR(500), TENANTID INT NOT NULL DEFAULT -1, CONSTRAINT PK_MPIEVARIANTIMPLICATION_PIEVARIANTIMPLICATIONID PRIMARY KEY (PIEVARIANTIMPLICATIONID), CONSTRAINT FK_MPIEVARIANTIMPLICATION_PIEVARIANTID FOREIGN KEY (PIEVARIANTID) REFERENCES pie.MPIEVARIANT(PIEVARIANTID), CONSTRAINT CK_MPIEVARIANTIMPLICATION_IMPLICATIONTYPE CHECK (IMPLICATIONTYPE IN (1, 2, 3, 4, 5, 6)), CONSTRAINT CK_MPIEVARIANTIMPLICATION_REFID_REQUIRED CHECK (IMPLICATIONTYPE = 6 OR REFID IS NOT NULL) ); -- ============================================================================= -- MPIETRAININGTOPIC — Training topic master -- ============================================================================= CREATE TABLE pie.MPIETRAININGTOPIC ( PIETRAININGTOPICID UUID NOT NULL, PIEMODULEID UUID NOT NULL, TOPICCODE VARCHAR(20) NOT NULL, TOPICNAME VARCHAR(200) NOT NULL, MENUPATH VARCHAR(500), TOPICCATEGORY VARCHAR(50), ESTIMATEDMINUTES INT NOT NULL DEFAULT 60, SEQUENCEORDER SMALLINT NOT NULL DEFAULT 1, ISMANDATORY SMALLINT NOT NULL DEFAULT 1, CMSCONTENTREF VARCHAR(200), VERSION SMALLINT NOT NULL DEFAULT 0, STATUS SMALLINT NOT NULL DEFAULT 1, SORTORDER SMALLINT NOT NULL DEFAULT 9999, CREATEDBYID INT NOT NULL DEFAULT -1, CREATEDON TIMESTAMP NOT NULL DEFAULT NOW(), MODIFIEDBYID INT NOT NULL DEFAULT -1, MODIFIEDON TIMESTAMP NOT NULL DEFAULT NOW(), SOURCETYPE SMALLINT NOT NULL DEFAULT 5, TENANTID INT NOT NULL DEFAULT -1, CONSTRAINT PK_MPIETRAININGTOPIC_PIETRAININGTOPICID PRIMARY KEY (PIETRAININGTOPICID), CONSTRAINT UK_MPIETRAININGTOPIC_CODE_MODULE_TENANT UNIQUE (PIEMODULEID, TOPICCODE, TENANTID), CONSTRAINT FK_MPIETRAININGTOPIC_PIEMODULEID FOREIGN KEY (PIEMODULEID) REFERENCES pie.MPIEMODULE(PIEMODULEID), CONSTRAINT CK_MPIETRAININGTOPIC_ISMANDATORY CHECK (ISMANDATORY IN (0, 1)), CONSTRAINT CK_MPIETRAININGTOPIC_STATUS CHECK (STATUS IN (0, 1, 2, 3, 4, 5)), CONSTRAINT CK_MPIETRAININGTOPIC_SOURCETYPE CHECK (SOURCETYPE IN (1, 2, 3, 4, 5)) ); -- ============================================================================= -- MPIEUATSCENARIO — UAT scenario master -- ============================================================================= CREATE TABLE pie.MPIEUATSCENARIO ( PIEUATSCENARIOID UUID NOT NULL, PIEMODULEID UUID NOT NULL, SCENARIOCODE VARCHAR(20) NOT NULL, SCENARIOTITLE VARCHAR(200) NOT NULL, TRANSACTIONTYPE VARCHAR(50), PRECONDITIONS VARCHAR(1000), STEPS TEXT NOT NULL DEFAULT '[]', EXPECTEDRESULT VARCHAR(1000) NOT NULL, ESTIMATEDMINUTES INT NOT NULL DEFAULT 30, COMPLEXITY SMALLINT NOT NULL DEFAULT 2, -- 1 Simple, 2 Medium, 3 Complex ISMANDATORY SMALLINT NOT NULL DEFAULT 1, CMSCONTENTREF VARCHAR(200), VERSION SMALLINT NOT NULL DEFAULT 0, STATUS SMALLINT NOT NULL DEFAULT 1, SORTORDER SMALLINT NOT NULL DEFAULT 9999, CREATEDBYID INT NOT NULL DEFAULT -1, CREATEDON TIMESTAMP NOT NULL DEFAULT NOW(), MODIFIEDBYID INT NOT NULL DEFAULT -1, MODIFIEDON TIMESTAMP NOT NULL DEFAULT NOW(), SOURCETYPE SMALLINT NOT NULL DEFAULT 5, TENANTID INT NOT NULL DEFAULT -1, CONSTRAINT PK_MPIEUATSCENARIO_PIEUATSCENARIOID PRIMARY KEY (PIEUATSCENARIOID), CONSTRAINT UK_MPIEUATSCENARIO_CODE_MODULE_TENANT UNIQUE (PIEMODULEID, SCENARIOCODE, TENANTID), CONSTRAINT FK_MPIEUATSCENARIO_PIEMODULEID FOREIGN KEY (PIEMODULEID) REFERENCES pie.MPIEMODULE(PIEMODULEID), CONSTRAINT CK_MPIEUATSCENARIO_COMPLEXITY CHECK (COMPLEXITY IN (1, 2, 3)), CONSTRAINT CK_MPIEUATSCENARIO_ISMANDATORY CHECK (ISMANDATORY IN (0, 1)), CONSTRAINT CK_MPIEUATSCENARIO_STATUS CHECK (STATUS IN (0, 1, 2, 3, 4, 5)), CONSTRAINT CK_MPIEUATSCENARIO_SOURCETYPE CHECK (SOURCETYPE IN (1, 2, 3, 4, 5)) ); -- ============================================================================= -- MPIEMIGRATIONOBJECT — Data migration object master -- ============================================================================= CREATE TABLE pie.MPIEMIGRATIONOBJECT ( PIEMIGRATIONOBJECTID UUID NOT NULL, PIEMODULEID UUID NOT NULL, OBJECTCODE VARCHAR(20) NOT NULL, OBJECTNAME VARCHAR(200) NOT NULL, DATACATEGORY VARCHAR(50) NOT NULL, DESCRIPTION VARCHAR(1000), TEMPLATEREF VARCHAR(200), ISMANDATORY SMALLINT NOT NULL DEFAULT 1, SEQUENCEORDER SMALLINT NOT NULL DEFAULT 1, VERSION SMALLINT NOT NULL DEFAULT 0, STATUS SMALLINT NOT NULL DEFAULT 1, SORTORDER SMALLINT NOT NULL DEFAULT 9999, CREATEDBYID INT NOT NULL DEFAULT -1, CREATEDON TIMESTAMP NOT NULL DEFAULT NOW(), MODIFIEDBYID INT NOT NULL DEFAULT -1, MODIFIEDON TIMESTAMP NOT NULL DEFAULT NOW(), SOURCETYPE SMALLINT NOT NULL DEFAULT 5, TENANTID INT NOT NULL DEFAULT -1, CONSTRAINT PK_MPIEMIGRATIONOBJECT_PIEMIGRATIONOBJECTID PRIMARY KEY (PIEMIGRATIONOBJECTID), CONSTRAINT UK_MPIEMIGRATIONOBJECT_CODE_MODULE_TENANT UNIQUE (PIEMODULEID, OBJECTCODE, TENANTID), CONSTRAINT FK_MPIEMIGRATIONOBJECT_PIEMODULEID FOREIGN KEY (PIEMODULEID) REFERENCES pie.MPIEMODULE(PIEMODULEID), CONSTRAINT CK_MPIEMIGRATIONOBJECT_ISMANDATORY CHECK (ISMANDATORY IN (0, 1)), CONSTRAINT CK_MPIEMIGRATIONOBJECT_STATUS CHECK (STATUS IN (0, 1, 2, 3, 4, 5)), CONSTRAINT CK_MPIEMIGRATIONOBJECT_SOURCETYPE CHECK (SOURCETYPE IN (1, 2, 3, 4, 5)) ); -- ============================================================================= -- MPIEESTIMATIONRULE — Effort estimation rule master -- One row per module + engagement_type + complexity_level combination. -- ============================================================================= CREATE TABLE pie.MPIEESTIMATIONRULE ( PIEESTIMATIONRULEID UUID NOT NULL, PIEMODULEID UUID NOT NULL, ENGAGEMENTTYPE SMALLINT NOT NULL, -- engagement classification (enum from BLL) COMPLEXITYLEVEL SMALLINT NOT NULL, -- 1 Low, 2 Medium, 3 High BASEHOURS NUMERIC(9, 4) NOT NULL DEFAULT 0, CONFIGHOURS NUMERIC(9, 4) NOT NULL DEFAULT 0, TRAININGHOURS NUMERIC(9, 4) NOT NULL DEFAULT 0, MIGRATIONHOURS NUMERIC(9, 4) NOT NULL DEFAULT 0, UATHOURS NUMERIC(9, 4) NOT NULL DEFAULT 0, PMOVERHEADPCT NUMERIC(5, 2) NOT NULL DEFAULT 0, CONFIDENCESCORE NUMERIC(5, 4) NOT NULL DEFAULT 1, -- 0.00–1.00 VALIDFROM DATE NOT NULL DEFAULT CURRENT_DATE, VALIDTO DATE, VERSION SMALLINT NOT NULL DEFAULT 0, STATUS SMALLINT NOT NULL DEFAULT 1, SORTORDER SMALLINT NOT NULL DEFAULT 9999, CREATEDBYID INT NOT NULL DEFAULT -1, CREATEDON TIMESTAMP NOT NULL DEFAULT NOW(), MODIFIEDBYID INT NOT NULL DEFAULT -1, MODIFIEDON TIMESTAMP NOT NULL DEFAULT NOW(), SOURCETYPE SMALLINT NOT NULL DEFAULT 5, TENANTID INT NOT NULL DEFAULT -1, CONSTRAINT PK_MPIEESTIMATIONRULE_PIEESTIMATIONRULEID PRIMARY KEY (PIEESTIMATIONRULEID), CONSTRAINT UK_MPIEESTIMATIONRULE_MODULE_TYPE_COMPLEXITY_TENANT UNIQUE (PIEMODULEID, ENGAGEMENTTYPE, COMPLEXITYLEVEL, TENANTID), CONSTRAINT FK_MPIEESTIMATIONRULE_PIEMODULEID FOREIGN KEY (PIEMODULEID) REFERENCES pie.MPIEMODULE(PIEMODULEID), CONSTRAINT CK_MPIEESTIMATIONRULE_COMPLEXITYLEVEL CHECK (COMPLEXITYLEVEL IN (1, 2, 3)), CONSTRAINT CK_MPIEESTIMATIONRULE_STATUS CHECK (STATUS IN (0, 1, 2, 3, 4, 5)), CONSTRAINT CK_MPIEESTIMATIONRULE_SOURCETYPE CHECK (SOURCETYPE IN (1, 2, 3, 4, 5)) ); -- ============================================================================= -- LPIEESTIMATIONSNAPSHOT — Engagement effort snapshot (Log — append-only) -- NOTE: Uses UUID PK consistent with all other PIE tables (deviation from L-table BIGINT IDENTITY rule). -- ============================================================================= CREATE TABLE pie.LPIEESTIMATIONSNAPSHOT ( LPIEESTIMATIONSNAPSHOTID UUID NOT NULL, ENGAGEMENTREF VARCHAR(100) NOT NULL, SNAPSHOTDATA TEXT NOT NULL DEFAULT '{}', -- JSON: full calculation breakdown TOTALHOURS NUMERIC(18, 4) NOT NULL DEFAULT 0, CONFIDENCESCORE NUMERIC(5, 4) NOT NULL DEFAULT 1, CREATEDBYID INT NOT NULL DEFAULT -1, CREATEDON TIMESTAMP NOT NULL DEFAULT NOW(), TENANTID INT NOT NULL DEFAULT -1, CONSTRAINT PK_LPIEESTIMATIONSNAPSHOT_LPIEESTIMATIONSNAPSHOTID PRIMARY KEY (LPIEESTIMATIONSNAPSHOTID) ); -- ============================================================================= -- MPIEPROFILE — Implementation profile master (QuickStart) -- STATUS is PIE-specific: 0=Pending, 1=Draft, 2=Active/Published, 3=Deprecated -- ============================================================================= CREATE TABLE pie.MPIEPROFILE ( PIEPROFILEID UUID NOT NULL, PIEPROFILECODE VARCHAR(20) NOT NULL, PIEPROFILENAME VARCHAR(200) NOT NULL, INDUSTRY VARCHAR(100) NOT NULL, GEOGRAPHY VARCHAR(100) NOT NULL, COMPANYSIZEMIN INT NOT NULL DEFAULT 0, COMPANYSIZEMAX INT, PROFILEVERSION SMALLINT NOT NULL DEFAULT 1, -- PIE profile version number USAGECOUNT INT NOT NULL DEFAULT 0, AVGCOMPLETIONDAYS NUMERIC(9, 2), AVGREWORKRATE NUMERIC(9, 4), PUBLISHEDON TIMESTAMP, PUBLISHEDBYID INT, PREVIOUSVERSIONID UUID, MATCHKEYWORDS TEXT, VERSION SMALLINT NOT NULL DEFAULT 0, STATUS SMALLINT NOT NULL DEFAULT 1, -- 1=Draft, 2=Active, 3=Deprecated SORTORDER SMALLINT NOT NULL DEFAULT 9999, CREATEDBYID INT NOT NULL DEFAULT -1, CREATEDON TIMESTAMP NOT NULL DEFAULT NOW(), MODIFIEDBYID INT NOT NULL DEFAULT -1, MODIFIEDON TIMESTAMP NOT NULL DEFAULT NOW(), SOURCETYPE SMALLINT NOT NULL DEFAULT 5, TENANTID INT NOT NULL DEFAULT -1, CONSTRAINT PK_MPIEPROFILE_PIEPROFILEID PRIMARY KEY (PIEPROFILEID), CONSTRAINT UK_MPIEPROFILE_CODE_VERSION_TENANT UNIQUE (PIEPROFILECODE, PROFILEVERSION, TENANTID), CONSTRAINT CK_MPIEPROFILE_STATUS CHECK (STATUS IN (0, 1, 2, 3, 4, 5)), CONSTRAINT CK_MPIEPROFILE_SOURCETYPE CHECK (SOURCETYPE IN (1, 2, 3, 4, 5)) ); -- ============================================================================= -- MPIEPROFILEMODULE — Modules included in a profile (detail of MPIEPROFILE) -- ============================================================================= CREATE TABLE pie.MPIEPROFILEMODULE ( PIEPROFILEMODULEID UUID NOT NULL, PIEPROFILEID UUID NOT NULL, PIEMODULEID UUID NOT NULL, ISPRIMARY SMALLINT NOT NULL DEFAULT 0, SEQUENCEORDER SMALLINT NOT NULL DEFAULT 1, TENANTID INT NOT NULL DEFAULT -1, CONSTRAINT PK_MPIEPROFILEMODULE_PIEPROFILEMODULEID PRIMARY KEY (PIEPROFILEMODULEID), CONSTRAINT FK_MPIEPROFILEMODULE_PIEPROFILEID FOREIGN KEY (PIEPROFILEID) REFERENCES pie.MPIEPROFILE(PIEPROFILEID), CONSTRAINT FK_MPIEPROFILEMODULE_PIEMODULEID FOREIGN KEY (PIEMODULEID) REFERENCES pie.MPIEMODULE(PIEMODULEID), CONSTRAINT CK_MPIEPROFILEMODULE_ISPRIMARY CHECK (ISPRIMARY IN (0, 1)) ); -- ============================================================================= -- MPIEPROFILEVARIANTDEFAULT — Default variant selections per profile -- ============================================================================= CREATE TABLE pie.MPIEPROFILEVARIANTDEFAULT ( PIEPROFILEVARIANTDEFAULTID UUID NOT NULL, PIEPROFILEID UUID NOT NULL, PIEVARIANTID UUID NOT NULL, TENANTID INT NOT NULL DEFAULT -1, CONSTRAINT PK_MPIEPROFILEVARIANTDEFAULT_PIEPROFILEVARIANTDEFAULTID PRIMARY KEY (PIEPROFILEVARIANTDEFAULTID), CONSTRAINT FK_MPIEPROFILEVARIANTDEFAULT_PIEPROFILEID FOREIGN KEY (PIEPROFILEID) REFERENCES pie.MPIEPROFILE(PIEPROFILEID), CONSTRAINT FK_MPIEPROFILEVARIANTDEFAULT_PIEVARIANTID FOREIGN KEY (PIEVARIANTID) REFERENCES pie.MPIEVARIANT(PIEVARIANTID) ); -- ============================================================================= -- MPIEDEVIATIONQUESTION — Deviation discovery questions (master per profile) -- ============================================================================= CREATE TABLE pie.MPIEDEVIATIONQUESTION ( PIEDEVIATIONQUESTIONID UUID NOT NULL, PIEPROFILEID UUID NOT NULL, QUESTIONCODE VARCHAR(20) NOT NULL, QUESTIONTEXT VARCHAR(1000) NOT NULL, HELPTEXT VARCHAR(500), ANSWERTYPE VARCHAR(20) NOT NULL, -- YesNo | MultiChoice | Numeric OPTIONS TEXT, -- JSON array for MultiChoice SEQUENCEORDER SMALLINT NOT NULL DEFAULT 1, VERSION SMALLINT NOT NULL DEFAULT 0, STATUS SMALLINT NOT NULL DEFAULT 1, SORTORDER SMALLINT NOT NULL DEFAULT 9999, CREATEDBYID INT NOT NULL DEFAULT -1, CREATEDON TIMESTAMP NOT NULL DEFAULT NOW(), MODIFIEDBYID INT NOT NULL DEFAULT -1, MODIFIEDON TIMESTAMP NOT NULL DEFAULT NOW(), SOURCETYPE SMALLINT NOT NULL DEFAULT 5, TENANTID INT NOT NULL DEFAULT -1, CONSTRAINT PK_MPIEDEVIATIONQUESTION_PIEDEVIATIONQUESTIONID PRIMARY KEY (PIEDEVIATIONQUESTIONID), CONSTRAINT UK_MPIEDEVIATIONQUESTION_CODE_PROFILE_TENANT UNIQUE (PIEPROFILEID, QUESTIONCODE, TENANTID), CONSTRAINT FK_MPIEDEVIATIONQUESTION_PIEPROFILEID FOREIGN KEY (PIEPROFILEID) REFERENCES pie.MPIEPROFILE(PIEPROFILEID), CONSTRAINT CK_MPIEDEVIATIONQUESTION_STATUS CHECK (STATUS IN (0, 1, 2, 3, 4, 5)), CONSTRAINT CK_MPIEDEVIATIONQUESTION_SOURCETYPE CHECK (SOURCETYPE IN (1, 2, 3, 4, 5)) ); -- ============================================================================= -- MPIEDEVIATIONIMPLICATION — Implication rules for deviation answers -- ============================================================================= CREATE TABLE pie.MPIEDEVIATIONIMPLICATION ( PIEDEVIATIONIMPLICATIONID UUID NOT NULL, PIEDEVIATIONQUESTIONID UUID NOT NULL, ANSWERVALUE VARCHAR(200) NOT NULL, IMPLICATIONTYPE SMALLINT NOT NULL, -- 1 AddConfigItem, 2 RemoveConfigItem, 3 AddTrainingTopic, 4 AddUATScenario, 5 AddMigrationObject, 6 UpdateEffortDelta REFID UUID, REFCODE VARCHAR(20), EFFORTDELTAHOURS NUMERIC(9, 4) NOT NULL DEFAULT 0, DESCRIPTION VARCHAR(500), TENANTID INT NOT NULL DEFAULT -1, CONSTRAINT PK_MPIEDEVIATIONIMPLICATION_PIEDEVIATIONIMPLICATIONID PRIMARY KEY (PIEDEVIATIONIMPLICATIONID), CONSTRAINT FK_MPIEDEVIATIONIMPLICATION_PIEDEVIATIONQUESTIONID FOREIGN KEY (PIEDEVIATIONQUESTIONID) REFERENCES pie.MPIEDEVIATIONQUESTION(PIEDEVIATIONQUESTIONID), CONSTRAINT CK_MPIEDEVIATIONIMPLICATION_IMPLICATIONTYPE CHECK (IMPLICATIONTYPE IN (1, 2, 3, 4, 5, 6)), CONSTRAINT CK_MPIEDEVIATIONIMPLICATION_REFID_REQUIRED CHECK (IMPLICATIONTYPE = 6 OR REFID IS NOT NULL) ); -- ============================================================================= -- MPIEKPI — KPI master per module -- ============================================================================= CREATE TABLE pie.MPIEKPI ( PIEKPIID UUID NOT NULL, PIEMODULEID UUID NOT NULL, KPICODE VARCHAR(20) NOT NULL, KPINAME VARCHAR(200) NOT NULL, KPICATEGORY SMALLINT NOT NULL DEFAULT 1, -- 1 Efficiency, 2 Accuracy, 3 Adoption, 4 Financial UNIT VARCHAR(50), DESCRIPTION VARCHAR(1000), MEASUREMENTGUIDE VARCHAR(1000), CMSCONTENTREF VARCHAR(200), VERSION SMALLINT NOT NULL DEFAULT 0, STATUS SMALLINT NOT NULL DEFAULT 1, SORTORDER SMALLINT NOT NULL DEFAULT 9999, CREATEDBYID INT NOT NULL DEFAULT -1, CREATEDON TIMESTAMP NOT NULL DEFAULT NOW(), MODIFIEDBYID INT NOT NULL DEFAULT -1, MODIFIEDON TIMESTAMP NOT NULL DEFAULT NOW(), SOURCETYPE SMALLINT NOT NULL DEFAULT 5, TENANTID INT NOT NULL DEFAULT -1, CONSTRAINT PK_MPIEKPI_PIEKPIID PRIMARY KEY (PIEKPIID), CONSTRAINT UK_MPIEKPI_CODE_MODULE_TENANT UNIQUE (PIEMODULEID, KPICODE, TENANTID), CONSTRAINT FK_MPIEKPI_PIEMODULEID FOREIGN KEY (PIEMODULEID) REFERENCES pie.MPIEMODULE(PIEMODULEID), CONSTRAINT CK_MPIEKPI_KPICATEGORY CHECK (KPICATEGORY IN (1, 2, 3, 4)), CONSTRAINT CK_MPIEKPI_STATUS CHECK (STATUS IN (0, 1, 2, 3, 4, 5)), CONSTRAINT CK_MPIEKPI_SOURCETYPE CHECK (SOURCETYPE IN (1, 2, 3, 4, 5)) ); -- ============================================================================= -- MPIEPROFILEKPI — KPIs linked to a profile (detail of MPIEPROFILE) -- ============================================================================= CREATE TABLE pie.MPIEPROFILEKPI ( PIEPROFILEKPIID UUID NOT NULL, PIEPROFILEID UUID NOT NULL, PIEKPIID UUID NOT NULL, ISPRIMARY SMALLINT NOT NULL DEFAULT 0, SEQUENCEORDER SMALLINT NOT NULL DEFAULT 1, TENANTID INT NOT NULL DEFAULT -1, CONSTRAINT PK_MPIEPROFILEKPI_PIEPROFILEKPIID PRIMARY KEY (PIEPROFILEKPIID), CONSTRAINT FK_MPIEPROFILEKPI_PIEPROFILEID FOREIGN KEY (PIEPROFILEID) REFERENCES pie.MPIEPROFILE(PIEPROFILEID), CONSTRAINT FK_MPIEPROFILEKPI_PIEKPIID FOREIGN KEY (PIEKPIID) REFERENCES pie.MPIEKPI(PIEKPIID), CONSTRAINT CK_MPIEPROFILEKPI_ISPRIMARY CHECK (ISPRIMARY IN (0, 1)) ); -- ============================================================================= -- TPIEOUTCOME — Implementation outcome transaction (insert-only) -- Captures go-live and milestone outcome data for PIE learning loop. -- ============================================================================= CREATE TABLE pie.TPIEOUTCOME ( PIEOUTCOMEID UUID NOT NULL, ENGAGEMENTREF VARCHAR(100) NOT NULL, PIEMODULEID UUID NOT NULL, PIEPROFILEID UUID, PROFILEVERSION SMALLINT, ENGAGEMENTTYPE SMALLINT NOT NULL, INDUSTRY VARCHAR(100), COMPANYSIZE INT, GEOGRAPHY VARCHAR(100), ESTIMATEDHOURS NUMERIC(18, 4) NOT NULL DEFAULT 0, ACTUALHOURS NUMERIC(18, 4) NOT NULL DEFAULT 0, REWORKHOURS NUMERIC(18, 4) NOT NULL DEFAULT 0, REWORKCOUNT INT NOT NULL DEFAULT 0, UATPASSRATE NUMERIC(5, 4) NOT NULL DEFAULT 1, GOLIVESUCCESS SMALLINT NOT NULL DEFAULT 1, -- 0 No, 1 Yes COMPLETIONDAYS INT NOT NULL DEFAULT 0, REWORKREASONS TEXT, -- JSON array DEVIATIONMAP TEXT, -- JSON object RECORDEDON TIMESTAMP NOT NULL DEFAULT NOW(), CREATEDBYID INT NOT NULL DEFAULT -1, CREATEDON TIMESTAMP NOT NULL DEFAULT NOW(), TENANTID INT NOT NULL DEFAULT -1, CONSTRAINT PK_TPIEOUTCOME_PIEOUTCOMEID PRIMARY KEY (PIEOUTCOMEID), CONSTRAINT FK_TPIEOUTCOME_PIEMODULEID FOREIGN KEY (PIEMODULEID) REFERENCES pie.MPIEMODULE(PIEMODULEID), CONSTRAINT CK_TPIEOUTCOME_GOLIVESUCCESS CHECK (GOLIVESUCCESS IN (0, 1)) ); -- ============================================================================= -- TPIELEARNINGPROPOSAL — AI-generated gap proposal transaction -- ============================================================================= CREATE TABLE pie.TPIELEARNINGPROPOSAL ( PIELEARNINGPROPOSALID UUID NOT NULL, PIEPROFILEID UUID NOT NULL, PROPOSALTYPE SMALLINT NOT NULL DEFAULT 1, -- 1 AEO, 2 Manual, 3 Scheduled PROPOSALDESCRIPTION VARCHAR(2000), EVIDENCEREFS TEXT, -- JSON array of reference IDs DEVIATIONRATE NUMERIC(5, 4) NOT NULL DEFAULT 0, AICONFIDENCESCORE NUMERIC(5, 4) NOT NULL DEFAULT 0, PROPOSEDCONTENT TEXT, -- JSON: structured proposal payload REVIEWEDBYID INT NOT NULL DEFAULT -1, REVIEWEDON TIMESTAMP, REVIEWERNOTES VARCHAR(1000), VERSION SMALLINT NOT NULL DEFAULT 0, STATUS SMALLINT NOT NULL DEFAULT 0, -- 0 Pending, 1 Approved, 2 Rejected SORTORDER SMALLINT NOT NULL DEFAULT 9999, CREATEDBYID INT NOT NULL DEFAULT -1, CREATEDON TIMESTAMP NOT NULL DEFAULT NOW(), MODIFIEDBYID INT NOT NULL DEFAULT -1, MODIFIEDON TIMESTAMP NOT NULL DEFAULT NOW(), SOURCETYPE SMALLINT NOT NULL DEFAULT 5, TENANTID INT NOT NULL DEFAULT -1, CONSTRAINT PK_TPIELEARNINGPROPOSAL_PIELEARNINGPROPOSALID PRIMARY KEY (PIELEARNINGPROPOSALID), CONSTRAINT FK_TPIELEARNINGPROPOSAL_PIEPROFILEID FOREIGN KEY (PIEPROFILEID) REFERENCES pie.MPIEPROFILE(PIEPROFILEID), CONSTRAINT CK_TPIELEARNINGPROPOSAL_PROPOSALTYPE CHECK (PROPOSALTYPE IN (1, 2, 3)), CONSTRAINT CK_TPIELEARNINGPROPOSAL_STATUS CHECK (STATUS IN (0, 1, 2, 3, 4, 5)), CONSTRAINT CK_TPIELEARNINGPROPOSAL_SOURCETYPE CHECK (SOURCETYPE IN (1, 2, 3, 4, 5)) ); -- ============================================================================= -- INDEXES -- ============================================================================= CREATE INDEX IX_MPIEMODULE_TENANTID ON pie.MPIEMODULE(TENANTID); CREATE INDEX IX_MPIEMODULE_GROUP_STATUS ON pie.MPIEMODULE(TENANTID, PIEMODULEGROUP, STATUS); CREATE INDEX IX_MPIEMODULEDEPENDENCY_PIEMODULEID ON pie.MPIEMODULEDEPENDENCY(PIEMODULEID, TENANTID); CREATE INDEX IX_MPIECONFIGITEM_TENANTID ON pie.MPIECONFIGITEM(TENANTID); CREATE INDEX IX_MPIECONFIGITEM_PIEMODULEID ON pie.MPIECONFIGITEM(PIEMODULEID, TENANTID); CREATE INDEX IX_MPIEVARIANT_TENANTID ON pie.MPIEVARIANT(TENANTID); CREATE INDEX IX_MPIEVARIANT_PIEMODULEID ON pie.MPIEVARIANT(PIEMODULEID, TENANTID); CREATE INDEX IX_MPIEVARIANTIMPLICATION_VARIANTID ON pie.MPIEVARIANTIMPLICATION(PIEVARIANTID, TENANTID); CREATE INDEX IX_MPIETRAININGTOPIC_TENANTID ON pie.MPIETRAININGTOPIC(TENANTID); CREATE INDEX IX_MPIETRAININGTOPIC_PIEMODULEID ON pie.MPIETRAININGTOPIC(PIEMODULEID, TENANTID); CREATE INDEX IX_MPIEUATSCENARIO_TENANTID ON pie.MPIEUATSCENARIO(TENANTID); CREATE INDEX IX_MPIEUATSCENARIO_PIEMODULEID ON pie.MPIEUATSCENARIO(PIEMODULEID, TENANTID); CREATE INDEX IX_MPIEMIGRATIONOBJECT_TENANTID ON pie.MPIEMIGRATIONOBJECT(TENANTID); CREATE INDEX IX_MPIEMIGRATIONOBJECT_PIEMODULEID ON pie.MPIEMIGRATIONOBJECT(PIEMODULEID, TENANTID); CREATE INDEX IX_MPIEESTIMATIONRULE_TENANTID ON pie.MPIEESTIMATIONRULE(TENANTID); CREATE INDEX IX_MPIEESTIMATIONRULE_MODULE_TYPE ON pie.MPIEESTIMATIONRULE(PIEMODULEID, ENGAGEMENTTYPE, COMPLEXITYLEVEL, TENANTID) WHERE STATUS = 1; CREATE INDEX IX_LPIEESTIMATIONSNAPSHOT_ENGREF ON pie.LPIEESTIMATIONSNAPSHOT(ENGAGEMENTREF, TENANTID); CREATE INDEX IX_MPIEPROFILE_TENANTID ON pie.MPIEPROFILE(TENANTID); CREATE INDEX IX_MPIEPROFILE_MATCH ON pie.MPIEPROFILE(TENANTID, STATUS, INDUSTRY, GEOGRAPHY, COMPANYSIZEMIN) WHERE STATUS = 2; CREATE INDEX IX_MPIEPROFILEMODULE_PIEPROFILEID ON pie.MPIEPROFILEMODULE(PIEPROFILEID, TENANTID); CREATE INDEX IX_MPIEDEVIATIONQUESTION_TENANTID ON pie.MPIEDEVIATIONQUESTION(TENANTID); CREATE INDEX IX_MPIEDEVIATIONQUESTION_PROFILEID ON pie.MPIEDEVIATIONQUESTION(PIEPROFILEID, TENANTID); CREATE INDEX IX_MPIEDEVIATIONIMPLICATION_QSTID ON pie.MPIEDEVIATIONIMPLICATION(PIEDEVIATIONQUESTIONID, TENANTID); CREATE INDEX IX_MPIEKPI_TENANTID ON pie.MPIEKPI(TENANTID); CREATE INDEX IX_MPIEKPI_PIEMODULEID ON pie.MPIEKPI(PIEMODULEID, TENANTID); CREATE INDEX IX_TPIEOUTCOME_ENGAGEMENTREF ON pie.TPIEOUTCOME(ENGAGEMENTREF, TENANTID); CREATE INDEX IX_TPIEOUTCOME_PIEMODULEID ON pie.TPIEOUTCOME(PIEMODULEID, RECORDEDON DESC); CREATE INDEX IX_TPIEOUTCOME_PIEPROFILEID ON pie.TPIEOUTCOME(PIEPROFILEID, RECORDEDON DESC) WHERE PIEPROFILEID IS NOT NULL; CREATE INDEX IX_TPIELEARNINGPROPOSAL_ENGREF ON pie.TPIELEARNINGPROPOSAL(TENANTID, STATUS); CREATE INDEX IX_TPIELEARNINGPROPOSAL_PROFILEID ON pie.TPIELEARNINGPROPOSAL(PIEPROFILEID, STATUS);