# Mi-Insite canonical data model ## Modelling principles - PostgreSQL is the system of record. - UUID primary keys are generated server-side. - All tenant-owned records carry `tenant_id`. - Provider identifiers are namespaced by connector and account. - Mutable business objects retain version history; audit records are append-only. - Source payload, canonical content, enrichment and analyst corrections remain distinguishable. - Every metric and report number is reproducible from source lineage and a data cut-off. - Licence and retention metadata travel with evidence through every projection. ## Tenancy and identity ### Tenant Commercial, privacy and permission boundary. Stores slug, status, branding, locale, default time zone and policy references. ### Workspace A tenant-owned operating area for a brand, programme or client team. A workspace cannot reference another tenant's content, connectors or users. ### User and membership Users represent identities; tenant memberships assign status and capabilities. Workspace assignments further restrict operational access. MFA credentials and recovery material are stored separately with restricted access. The implemented foundation contains global `users`, `password_credentials`, `sessions` and one-time `password_reset_tokens`; only password hashes and token digests persist. Tenant memberships carry `tenant_id` and one named organisation role. Workspace memberships carry both `tenant_id` and `workspace_id`, enforced by composite foreign keys. Tenant-owned invitations may optionally restrict access to one workspace. Tenant and workspace settings use validated South African defaults. Identity tables are deliberately global because one identity may later join multiple organisations. Active tenant/workspace fields on a session are selectors, never authorisation grants. ### Role, capability and policy Capabilities govern view, export, approve, publish, administer and retain actions. Policies provide effective dates, versions and approval requirements. ## Sources and ingestion ### Connector Defines provider type, state, capability version, health, entitlement and rate-limit budget. Credentials are referenced from a secret store rather than stored in plaintext. ### Source account Represents an authorised social account, RSS feed or direct source. It records provider identity, permissions, last reconciliation and token expiry metadata. ### Ingestion event Immutable receipt record containing idempotency key, provider event identity, received time, permitted encrypted payload reference, rights class and processing state. ### Content item Canonical article, post, comment, reply, review, broadcast segment or imported record: - tenant and workspace; - source, connector and provider identifiers; - canonical URL and source timestamps; - author/outlet references; - permitted text and media references; - language, geography and visibility; - parent, conversation and duplicate-group relationships; - licence, retention and evidence status. ### Author, person and outlet Identity entities retain provenance and confidence. A public author is not automatically a CRM person. Any merge or first-party enrichment is consent-aware and auditable. ## Intelligence ### Query and monitor Versioned inclusion/exclusion logic, source scope, languages, entities, taxonomies and schedule. Every alert and report references the exact query version used. ### Taxonomy and classification Tenant-owned topics, issues, message pillars and risk labels. A classification records value, confidence, method, model version and optional analyst correction. ### Entity, topic and narrative Enrichment objects connected through versioned observations. Analysts may merge, split, rename and lock narrative clusters without overwriting previous model output. ### Alert References monitor version, trigger rule, evidence set, severity, recipients and delivery attempts. Alerts may create or join cases idempotently. ## Operations ### Conversation and case A conversation groups related channel interactions. A case connects content, people, priority, owner, SLA clocks, playbook, approvals, responses and resolution. Case transitions are append-only events. Current state is a transactional projection. ### SLA policy and clock Stores tenant calendar, priority, response target, resolution target, pause rules and effective dates. Clock events reconstruct elapsed and paused time. ### Approval An approval request identifies the exact subject version, required capability, order, decision, actor, reason and timestamp. Editing approved content creates a new version and invalidates the approval as policy requires. ### Incident Crisis object connecting severity, incident commander, timeline, stakeholders, decisions, statements, situation reports and post-incident review. ## Publishing ### Campaign Joins objective, audience, dates, markets, message pillars, baseline, content, coverage, costs and KPI definitions. ### Post, variant and version A master post owns network-specific variants. Each version stores rendered content, validation result, author and campaign. Approval attaches to an immutable version. ### Asset and asset right Object metadata, hash, alt text, owner, licence, usage window, derivative relationships and retention. Expired assets cannot be scheduled. ### Publication Idempotent attempt containing target account, scheduled time, provider request key, status, error, retry count, provider post ID and reconciliation time. ## Archive and governance ### Archive snapshot Permitted content and metadata reference, canonical hash, capture time, object version, retention class and source rights. ### Retention policy Tenant, content class, licence, duration, deletion action and effective dates. Resolution records whether to delete content, retain metadata or create a tombstone. ### Legal hold Scope, authority, reason, start, review and release. Active holds prevent applicable deletion. ### Audit event Append-only actor, tenant, action, target, request/job correlation, policy version, timestamp and sanitised metadata. Restricted personal or content data does not belong in ordinary log fields. Workspace-specific events carry both tenant and workspace boundaries. A database trigger rejects update and delete operations. ### Evidence bundle Immutable export request, selection criteria, cut-off, manifest, object hashes, approver and expiry. Download access is short-lived and audited. ## KPI and reporting ### KPI definition Name, objective, formula, accepted sources, dimensions, exclusions, baseline, target, warning bands, owner, cadence, confidence method and version. ### KPI observation Definition version, period, dimensions, value, numerator, denominator, confidence, source lineage and calculation run. ### Dashboard and report Saved selection of KPI observations, queries, evidence, narrative and audience. A fixed report records data cut-off, edits, approval, export object and delivery attempts. ## Database enforcement - Tenant tables use row-level security and non-null tenant keys. - Unique provider constraints include tenant, connector and source account. - Foreign keys and service queries enforce the tenant boundary. - Frequently filtered tenant/time columns receive composite indexes. - High-volume content, events and observations are partition candidates. - Outbox rows are inserted in the same transaction as domain changes. - Destructive retention runs use small batches, legal-hold checks and audit events. - Production roles separate application, migration, maintenance and read-only reporting access. ## Local scaffold schema The initial migration implements the minimum vertical foundation: - tenants; - workspaces; - users; - tenant memberships; - workspace memberships; - password credentials, sessions and one-time reset tokens; - invitations; - content items; - audit events. Further tables are introduced only with their domain workflow, authorisation policy, migration tests and cross-tenant tests.