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

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

### 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;
- content items;
- audit events.

Further tables are introduced only with their domain workflow, authorisation policy, migration
tests and cross-tenant tests.