All Mosaic application tables use the MO_ prefix. Legacy Frameworking tables have no prefix but remain in use for workers, organisations, and addresses. This article provides a complete table reference grouped by the area of Mosaic they support.
Person management
Table | Description |
MO_PERSONS | Core person records — demographics, date of birth, date of death, gender, context (child/adult). PERSON_ID is NUMERIC(16) |
MO_PERSON_NAMES | Person names by type — display name, registered name, aliases. Linked by person_id and name_type_id |
MO_PERSON_GENDER_IDENTITIES | Gender identity records with start and end dates |
MO_PERSON_PRONOUNS | Pronoun records with start and end dates |
MO_PERSON_GENDER_PRONOUN_NOTES | Gender pronoun notes with start and end dates |
MO_PERSON_RELATIONSHIPS | Person-to-person relationships (family, carers, etc.) |
MO_PERSON_ORG_RELATIONSHIPS | Person-to-organisation relationships (GP, school, employer, etc.) |
MO_PERSON_WORKER_RELATIONSHIPS | Person-to-worker assignments (allocated worker, social worker, etc.) |
ADDRESSES_PEOPLE | Links between persons and addresses |
ADDRESSES | Address records (shared across persons, organisations, and workers) |
MO_ADDRESSES_NON_ENG_DETAILS | Non-English address translations, linked by address ID and language code |
Groups and subgroups
Table | Description |
MO_GROUPS | Named groups of people |
MO_GROUP_TYPES | Group type definitions controlling available features |
MO_GROUP_SUBJECTS | Group membership records with start and end dates |
MO_SUBGROUPS | Subgroup snapshots used by workflow steps |
MO_SUBGROUP_SUBJECTS | Subgroup membership |
Workflow
Table | Description |
MO_WORKFLOW_STEPS | Runtime workflow step instances — status, assigned worker/team, dates |
MO_WORKFLOW_STEP_TYPES | Step definition templates (configuration) |
MO_WORKFLOW_ACTIVITY_TYPES | Activity definitions within step types |
MO_WORKFLOW_NEXT_ACTION_TYPES | Next action transition definitions |
MO_WORKFLOW_NEXT_ACTION_RULES | Rules constraining next action behaviour |
MO_WORKFLOW_TASK_RULES | Task definitions within steps |
MO_WORKFLOW_TASK_TYPES | Task type descriptions |
MO_ROLE_PERMISSIONS | Per-role permissions for each step type |
Case notes
Table | Description |
MO_CASE_NOTES | Case note headers — type, title, contact date, status, timeout |
MO_CASE_NOTE_DETAILS | Case note body entries. Multiple rows per note support appendments. Includes IS_SIGNIFICANT_EVENT flag |
MO_CASE_NOTE_TYPES | Reference data — case note type codes, descriptions, and context |
MO_CASE_NOTE_ALERT_RECIPIENTS | Alert recipient records. EMAIL_STATUS values: I (initial), P (pending), Y (sent), N (not sent), R (retry), RE (retry error) |
Case chronology
Table | Description |
MO_CASE_CHRONOLOGIES | One row per person. Unique index on PERSON_ID |
MO_CASE_CHRONOLOGY_EVENTS | Individual chronology events. Unique on (CASE_CHRONOLOGY_ID, DATE_FROM, POSITION) |
MO_CREATED_CHRONOLOGIES | Worker-created curated chronologies with title, status, and column visibility flags |
MO_CREATED_CHRONOLOGY_WORKERS | Worker access records for created chronologies |
Documents
Table | Description |
MO_DOCUMENT_METADATA | Document metadata — name, category, sub-category, storage URL, document class |
MO_DOCUMENTS | Legacy binary document content (deprecated — newer documents use Azure or EDRMS storage) |
Forms and templates
Table | Description |
MO_TEMPLATES | Form template definitions |
MO_TEMPLATE_VERSIONS | Versioned form templates |
MO_QUESTIONS | Question definition |
MO_QUESTION_VERSIONS | Versioned question definitions |
MO_TEMPLATE_VERS_QUESTION_VERS | Links questions to template versions |
MO_ANSWER_VALIDATION_RULES | Validation rules applied to form fields |
MO_TEMPLATE_CLASSES | Template classes that control business actions on form completion |
MO_TEMPLATE_VIEW_USES | View types: SCREEN, SCREEN_NEXT_ACT, PRINT, DESIGN, OFFLINE_SCREEN |
Education
Table | Description |
MO_EDUCATION_ACTIV_CATEGORIES | Activity categories with effective start and end dates |
MO_EDUCATION_EXCL_STATUS_TYPES | Exclusion status reference data |
MO_EDUCATION_CHARACTER_TYPES | Individual characteristic types |
MO_EDUCATION_SUPPORT_SCHEME_TYPES | Support scheme reference data |
MO_EDUCATION_LOG_OUTCOME_TYPES | Log outcome types |
MO_EDUCATION_CONTACT_METHODS | Contact method reference data |
MO_EDUCATION_SEN_SCHEME_TYPES | SEN scheme reference data |
Care leavers
Table | Description |
MO_CARE_LEAVER_AGES | Configuration of PRE_DAYS and POST_DAYS windows around care leaver age milestones |
MO_CARE_LEAVER_SITUATIONS | Recorded situations per person and age |
Workers and organisations (legacy FWi tables)
Table | Description |
Workers | Worker accounts. ID is numeric(9). Unique index on system_user_id (login name) |
Organisations | Organisation records |
Teams | Teams within organisations |
People_workers | Worker-to-person relationship allocations |
Addresses | Physical addresses shared across persons, organisations, and workers |
People | Legacy FWi person table. Mosaic primarily uses MO_PERSONS |
Reference data and lookups
Table | Description |
reference_data | Generic reference data rows linked to reference_data_areas |
reference_data_areas | Area definitions with optional context column |
mo_name_types | Person name type reference data (birth name, registered name, etc.) |
mo_languages | Language reference data |
bank_holidays | Bank holiday dates |
prof_relationship_types | Professional relationship types between workers and persons |
System configuration
Table | Description |
system_functions | Permission codes (system functions). function_code is the key |
system_functions_roles | Mapping of permission codes to worker roles |
system_function_group_members | Groups of related system functions |
system_properties | String-valued runtime configuration settings |
system_numeric_properties | Numeric runtime settings (e.g. Case_Note_Timeout_Hours, LA_Org_ID) |
external_system_function_types | Links system functions to external system integrations |
nrd_mappings | Maps function codes to notification rule data |