FM / docs

/home/techb158/trellopowerup.abdallabala.com/docs/

/home/techb158/trellopowerup.abdallabala.com/docs

Name	Size	Mode	Actions
00-design-study.md	3678	0644	edit dl rm
01-uml-class-diagram.puml	11869	0644	edit dl rm
02-use-case-diagram.puml	3763	0644	edit dl rm
03-sequence-diagrams.puml	9732	0644	edit dl rm
04-database-entity-model.mmd	7051	0644	edit dl rm
05-database-schema.sql	13372	0644	edit dl rm
06-diagram-preview.html	5021	0644	edit dl rm
07-design-checklist.md	3256	0644	edit dl rm
08-step-2-storage-layer.md	5325	0644	edit dl rm
09-step-3-risk-crud-ui.md	3380	0644	edit dl rm
10-step-4-mitigation-workflow.md	3996	0644	edit dl rm
11-step-5-deployment-gate-workflow.md	2292	0644	edit dl rm
12-step-6-multi-pm-integration.md	3770	0644	edit dl rm
13-step-6-1-microsoft-planner-integration.md	2335	0644	edit dl rm
14-step-7-reporting-export.md	4158	0644	edit dl rm
15-step-7-1-oauth-live-connectors.md	4545	0644	edit dl rm
16-step-8-user-roles-access-control.md	3297	0644	edit dl rm
17-step-9-production-deployment-security.md	4228	0644	edit dl rm
18-step-10-final-academic-submission.md	3199	0644	edit dl rm
19-final-report-draft.md	6814	0644	edit dl rm
20-instructor-submission-checklist.md	3639	0644	edit dl rm
21-demo-script.md	3948	0644	edit dl rm
22-traceability-matrix.md	4847	0644	edit dl rm
23-testing-evidence.md	2961	0644	edit dl rm
24-evaluation-rubric-mapping.md	2910	0644	edit dl rm
25-final-deployment-runbook.md	3214	0644	edit dl rm
26-known-limitations-and-future-work.md	2632	0644	edit dl rm
27-final-qa-checklist.md	2893	0644	edit dl rm
28-demo-rehearsal-script.md	3618	0644	edit dl rm
29-submission-freeze-report.md	2769	0644	edit dl rm
30-final-known-issues.md	1876	0644	edit dl rm
31-trello-powerup-implementation-guide.md	7608	0644	edit dl rm
32-vps-deployment-guide.md	3697	0644	edit dl rm
dashboard-spec.md	3691	0644	edit dl rm
methodology-mapping.md	2471	0644	edit dl rm
next-implementation-step.md	1304	0644	edit dl rm
trello-admin-setup.md	2175	0644	edit dl rm

Edit: /home/techb158/trellopowerup.abdallabala.com/docs/05-database-schema.sql (13372B)

-- COSMIC AI-Risk Dashboard, relational database schema draft
-- Target: PostgreSQL compatible. SQLite can use this with minor type adjustments.

CREATE TABLE roles (
  id TEXT PRIMARY KEY,
  name TEXT NOT NULL UNIQUE,
  permissions_json TEXT NOT NULL DEFAULT '[]'
);

CREATE TABLE users (
  id TEXT PRIMARY KEY,
  display_name TEXT NOT NULL,
  email TEXT NOT NULL UNIQUE,
  role_id TEXT NOT NULL REFERENCES roles(id),
  active BOOLEAN NOT NULL DEFAULT TRUE,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE projects (
  id TEXT PRIMARY KEY,
  name TEXT NOT NULL,
  subtitle TEXT,
  project_type TEXT NOT NULL CHECK (project_type IN ('Incremental','Disruptive','Applied Research','AI-Enabler','Citizen-Led')),
  current_lifecycle_phase TEXT,
  risk_appetite NUMERIC NOT NULL DEFAULT 50,
  assessment_date DATE,
  status TEXT NOT NULL DEFAULT 'Active',
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE lifecycle_phases (
  id TEXT PRIMARY KEY,
  project_id TEXT NOT NULL REFERENCES projects(id) ON DELETE CASCADE,
  name TEXT NOT NULL,
  status TEXT NOT NULL CHECK (status IN ('Not started','In progress','Warning','Blocked','Complete')),
  readiness_score NUMERIC NOT NULL CHECK (readiness_score >= 0 AND readiness_score <= 100),
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE(project_id, name)
);

CREATE TABLE risks (
  id TEXT PRIMARY KEY,
  project_id TEXT NOT NULL REFERENCES projects(id) ON DELETE CASCADE,
  owner_user_id TEXT REFERENCES users(id),
  title TEXT NOT NULL,
  description TEXT,
  dimension TEXT NOT NULL CHECK (dimension IN ('Organizational','Technical','Human')),
  domain TEXT NOT NULL CHECK (domain IN ('Strategic and organizational risks','Technical risks','Legal and ethical risks')),
  lifecycle_phase TEXT NOT NULL,
  probability INTEGER NOT NULL CHECK (probability BETWEEN 1 AND 5),
  impact INTEGER NOT NULL CHECK (impact BETWEEN 1 AND 5),
  detectability INTEGER NOT NULL CHECK (detectability BETWEEN 1 AND 5),
  status TEXT NOT NULL CHECK (status IN ('Open','In mitigation','Accepted','Closed')),
  approval_status TEXT NOT NULL CHECK (approval_status IN ('Pending','Approved','Rejected','Accepted')),
  due_date DATE,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE risk_scores (
  id TEXT PRIMARY KEY,
  risk_id TEXT NOT NULL REFERENCES risks(id) ON DELETE CASCADE,
  raw_score NUMERIC NOT NULL,
  normalized_score NUMERIC NOT NULL CHECK (normalized_score >= 0 AND normalized_score <= 100),
  residual_score NUMERIC NOT NULL CHECK (residual_score >= 0 AND residual_score <= 100),
  risk_level TEXT NOT NULL CHECK (risk_level IN ('Low','Medium','High','Critical')),
  calculated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE mitigation_actions (
  id TEXT PRIMARY KEY,
  risk_id TEXT NOT NULL REFERENCES risks(id) ON DELETE CASCADE,
  owner_user_id TEXT REFERENCES users(id),
  title TEXT NOT NULL,
  description TEXT,
  status TEXT NOT NULL CHECK (status IN ('Not started','In progress','Done','Rejected')),
  progress_percent NUMERIC NOT NULL DEFAULT 0 CHECK (progress_percent >= 0 AND progress_percent <= 100),
  effectiveness_percent NUMERIC NOT NULL DEFAULT 0 CHECK (effectiveness_percent >= 0 AND effectiveness_percent <= 100),
  due_date DATE,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE indicators (
  id TEXT PRIMARY KEY,
  project_id TEXT NOT NULL REFERENCES projects(id) ON DELETE CASCADE,
  name TEXT NOT NULL,
  measurand TEXT NOT NULL,
  unit TEXT NOT NULL,
  threshold_min NUMERIC,
  threshold_max NUMERIC,
  interpretation_rule TEXT NOT NULL,
  dimension TEXT CHECK (dimension IN ('Organizational','Technical','Human')),
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE evidence_artifacts (
  id TEXT PRIMARY KEY,
  project_id TEXT NOT NULL REFERENCES projects(id) ON DELETE CASCADE,
  risk_id TEXT REFERENCES risks(id) ON DELETE SET NULL,
  mitigation_id TEXT REFERENCES mitigation_actions(id) ON DELETE SET NULL,
  type TEXT NOT NULL CHECK (type IN ('Document','Metric','Review','Test','Trello Card','Planner Task','URL','Other')),
  title TEXT NOT NULL,
  uri TEXT,
  created_by_user_id TEXT REFERENCES users(id),
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE measurement_records (
  id TEXT PRIMARY KEY,
  project_id TEXT NOT NULL REFERENCES projects(id) ON DELETE CASCADE,
  indicator_id TEXT NOT NULL REFERENCES indicators(id) ON DELETE CASCADE,
  evidence_id TEXT REFERENCES evidence_artifacts(id) ON DELETE SET NULL,
  value_text TEXT,
  numeric_value NUMERIC,
  status TEXT NOT NULL CHECK (status IN ('Pass','Warning','Fail','Pending')),
  measured_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE experiments (
  id TEXT PRIMARY KEY,
  project_id TEXT NOT NULL REFERENCES projects(id) ON DELETE CASCADE,
  name TEXT NOT NULL,
  model_name TEXT NOT NULL,
  dataset_version TEXT NOT NULL,
  selected BOOLEAN NOT NULL DEFAULT FALSE,
  reproducibility_status TEXT NOT NULL CHECK (reproducibility_status IN ('Complete','Partial','Missing')),
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE model_metrics (
  id TEXT PRIMARY KEY,
  experiment_id TEXT NOT NULL REFERENCES experiments(id) ON DELETE CASCADE,
  metric_name TEXT NOT NULL,
  metric_value NUMERIC NOT NULL,
  threshold_value NUMERIC,
  status TEXT NOT NULL CHECK (status IN ('Pass','Warning','Fail')),
  measured_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE deployment_gates (
  id TEXT PRIMARY KEY,
  project_id TEXT NOT NULL REFERENCES projects(id) ON DELETE CASCADE,
  status TEXT NOT NULL CHECK (status IN ('Ready','Warning','Blocked')),
  review_status TEXT NOT NULL DEFAULT 'Pending review' CHECK (review_status IN ('Pending review','Approved','Rejected','Accepted','Needs changes')),
  summary TEXT,
  evaluated_by_user_id TEXT REFERENCES users(id),
  evaluated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  reviewed_by_display_name TEXT,
  reviewed_at TIMESTAMP
);

CREATE TABLE gate_criteria (
  id TEXT PRIMARY KEY,
  gate_id TEXT NOT NULL REFERENCES deployment_gates(id) ON DELETE CASCADE,
  name TEXT NOT NULL,
  actual_value TEXT NOT NULL,
  expected_rule TEXT NOT NULL,
  status TEXT NOT NULL CHECK (status IN ('Pass','Warning','Fail')),
  blocking BOOLEAN NOT NULL DEFAULT FALSE,
  evidence TEXT,
  evidence_title TEXT,
  evidence_uri TEXT,
  reviewer_status TEXT NOT NULL DEFAULT 'Open' CHECK (reviewer_status IN ('Open','Reviewed','Evidence requested','Accepted','Rejected')),
  reviewer_note TEXT
);

CREATE TABLE gate_decisions (
  id TEXT PRIMARY KEY,
  gate_id TEXT NOT NULL REFERENCES deployment_gates(id) ON DELETE CASCADE,
  decision TEXT NOT NULL CHECK (decision IN ('Approved','Rejected','Accepted','Needs changes')),
  reason TEXT NOT NULL,
  approved_by_user_id TEXT REFERENCES users(id),
  reviewer_display_name TEXT,
  approved_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE audit_events (
  id TEXT PRIMARY KEY,
  project_id TEXT NOT NULL REFERENCES projects(id) ON DELETE CASCADE,
  actor_user_id TEXT REFERENCES users(id),
  entity_type TEXT NOT NULL,
  entity_id TEXT NOT NULL,
  action TEXT NOT NULL,
  before_json TEXT,
  after_json TEXT,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE trello_mappings (
  id TEXT PRIMARY KEY,
  project_id TEXT NOT NULL REFERENCES projects(id) ON DELETE CASCADE,
  trello_board_id TEXT,
  trello_list_id TEXT,
  trello_card_id TEXT,
  local_entity_type TEXT NOT NULL,
  local_entity_id TEXT NOT NULL,
  sync_status TEXT NOT NULL CHECK (sync_status IN ('Pending','Synced','Conflict','Failed')),
  last_synced_at TIMESTAMP
);

CREATE TABLE project_management_integrations (
  id TEXT PRIMARY KEY,
  project_id TEXT NOT NULL REFERENCES projects(id) ON DELETE CASCADE,
  provider TEXT NOT NULL CHECK (provider IN ('Trello','Jira','Asana','Microsoft Planner')),
  workspace_name TEXT NOT NULL,
  external_project_key TEXT NOT NULL,
  base_url TEXT,
  auth_mode TEXT NOT NULL,
  connection_status TEXT NOT NULL CHECK (connection_status IN ('Connected','Needs configuration','Disabled')),
  sync_direction TEXT NOT NULL CHECK (sync_direction IN ('COSMIC to PM','PM to COSMIC','Bidirectional')),
  last_sync_at TIMESTAMP,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE(project_id, provider)
);

CREATE TABLE external_work_item_mappings (
  id TEXT PRIMARY KEY,
  project_id TEXT NOT NULL REFERENCES projects(id) ON DELETE CASCADE,
  integration_id TEXT NOT NULL REFERENCES project_management_integrations(id) ON DELETE CASCADE,
  provider TEXT NOT NULL,
  local_entity_type TEXT NOT NULL,
  local_entity_id TEXT NOT NULL,
  local_title TEXT,
  external_item_type TEXT NOT NULL,
  external_item_id TEXT NOT NULL,
  external_item_key TEXT NOT NULL,
  external_url TEXT,
  external_status TEXT,
  sync_status TEXT NOT NULL CHECK (sync_status IN ('Pending','Synced','Conflict','Failed')),
  field_mapping_json TEXT,
  last_synced_at TIMESTAMP,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE(integration_id, local_entity_type, local_entity_id)
);

CREATE TABLE project_management_sync_runs (
  id TEXT PRIMARY KEY,
  project_id TEXT NOT NULL REFERENCES projects(id) ON DELETE CASCADE,
  integration_id TEXT NOT NULL REFERENCES project_management_integrations(id) ON DELETE CASCADE,
  provider TEXT NOT NULL,
  status TEXT NOT NULL CHECK (status IN ('Completed','Completed with errors','Failed')),
  started_at TIMESTAMP NOT NULL,
  finished_at TIMESTAMP NOT NULL,
  created_count INTEGER NOT NULL DEFAULT 0,
  updated_count INTEGER NOT NULL DEFAULT 0,
  failed_count INTEGER NOT NULL DEFAULT 0,
  summary TEXT,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE report_exports (
  id TEXT PRIMARY KEY,
  project_id TEXT NOT NULL REFERENCES projects(id) ON DELETE CASCADE,
  report_type TEXT NOT NULL,
  format TEXT NOT NULL CHECK (format IN ('JSON','CSV','HTML','PDF')),
  generated_by_user_id TEXT REFERENCES users(id),
  generated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  file_name TEXT,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX idx_risks_project_status ON risks(project_id, status);
CREATE INDEX idx_risks_dimension ON risks(project_id, dimension);
CREATE INDEX idx_scores_risk_time ON risk_scores(risk_id, calculated_at DESC);
CREATE INDEX idx_measurements_project_indicator ON measurement_records(project_id, indicator_id);
CREATE INDEX idx_gates_project_time ON deployment_gates(project_id, evaluated_at DESC);
CREATE INDEX idx_trello_card ON trello_mappings(trello_card_id);

CREATE INDEX idx_pm_integrations_project ON project_management_integrations(project_id, provider);
CREATE INDEX idx_external_mappings_project ON external_work_item_mappings(project_id, provider);
CREATE INDEX idx_sync_runs_project_time ON project_management_sync_runs(project_id, started_at DESC);
CREATE INDEX idx_report_exports_project_time ON report_exports(project_id, generated_at DESC);

-- Step 7.1, OAuth and live PM connector extension
CREATE TABLE IF NOT EXISTS oauth_states (
  id TEXT PRIMARY KEY,
  provider TEXT NOT NULL,
  integration_id TEXT,
  project_id TEXT,
  actor_user_id TEXT,
  state TEXT NOT NULL UNIQUE,
  status TEXT NOT NULL,
  expires_at TEXT,
  used_at TEXT,
  created_at TEXT NOT NULL,
  updated_at TEXT NOT NULL
);

CREATE TABLE IF NOT EXISTS oauth_tokens (
  id TEXT PRIMARY KEY,
  provider TEXT NOT NULL,
  integration_id TEXT,
  project_id TEXT,
  token_type TEXT,
  access_token_encrypted TEXT NOT NULL,
  access_token_redacted TEXT,
  refresh_token_encrypted TEXT,
  refresh_token_redacted TEXT,
  expires_at TEXT,
  scope TEXT,
  status TEXT NOT NULL,
  raw_metadata_json TEXT,
  revoked_at TEXT,
  created_at TEXT NOT NULL,
  updated_at TEXT NOT NULL
);

ALTER TABLE project_management_integrations ADD COLUMN live_enabled BOOLEAN DEFAULT FALSE;
ALTER TABLE project_management_integrations ADD COLUMN live_config_json TEXT;
ALTER TABLE project_management_sync_runs ADD COLUMN failure_json TEXT;

-- Step 8 access control: API permissions are stored in roles.permissions_json and resolved through users.role_id.
-- The prototype uses X-Cosmic-User-Id to select the current actor.

-- Step 9 production operations note:
-- Backup manifests are currently stored as JSON files beside database backups.
-- Runtime configuration is provided through environment variables and is intentionally not persisted.
-- A future production database version may add this table if backups are tracked inside the database:
-- CREATE TABLE backup_manifests (
--   id TEXT PRIMARY KEY,
--   actor_user_id TEXT REFERENCES users(id),
--   source_file TEXT NOT NULL,
--   backup_file TEXT NOT NULL,
--   created_at TIMESTAMP NOT NULL,
--   size_bytes INTEGER NOT NULL,
--   sha256 TEXT NOT NULL
-- );

-- COSMIC AI-Risk Dashboard, relational database schema draft -- Target: PostgreSQL compatible. SQLite can use this with minor type adjustments. CREATE TABLE roles ( id TEXT PRIMARY KEY, name TEXT NOT NULL UNIQUE, permissions_json TEXT NOT NULL DEFAULT '[]' ); CREATE TABLE users ( id TEXT PRIMARY KEY, display_name TEXT NOT NULL, email TEXT NOT NULL UNIQUE, role_id TEXT NOT NULL REFERENCES roles(id), active BOOLEAN NOT NULL DEFAULT TRUE, created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ); CREATE TABLE projects ( id TEXT PRIMARY KEY, name TEXT NOT NULL, subtitle TEXT, project_type TEXT NOT NULL CHECK (project_type IN ('Incremental','Disruptive','Applied Research','AI-Enabler','Citizen-Led')), current_lifecycle_phase TEXT, risk_appetite NUMERIC NOT NULL DEFAULT 50, assessment_date DATE, status TEXT NOT NULL DEFAULT 'Active', created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ); CREATE TABLE lifecycle_phases ( id TEXT PRIMARY KEY, project_id TEXT NOT NULL REFERENCES projects(id) ON DELETE CASCADE, name TEXT NOT NULL, status TEXT NOT NULL CHECK (status IN ('Not started','In progress','Warning','Blocked','Complete')), readiness_score NUMERIC NOT NULL CHECK (readiness_score >= 0 AND readiness_score <= 100), updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, UNIQUE(project_id, name) ); CREATE TABLE risks ( id TEXT PRIMARY KEY, project_id TEXT NOT NULL REFERENCES projects(id) ON DELETE CASCADE, owner_user_id TEXT REFERENCES users(id), title TEXT NOT NULL, description TEXT, dimension TEXT NOT NULL CHECK (dimension IN ('Organizational','Technical','Human')), domain TEXT NOT NULL CHECK (domain IN ('Strategic and organizational risks','Technical risks','Legal and ethical risks')), lifecycle_phase TEXT NOT NULL, probability INTEGER NOT NULL CHECK (probability BETWEEN 1 AND 5), impact INTEGER NOT NULL CHECK (impact BETWEEN 1 AND 5), detectability INTEGER NOT NULL CHECK (detectability BETWEEN 1 AND 5), status TEXT NOT NULL CHECK (status IN ('Open','In mitigation','Accepted','Closed')), approval_status TEXT NOT NULL CHECK (approval_status IN ('Pending','Approved','Rejected','Accepted')), due_date DATE, created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ); CREATE TABLE risk_scores ( id TEXT PRIMARY KEY, risk_id TEXT NOT NULL REFERENCES risks(id) ON DELETE CASCADE, raw_score NUMERIC NOT NULL, normalized_score NUMERIC NOT NULL CHECK (normalized_score >= 0 AND normalized_score <= 100), residual_score NUMERIC NOT NULL CHECK (residual_score >= 0 AND residual_score <= 100), risk_level TEXT NOT NULL CHECK (risk_level IN ('Low','Medium','High','Critical')), calculated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ); CREATE TABLE mitigation_actions ( id TEXT PRIMARY KEY, risk_id TEXT NOT NULL REFERENCES risks(id) ON DELETE CASCADE, owner_user_id TEXT REFERENCES users(id), title TEXT NOT NULL, description TEXT, status TEXT NOT NULL CHECK (status IN ('Not started','In progress','Done','Rejected')), progress_percent NUMERIC NOT NULL DEFAULT 0 CHECK (progress_percent >= 0 AND progress_percent <= 100), effectiveness_percent NUMERIC NOT NULL DEFAULT 0 CHECK (effectiveness_percent >= 0 AND effectiveness_percent <= 100), due_date DATE, created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ); CREATE TABLE indicators ( id TEXT PRIMARY KEY, project_id TEXT NOT NULL REFERENCES projects(id) ON DELETE CASCADE, name TEXT NOT NULL, measurand TEXT NOT NULL, unit TEXT NOT NULL, threshold_min NUMERIC, threshold_max NUMERIC, interpretation_rule TEXT NOT NULL, dimension TEXT CHECK (dimension IN ('Organizational','Technical','Human')), created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ); CREATE TABLE evidence_artifacts ( id TEXT PRIMARY KEY, project_id TEXT NOT NULL REFERENCES projects(id) ON DELETE CASCADE, risk_id TEXT REFERENCES risks(id) ON DELETE SET NULL, mitigation_id TEXT REFERENCES mitigation_actions(id) ON DELETE SET NULL, type TEXT NOT NULL CHECK (type IN ('Document','Metric','Review','Test','Trello Card','Planner Task','URL','Other')), title TEXT NOT NULL, uri TEXT, created_by_user_id TEXT REFERENCES users(id), created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ); CREATE TABLE measurement_records ( id TEXT PRIMARY KEY, project_id TEXT NOT NULL REFERENCES projects(id) ON DELETE CASCADE, indicator_id TEXT NOT NULL REFERENCES indicators(id) ON DELETE CASCADE, evidence_id TEXT REFERENCES evidence_artifacts(id) ON DELETE SET NULL, value_text TEXT, numeric_value NUMERIC, status TEXT NOT NULL CHECK (status IN ('Pass','Warning','Fail','Pending')), measured_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ); CREATE TABLE experiments ( id TEXT PRIMARY KEY, project_id TEXT NOT NULL REFERENCES projects(id) ON DELETE CASCADE, name TEXT NOT NULL, model_name TEXT NOT NULL, dataset_version TEXT NOT NULL, selected BOOLEAN NOT NULL DEFAULT FALSE, reproducibility_status TEXT NOT NULL CHECK (reproducibility_status IN ('Complete','Partial','Missing')), created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ); CREATE TABLE model_metrics ( id TEXT PRIMARY KEY, experiment_id TEXT NOT NULL REFERENCES experiments(id) ON DELETE CASCADE, metric_name TEXT NOT NULL, metric_value NUMERIC NOT NULL, threshold_value NUMERIC, status TEXT NOT NULL CHECK (status IN ('Pass','Warning','Fail')), measured_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ); CREATE TABLE deployment_gates ( id TEXT PRIMARY KEY, project_id TEXT NOT NULL REFERENCES projects(id) ON DELETE CASCADE, status TEXT NOT NULL CHECK (status IN ('Ready','Warning','Blocked')), review_status TEXT NOT NULL DEFAULT 'Pending review' CHECK (review_status IN ('Pending review','Approved','Rejected','Accepted','Needs changes')), summary TEXT, evaluated_by_user_id TEXT REFERENCES users(id), evaluated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, reviewed_by_display_name TEXT, reviewed_at TIMESTAMP ); CREATE TABLE gate_criteria ( id TEXT PRIMARY KEY, gate_id TEXT NOT NULL REFERENCES deployment_gates(id) ON DELETE CASCADE, name TEXT NOT NULL, actual_value TEXT NOT NULL, expected_rule TEXT NOT NULL, status TEXT NOT NULL CHECK (status IN ('Pass','Warning','Fail')), blocking BOOLEAN NOT NULL DEFAULT FALSE, evidence TEXT, evidence_title TEXT, evidence_uri TEXT, reviewer_status TEXT NOT NULL DEFAULT 'Open' CHECK (reviewer_status IN ('Open','Reviewed','Evidence requested','Accepted','Rejected')), reviewer_note TEXT ); CREATE TABLE gate_decisions ( id TEXT PRIMARY KEY, gate_id TEXT NOT NULL REFERENCES deployment_gates(id) ON DELETE CASCADE, decision TEXT NOT NULL CHECK (decision IN ('Approved','Rejected','Accepted','Needs changes')), reason TEXT NOT NULL, approved_by_user_id TEXT REFERENCES users(id), reviewer_display_name TEXT, approved_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ); CREATE TABLE audit_events ( id TEXT PRIMARY KEY, project_id TEXT NOT NULL REFERENCES projects(id) ON DELETE CASCADE, actor_user_id TEXT REFERENCES users(id), entity_type TEXT NOT NULL, entity_id TEXT NOT NULL, action TEXT NOT NULL, before_json TEXT, after_json TEXT, created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ); CREATE TABLE trello_mappings ( id TEXT PRIMARY KEY, project_id TEXT NOT NULL REFERENCES projects(id) ON DELETE CASCADE, trello_board_id TEXT, trello_list_id TEXT, trello_card_id TEXT, local_entity_type TEXT NOT NULL, local_entity_id TEXT NOT NULL, sync_status TEXT NOT NULL CHECK (sync_status IN ('Pending','Synced','Conflict','Failed')), last_synced_at TIMESTAMP ); CREATE TABLE project_management_integrations ( id TEXT PRIMARY KEY, project_id TEXT NOT NULL REFERENCES projects(id) ON DELETE CASCADE, provider TEXT NOT NULL CHECK (provider IN ('Trello','Jira','Asana','Microsoft Planner')), workspace_name TEXT NOT NULL, external_project_key TEXT NOT NULL, base_url TEXT, auth_mode TEXT NOT NULL, connection_status TEXT NOT NULL CHECK (connection_status IN ('Connected','Needs configuration','Disabled')), sync_direction TEXT NOT NULL CHECK (sync_direction IN ('COSMIC to PM','PM to COSMIC','Bidirectional')), last_sync_at TIMESTAMP, created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, UNIQUE(project_id, provider) ); CREATE TABLE external_work_item_mappings ( id TEXT PRIMARY KEY, project_id TEXT NOT NULL REFERENCES projects(id) ON DELETE CASCADE, integration_id TEXT NOT NULL REFERENCES project_management_integrations(id) ON DELETE CASCADE, provider TEXT NOT NULL, local_entity_type TEXT NOT NULL, local_entity_id TEXT NOT NULL, local_title TEXT, external_item_type TEXT NOT NULL, external_item_id TEXT NOT NULL, external_item_key TEXT NOT NULL, external_url TEXT, external_status TEXT, sync_status TEXT NOT NULL CHECK (sync_status IN ('Pending','Synced','Conflict','Failed')), field_mapping_json TEXT, last_synced_at TIMESTAMP, created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, UNIQUE(integration_id, local_entity_type, local_entity_id) ); CREATE TABLE project_management_sync_runs ( id TEXT PRIMARY KEY, project_id TEXT NOT NULL REFERENCES projects(id) ON DELETE CASCADE, integration_id TEXT NOT NULL REFERENCES project_management_integrations(id) ON DELETE CASCADE, provider TEXT NOT NULL, status TEXT NOT NULL CHECK (status IN ('Completed','Completed with errors','Failed')), started_at TIMESTAMP NOT NULL, finished_at TIMESTAMP NOT NULL, created_count INTEGER NOT NULL DEFAULT 0, updated_count INTEGER NOT NULL DEFAULT 0, failed_count INTEGER NOT NULL DEFAULT 0, summary TEXT, created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ); CREATE TABLE report_exports ( id TEXT PRIMARY KEY, project_id TEXT NOT NULL REFERENCES projects(id) ON DELETE CASCADE, report_type TEXT NOT NULL, format TEXT NOT NULL CHECK (format IN ('JSON','CSV','HTML','PDF')), generated_by_user_id TEXT REFERENCES users(id), generated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, file_name TEXT, created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ); CREATE INDEX idx_risks_project_status ON risks(project_id, status); CREATE INDEX idx_risks_dimension ON risks(project_id, dimension); CREATE INDEX idx_scores_risk_time ON risk_scores(risk_id, calculated_at DESC); CREATE INDEX idx_measurements_project_indicator ON measurement_records(project_id, indicator_id); CREATE INDEX idx_gates_project_time ON deployment_gates(project_id, evaluated_at DESC); CREATE INDEX idx_trello_card ON trello_mappings(trello_card_id); CREATE INDEX idx_pm_integrations_project ON project_management_integrations(project_id, provider); CREATE INDEX idx_external_mappings_project ON external_work_item_mappings(project_id, provider); CREATE INDEX idx_sync_runs_project_time ON project_management_sync_runs(project_id, started_at DESC); CREATE INDEX idx_report_exports_project_time ON report_exports(project_id, generated_at DESC); -- Step 7.1, OAuth and live PM connector extension CREATE TABLE IF NOT EXISTS oauth_states ( id TEXT PRIMARY KEY, provider TEXT NOT NULL, integration_id TEXT, project_id TEXT, actor_user_id TEXT, state TEXT NOT NULL UNIQUE, status TEXT NOT NULL, expires_at TEXT, used_at TEXT, created_at TEXT NOT NULL, updated_at TEXT NOT NULL ); CREATE TABLE IF NOT EXISTS oauth_tokens ( id TEXT PRIMARY KEY, provider TEXT NOT NULL, integration_id TEXT, project_id TEXT, token_type TEXT, access_token_encrypted TEXT NOT NULL, access_token_redacted TEXT, refresh_token_encrypted TEXT, refresh_token_redacted TEXT, expires_at TEXT, scope TEXT, status TEXT NOT NULL, raw_metadata_json TEXT, revoked_at TEXT, created_at TEXT NOT NULL, updated_at TEXT NOT NULL ); ALTER TABLE project_management_integrations ADD COLUMN live_enabled BOOLEAN DEFAULT FALSE; ALTER TABLE project_management_integrations ADD COLUMN live_config_json TEXT; ALTER TABLE project_management_sync_runs ADD COLUMN failure_json TEXT; -- Step 8 access control: API permissions are stored in roles.permissions_json and resolved through users.role_id. -- The prototype uses X-Cosmic-User-Id to select the current actor. -- Step 9 production operations note: -- Backup manifests are currently stored as JSON files beside database backups. -- Runtime configuration is provided through environment variables and is intentionally not persisted. -- A future production database version may add this table if backups are tracked inside the database: -- CREATE TABLE backup_manifests ( -- id TEXT PRIMARY KEY, -- actor_user_id TEXT REFERENCES users(id), -- source_file TEXT NOT NULL, -- backup_file TEXT NOT NULL, -- created_at TIMESTAMP NOT NULL, -- size_bytes INTEGER NOT NULL, -- sha256 TEXT NOT NULL -- );