The Mosaic database schema follows strict conventions that make it easier to navigate once you understand the patterns. This article covers table prefixes, constraint naming, audit columns, boolean flags, date handling, and key data type differences between Oracle and SQL Server.
Database platform differences
Feature | Oracle | SQL Server |
Auto-increment | Sequences (ISQ_ prefix) + trigger | IDENTITY(1,1) columns |
Large text | CLOB | varchar(max) |
Date type | DATE | datetime |
Variable string | VARCHAR2(n CHAR) | varchar(n) |
Boolean columns | VARCHAR(1) with 'Y'/'N' | VARCHAR(1) with 'Y'/'N' |
📌Note: Oracle DDL files use character-length semantics (`nls_length_semantics=char`), so VARCHAR2 columns measure length in characters, not bytes.
Table naming conventions
Prefix | Meaning | Example |
MO_ | Mosaic application table. | MO_PERSONS, MO_CASE_NOTES, MO_WORKFLOW_STEPS. |
MO_EDUCATION_ | Education-related tables. | MO_EDUCATION_ACTIV_CATEGORIES. |
MO_EDUCATION_ACTIV_CATEGORIES | (no prefix). | Legacy Frameworki tables still in use | workers, organisations, addresses. |
MAINTAINED_ | Schema management infrastructure. | MAINTAINED_OBJECT_CHANGES. |
📌Note: Tables without the MO_ prefix originate from the predecessor Frameworki (FWi) system. Some remain the authoritative source for worker and organisation data.
Constraint and index naming conventions
Prefix | Type | Example |
XPKMO_ | Primary key. | XPKMO_CASE_NOTES |
XAK | Unique constraint or unique index. | XAK1MO_CASE_CHRONOLOGIES |
XIF | Foreign key support index | XIF1MO_CASE_NOTES |
XIE | Non-unique secondary index | XIE1MO_CASE_NOTE_DETAILS |
ISQ_ | Oracle identity sequence | ISQ_MO_CASE_NOTES |
Audit columns
Most transactional tables include audit columns that track who created and updated each record. The "for" variants record the worker on whose behalf the action was performed (used with the Can Act For feature).
Column | Description |
CREATED_DATETIME / CREATED_ON | Timestamp of creation (defaults to current date/time) |
CREATED_BY_WORKER_ID | The worker who created the record |
CREATED_FOR_WORKER_ID / CREATED_ACTING_FOR | he worker on whose behalf the record was created (defaults to system worker 2001) |
CREATED_BY_TEAM_ID | The team of the creating worker |
UPDATED_DATETIME / UPDATED_ON | Timestamp of last update |
UPDATED_BY_WORKER_ID | The worker who last updated the record |
UPDATED_FOR_WORKER_ID / UPDATED_ACTING_FOR | The worker on whose behalf the update was made |
DELETED_ON | Timestamp of soft deletion (NULL = not deleted) |
DELETED_BY_ID | The worker who deleted the record |
DELETED_ACTING_FOR_ID | The worker on whose behalf the deletion was made |
Boolean flag columns
Boolean values are stored as VARCHAR(1) with values 'Y' or 'N', enforced by a check constraint.
⚠️Important: Always filter boolean columns using 'Y' or 'N' string values in your queries, not 1/0 or true/false.
Date-only columns
Some columns store dates without a time component, enforced by check constraints:
Oracle: `CHECK (col = TRUNC(col))`
SQL Server: `CHECK (col = CONVERT(datetime, CONVERT(varchar, col, 103), 103))`
Person ID vs Worker ID
PERSON_ID is NUMERIC(16) throughout the schema.
WORKER_ID is NUMERIC(9) throughout the schema.
These different sizes reflect the legacy FWi format. Be aware of this when joining person and worker tables.
Restriction status function
Person restriction status is computed by the database function `mosaic_person_restrictions.is_restricted(person_id)`. This function is called in most person-related queries.
⚠️Important: If your report includes person data, ensure you respect restriction settings. Call this function or check the restriction status to ensure restricted person data is only visible to authorised users.
Tips for report writers
All Mosaic tables use the MO_ prefix. Legacy FWi tables have no prefix.
Boolean columns use 'Y'/'N' - never use 1/0 or true/false in your queries.
Use TRUNC() (Oracle) or CONVERT() (SQL Server) when comparing date-only columns.
PERSON_ID is NUMERIC(16), WORKER_ID is NUMERIC(9) — be aware of this when joining.
🤓Tip: If you are unsure which table holds the data you need, see the database table reference articles for a full listing by functional area.