Introduction
This document will step through all of the tables in the staging database and applies to all versions of core from 7.9.0 onwards (unless otherwise noted).
All tables are in a schema called source; any other schema and tables in the staging database that ShareDo connects to will be ignored. The use of the schema name source is omitted below for brevity.
Base Tables
ODS
Purpose: A base table that is referenced by all other tables (organisation/person/user/etc.) when referencing an ODS.
Note: The reference of an ODS is globally unique (to these tables).
Columns:
| reference (nvarchar(200)) | ODS reference identifier, unique, primary key |
| name (nvarchar(250)) | For organisation and team, actual name. For user and person, an aid for readability though not used. |
| type (nvarchar(250)) | One of organisation, person, user, team |
sharedo
Purpose: One row for each row in a primary sharedo table.
Note: The reference is used once loaded as the visible reference in sharedo - so care is recommended as to its format. Further, cut-over planning should make sure that the used references don't clash with those that would be generated via the reference generator. Discuss with the project's Solution Archive.
Columns:
| reference (nvarchar(200) | Primary key |
| sharedoTypeSystemName (nvarchar(200)) | Case sensitive internal ‘system name’ |
| phaseSystemName (nvarchar(100)) | Case sensitive internal ‘system name’ |
| category (nvarchar(500)) | The text of the category, a column to aid data translations and not required (see below) |
| title (nvarchar(max)) | Text, invariable short |
| description (nvarchar(max)) | Text, invariable long |
| categoryId (int) | The integer id of the option set value (see below) |
| externalReference (nvarchar(100)) | Optional, can hold some key that is owned by an external system that correlates to this item |
| titleIsUserProvided (bit) | Usually the title is provided and (for performance) not set by title generators |
| timeZone (nvarchar(100)) | ISO format of the timezone name |
Configuration references
- sharedoTypeSystemName - from configuration in Modeller for the type, and visible in the configuration extract
importConfig.sharedoTypes. - phaseSystemName - from configuration in Modeller for the type, and visible in the configuration extract
importConfig.phases.
Category Lookup
The categoryId column is data loaded and requires to be set with the ID within the configured category. This is from importConfig.sharedoType.categoryOptionSetSystemName and one of the corresponding options (of the option set) within importConfig.optionSetValues. A solution architect can discuss this together with mapping requirements to align the extracted data with the configuration created/curated within Modeller.
ODS Primary data tables
organisation
Purpose: Holds primary organisation attributes.
Note: this data may already exist in the ShareDo environment; it can be matched by external reference or unique ID.
Columns:
| reference, odsReference (nvarchar(200)) | Same field, unique identifier and table primary key |
| matchUniqueId (uniqueidentifier) | Matching pre-existing unique identifier. |
| industrySICCode (nvarchar(250)) | Industry SIC code |
| isVATRegistered (nvarchar(250)) | - VAT registration flag/value. |
| VATCountryCode (nvarchar(250)) | ISO VAT country code. |
| VATNumber (nvarchar(250)) | VAT number |
| VATComments (nvarchar(250)) | Notes related to VAT |
| companyNumber (nvarchar(250)) | Company registration number |
| externalReference (nvarchar(200)) | External system reference |
| organisationReference (nvarchar(200)) | Organisation reference string. |
Data references
- odsReference - to the ods table
Configuration references
- VATCountryCode - the isoCode of the country taken from
importConfig.countries.
person
Purpose: Attributes relating to people.
Note: A user is also a person, so users will have rows in both this table and user.
Columns:
| reference (nvarchar(200)) | Primary reference identifier for the person |
| odsReference (nvarchar(200)) | Reference to the ODS (Operational Data Store) system |
| matchUniqueId (uniqueidentifier) | Unique identifier for matching purposes |
| personReference (nvarchar(200)) | Additional person reference |
| externalReference (nvarchar(100)) | External system reference |
| title (nvarchar(250)) | Person's title (Mr., Ms., Dr., etc.) using the relevant option set value |
| gender (nvarchar(250)) | Gender information using the relevant option set value |
| firstname (nvarchar(200)) | First name |
| surname (nvarchar(200)) | Last name |
| middlenameOrInitial (nvarchar(100)) | Middle name or initial |
| dateOfBirth (date) | Date of birth |
| dateOfDeath (date) | Date of death (if applicable) |
| niNumber (nvarchar(50)) | National Insurance number |
| employeeCode (nvarchar(50)) | Employee identification code |
| primaryTeamId (uniqueidentifier) | Links to the primary team the person belongs to |
| contactHoursFrom (time) | Start time for contact hours |
| contactHoursTo (time) | End time for contact hours |
| isActive (bit) | Boolean flag indicating if the person record is active |
team
Purpose: The source. table stores information about teams within the organisation.
Note: Typically, teams are treated as configuration and promoted across environments via config export & import; the use of this table is to support using existing teams, typically as participants on work items.
Columns:
| reference (nvarchar(200)) | Primary reference identifier for the team |
| odsReference (nvarchar(200)) | Reference to the ODS (Operational Data Store) system |
| matchUniqueId (uniqueidentifier) | Unique identifier for matching purposes |
| externalReference (nvarchar(100)) | External system reference |
| name (nvarchar(500)) | The team's name (up to 500 characters) |
| description (nvarchar(100)) | Brief description of the team |
| isActive (bit) | Boolean flag indicating if the team is currently active |
| canHaveExternalMembers (bit) | Boolean flag indicating whether the team can include external members (non-employees) |
users
Purpose: Shared primary key into [ods], extends the details in [person] with user attributes such as identity claim.
Note: Typically, users are managed in an external system such as Azure Entra and (effectively) preexist in the environment prior to data loading. The use of this table and the matching to existing (user) data enables the use of these preexisting users as participants. If a user is not matched (for example, they may be former employees), then a user account will be created.
Columns:
| reference (nvarchar(200)) | Primary reference identifier for the user |
| odsReference (nvarchar(200)) | Reference to the ODS (Operational Data Store) system |
| matchUniqueId (uniqueidentifier) | Unique identifier for matching purposes |
| identityClaim (nvarchar(400)) | Identity claim information (likely contains authentication details such as email, username, or other identity tokens) |
| identityProvider (nvarchar(50)) | The identity provider used for authentication (e.g. aad) |
ODS Child Data
address
Purpose: Stores postal and location addresses associated with ODS entities.
Columns:
| reference (nvarchar(200)) | Address record reference. |
| odsReference (nvarchar(200)) | Entity reference the address belongs to. |
| odsType (nvarchar(250)) | Type/category of the ODS entity. - organisation, person, user, team |
| addressType (nvarchar(250)) | Classification of the address (e.g., billing, site). |
| addressName (nvarchar(500)) | Address label or name. |
| addressLine1 (nvarchar(250)) | Address line 1. |
| addressLine2 (nvarchar(250)) | Address line 2. |
| addressLine3 (nvarchar(250)) | Address line 3. |
| addressLine4 (nvarchar(250)) | Address line 4. |
| town (nvarchar(250)) | Town/City. |
| county (nvarchar(250)) | County/State/Region. |
| postCode (nvarchar(50)) | Postal/ZIP code. |
| countryCode (nvarchar(500)) | Country code or name. |
| dxNumber (nvarchar(100)) | DX mailbox number (if applicable). |
| dxName (nvarchar(100)) | DX exchange name (if applicable). |
| isActive (bit) | Whether this address is active. |
| departmentCode (nvarchar(100)) | Department or sub-unit code. |
| externalReference (nvarchar(100)) | External system reference. |
| latitude (float) | Latitude coordinate. |
| longitude (float) | Longitude coordinate. |
| geoCodeType (int) | Type/precision of geocoding used. |
| bagBoxNumber (nvarchar(200)) | BAG/PO Box number. |
Data references
- odsReference - the ods this record is child data of.
Configuration references
- addressType - from feature configuration in Modeller, the type of the address - billing, correspondence, etc.
contactDetails
Purpose: Stores contact details with a type code, value, and a flag indicating whether the contact detail is primary for its context.
Note: The isPrimary bit should be set to true for one of each contact type, and this will result in that contact method appearing in summary cards in the UI.
Columns:
| reference (nvarchar(200)) | A reference identifier associated with the contact detail. |
| odsReference (nvarchar(200)) | An ODS-oriented reference identifier associated with the contact detail. |
| contactTypeCode (nvarchar(50)) | A code indicating the contact detail type (for example, a category label). |
| contactValue (nvarchar(400)) | eg values such as email, phone numbers, Twitter, CompuServe etc.. |
| isPrimary (bit) | Indicates whether this contact detail is the primary one within its context (1 = primary, 0 = not primary). |
Data references
- odsReference - the ods this record is ‘child data of’.
Configuration references
- contactTypeCode should match the environment's configuration in Modeller and as extracted in
importConfig.contactTypes.
employmentDetails
Purpose: Specific metadata to connect organisations with people as employees.
Note: There is some overlap with odsRelationships, however, this loader has additional attributes. Choose the table based on the configuration (in Modeller).
Columns:
| reference (nvarchar(200)) | Employment record reference |
| personReference (nvarchar(200)) | Person reference |
| organisationReference (nvarchar(200)) | Organisation reference |
| employeeCode (nvarchar(100)) | Internal employee code |
| jobTitle (nvarchar(500)) | Job title/position |
| primaryRoleSystemName (nvarchar(200)) | Primary role identifier |
formAspectAttributes
Purpose: Allows loading of fields as extended via aspects & forms in Modeller.
Note: Each row in this table should reference exactly one sharedo (work item), participant or ODS.
Columns:
| reference (nvarchar(400)) | Attribute record reference. |
| attribute (nvarchar(200)) | Attribute name/key, case sensitive |
| value (nvarchar(max)) | Attribute value. |
| odsReference (nvarchar(200)) | Related ODS entity reference |
| sharedoReference (nvarchar(200)) | Related Sharedo reference |
| participantReference (nvarchar(200)) | Related participant reference |
| groupReference (nvarchar(200)) | Coalesced field. |
odsRelationship
Purpose: To link two ODS entities in relations that can be extended in Modeller
Columns:
| reference (nvarchar(200)) | Relationship reference |
| parentReference (nvarchar(200)) | Parent ODS reference |
| siblingReference (nvarchar(100)) | Related/sibling ODS reference |
| relationshipType (nvarchar(50)) | Relationship type code |
| relationshipMeta1 (nvarchar(500)) | Additional metadata for the relationship |
| periodFrom (datetime) | Start date/time of the relationship |
| periodTo (datetime) | End date/time of the relationship |
odsTags
Purpose:
Notes: Tags (aka party types) are attributes specific to the ODS, irrespective of how or if the ODS participates in a work item. For example, a person may be recognised as a medical specialist and participate in a matter as a medical expert. Frequently, the words used for the tags and the name of a role are the same, e.g. an organisation that is recognised as an insurer may be included on a matter as the insurer; this table is to track the attributes. The nomenclature within the ShareDo UI and documentation may refer to tags, party types and types when meaning attributes that are associated with an ODS. Refer to the glossary for (within data load discussions) the terms used.
Columns:
| reference (nvarchar(200)) | Tag record reference |
| odsReference (nvarchar(200)) | Related ODS reference |
| odsTag (nvarchar(100)) | Tag value/label |
Work Item / ShareDo Primary Tables
contract
Purpose: Within a typical ShareDo deployment, the contract is the top-level work item.
Columns:
| reference (computed, sharedoReference) | Computed with value from sharedoReference |
| sharedoReference (nvarchar (200) NOT NULL) | The unique key, is also visible once loaded in the UI |
| matchUniqueId (uniqueidentifier NULL) | For when referring to an existing contract |
| createdDate (datetime NOT NULL) | Historic value for when the contract was created |
| effectiveFrom (datetime NOT NULL) | Validity date |
| effectiveTo (datetime NOT NULL) | Validity date |
customSharedos
Purpose: Created in Modeller and somewhat generic in that they have no specific attributes, but are fully fledged work items.
Columns:
| reference (nvarchar(200)) | Custom record reference |
| sharedoReference (nvarchar(200)) | Linked Sharedo reference |
instruction
Purpose: Stores instruction metadata linked to a ShareDo case or item.
Columns:
| reference (nvarchar(200)) | Instruction reference. |
| sharedoReference (nvarchar(200)) | Related Sharedo reference. |
| matchUniqueId (uniqueidentifier) | Unique matching identifier for deduplication/correlation. |
| sourceOfBusiness (nvarchar(200)) | Source of business/origin. |
| caseWorkType (nvarchar(200)) | Case work type. |
| jurisdiction (nvarchar(200)) | Jurisdiction. |
| caseSharedoTypeSystemName (nvarchar(200)) | Case Sharedo type name. |
matter
The most common entity used to match ‘a thing’ that is progressed in the business context.
Table: sharedo-staging.source.matter
Purpose: Represents matter-level metadata and client/jurisdiction details.
Columns:
| reference (nvarchar(200)) | Matter reference. |
| sharedoReference (nvarchar(200)) | Related Sharedo reference. |
| matchUniqueId (uniqueidentifier) | Match/correlation identifier. |
| jurisdictionCode (nvarchar(250)) | Jurisdiction code or name. |
| clientSowReference (nvarchar(250)) | Statement of work reference for the client. |
| internalDocumentReference (nvarchar(250)) | Internal document reference. |
| externalDocumentReference (nvarchar(250)) | External document reference. |
| matterNumber (nvarchar(200)) | Matter/case number. |
| notificationContent (nvarchar(max)) | Notification or message content. |
| clientOdsReference (nvarchar(250)) | Client ODS reference. |
| feeTemplateSystemName (nvarchar(200)) | Fee template system name. |
proceding
Optional data within a matter holding proceeding-specific attributes (such as jurisdiction and court track).
Table: sharedo-staging.
Purpose: Represents proceeding-level metadata.
Columns:
| reference (nvarchar(200)) | Primary reference identifier for the proceeding. |
| sharedoReference (nvarchar(200)) | Reference to the associated work item (shared document). |
| parentSharedoReference (nvarchar(200)) | Reference to a parent sharedo, indicating hierarchical relationships between proceedings. |
| courtReference (nvarchar(50)) | Reference number or identifier assigned by the court. |
| courtTrack (nvarchar(250)) | The court track or pathway the proceeding follows. |
| jurisdiction (varchar(200)) | The legal jurisdiction where the proceeding takes place. |
| litigationReason (varchar(200)) | The reason or basis for the litigation. |
statementOfWork
Purpose: Represents SoW metadata. Within typical ShareDo deployments, this is data that is a child of a contract and a parent to matters.
Columns:
| reference (nvarchar(200)) | Primary reference identifier for the statement of work |
| sharedoReference (nvarchar(200)) | Reference to the associated sharedo (shared document) |
| matchUniqueId (uniqueidentifier) | Unique identifier for matching purposes |
| parentContractReference (nvarchar(200)) | Reference to the parent contract this SOW falls under |
| feeTemplateSystemName (nvarchar(200)) | System name of the fee template associated with this SOW; this is optional from core 7.11 onwards. |
| matchingSharedoTypeSystemNamesCoverage (nvarchar(max)) | Types of sharedos this SOW covers (unlimited length field suggests it can contain multiple types) |
| matchingDateStart (datetime) | Start date for when this SOW is applicable |
| matchingDateEnd (datetime) | End date for when this SOW is applicable |
| matchingKeyDateSystemName (nvarchar(200)) | Specific key date system name that triggers this SOW |
| matchingParticipantRoleSystemNameSoW (nvarchar(200)) | Participant role in the SOW context |
| matchingParticipantRoleSystemNameInstruction (nvarchar(200)) | Participant role in the instruction context |
| matchingBusinessSource (nvarchar(200)) | Business source that this SOW applies to |
Work Item / ShareDo Child Tables
accountAdjustments & accountAdjustmentTransactions
Purpose: Represents account adjustment headers capturing who, when, and currency context.
Columns:
| reference (nvarchar(200)) | Unique reference for the account adjustment. |
| sharedoReference (nvarchar(200)) | Associated Sharedo item reference. |
| userReference (nvarchar(200)) | User responsible or associated with the adjustment. |
| effectiveDate (datetime) | Effective date of the adjustment. |
| currencyCode (nvarchar(3)) | ISO currency code for the adjustment. |
budgetTransactions
Purpose: Captures budget-related movements and adjustments tied to ShareDo items and budget structures.
Columns:
| reference (nvarchar(200)) | Budget transaction reference. |
| sharedoReference (nvarchar(200)) | Related Sharedo reference. |
| budgetStructureReference (nvarchar(200)) | Budget structure involved. |
| chartOfAccountsSegmentUniqueCode (nvarchar(200)) | Chart of accounts segment. |
| budgetPosition (nvarchar(200)) | Budget position or line item. |
| transactionAmount (decimal(18,2)) | Amount of the transaction. |
| transactionDate (datetime) | Date of the transaction. |
| transactionCurrencyCode (nvarchar(5)) | Currency of the transaction. |
| changeReason (nvarchar(50)) | Reason code for the change. |
| userReference (nvarchar(50)) | User associated with the transaction. |
| settlementOutcome (nvarchar(200)) | Outcome related to settlement, if applicable. |
comments
Purpose: Stores user comments associated with ShareDo work items.
Columns:
| reference (nvarchar(200)) | Comment reference. |
| userReference (nvarchar(100)) | Authoring user reference. |
| sharedoReference (nvarchar(200)) | Target Sharedo reference. |
| created (datetime) | Timestamp when the comment was created. |
| comment (nvarchar(max)) | Comment text. |
| isPrivate (bit) | Whether the comment is private. |
documents
Purpose: Stores document metadata, location, ownership, and classification details to enable a deep link from Chronology to a specific file inside the DMS.
Columns:
| reference (nvarchar(250)) | Internal reference for the row. |
| documentReference (nvarchar(250)) | Document system reference. |
| fileName (nvarchar(max)) | File name of the document. |
| folder (nvarchar(max)) | Folder or virtual path. |
| createdDate (datetime) | Document creation date. |
| createdByUserRef (nvarchar(250)) | User who created the document. |
| UNCfilePath (nvarchar(max)) | UNC path (if applicable). |
| sharedoTypeSystemName (nvarchar(200)) | ShareDo type name. |
| phaseSystemName (nvarchar(100)) | Phase of the ShareDo item at creation. |
| category (nvarchar(500)) | Document category. |
| title (nvarchar(max)) | Document title. |
| description (nvarchar(max)) | Document description. |
| ownerUserReference (nvarchar(100)) | Owner user reference. |
| deliveryMethodSystemName (nvarchar(100)) | Delivery method identifier. |
| recordDeliveryFlag (bit) | Whether delivery was recorded. |
| postageClass (nvarchar(200)) | Postage class (if posted). |
| tracingReference (nvarchar(200)) | Tracing or tracking reference. |
| deliveryMethodPhaseSystemName (nvarchar(200)) | Phase of the delivery method. |
| sharedoReference (nvarchar(200)) | Related ShareDo reference. |
| documentLoadTypereference (nvarchar(50)) | Load type reference. |
| documentLoadType (nvarchar(50)) | Load type label. |
| repositoryId (nvarchar(200)) | Repository identifier. |
| poiSystemName (nvarchar(200)) | Point-of-interest or system tag. |
formAspectAttributes
Purpose: Allows loading of fields as extended via aspects & forms in Modeller.
Note: Each row in this table should reference exactly one sharedo (work item), participant or ODS.
Columns:
| reference (nvarchar(400)) | Attribute record reference. |
| attribute (nvarchar(200)) | Attribute name/key, case sensitive |
| value (nvarchar(max)) | Attribute value. |
| odsReference (nvarchar(200)) | Related ODS entity reference |
| sharedoReference (nvarchar(200)) | Related Sharedo reference |
| participantReference (nvarchar(200)) | Related participant reference |
| groupReference (nvarchar(200)) | Coalesced field. |
incident
Purpose: Captures incident details such as location, description, and contextual factors.
Columns:
| reference (nvarchar(400)) | Incident reference. |
| sharedoReference (nvarchar(200)) | Related Sharedo reference. |
| addressLine1 (nvarchar(200)) | Address line 1 of the incident location. |
| addressLine2 (nvarchar(100)) | Address line 2 of the incident location. |
| townCity (nvarchar(100)) | Town/City of the incident. |
| county (nvarchar(100)) | County/Region of the incident. |
| country (nvarchar(100)) | Country of the incident. |
| postCode (nvarchar(50)) | Postal code of the incident. |
| description (nvarchar(max)) | Description of the incident. |
| reportedToPolice (bit) | Whether the incident was reported to police. |
| weatherConditions (nvarchar(max)) | Weather conditions at the time. |
| incidentType (nvarchar(100)) | Type/category of incident. |
| incidentCause (nvarchar(100)) | Cause of incident. |
Constraints and Keys: The provided definition does not define primary keys, foreign keys, unique constraints, defaults, or indexes.
invoices
Table: sharedo-staging.source.invoices
Purpose: Stores invoice headers, amounts, and payor information.
Columns:
| reference (nvarchar(200)) | Invoice reference. |
| parentSharedoReference (nvarchar(200)) | Related parent Sharedo reference (e.g., matter or request). |
| details (nvarchar(max)) | Invoice details/description. |
| amount (decimal(18,2)) | Invoice net amount. |
| vat (decimal(18,2)) | VAT amount. |
| invoiceIssuedDate (datetime) | Date the invoice was issued. |
| payorOdsReference (nvarchar(200)) | Payor ODS reference. |
Constraints and Keys: The provided definition does not define primary keys, foreign keys, unique constraints, defaults, or indexes.
invoiceTransactionItems
Purpose: Stores individual invoice line items, including values, tax, and status.
Columns:
| reference (nvarchar(200)) | Invoice transaction reference. |
| sharedoReference (nvarchar(200)) | Related invoice/sharedo reference. |
| reserveTypeCode (nvarchar(500)) | Reserve or accounting code. |
| description (nvarchar(max)) | Line item description. |
| vat (decimal(18,2)) | VAT amount for the line. |
| amount (decimal(18,2)) | Net amount for the line. |
| quantity (decimal(18,2)) | Quantity for the line item. |
| UnitOfMeasure (nvarchar(200)) | Unit of measure for the quantity. |
| taxRate (decimal(18,2)) | Tax rate applied (percentage). |
| creditAccountCodes (nvarchar(200)) | Credit account codes used for posting. |
| currency (nvarchar(200)) | Currency code. |
| status (nvarchar(200)) | Processing status of the line item. |
| transactionItemType (nvarchar(200)) | Type/category of transaction item. |
Constraints and Keys: The provided definition does not define primary keys, foreign keys, unique constraints, defaults, or indexes.
invoiceReceiptItems
Purpose: Records receipt items associated with invoices, including dates and amounts.
Columns:
| reference (nvarchar(200)) | Receipt item reference. |
| sharedoReference (nvarchar(200)) | Related Sharedo reference. |
| receiptDate (datetime) | Date of the receipt. |
| paymentMethod (nvarchar(200)) | Payment method used. |
| amount (decimal(18,2)) | Amount received. |
| reserveTypeCode (nvarchar(500)) | Reserve type code, if applicable. |
Constraints and Keys: The provided definition does not define primary keys, foreign keys, unique constraints, defaults, or indexes.
keyDates
Purpose: Structured date fields that store various additional attributes.
Notes: In addition to the data in the keyDates table, additional attributes are in the (common / base) sharedo table. This enables (subject to configuration) other attributes and (for example) phase history or participant assignments to be loaded for key dates.
Columns:
| reference (nvarchar(200)) | Key date reference |
| keyDateType (nvarchar(250)) | Type/category of the key date |
| keyDate (datetime) | The date/time of the event |
| keyDateStatusCode (nvarchar(250)) | Status code for the key date |
| sharedoReference (nvarchar(200)) | Related Sharedo reference |
| reminderOffset (int) | Reminder offset amount |
| reminderOffsetMetric (nvarchar(3)) | Unit for the offset (e.g., M, D, see below) |
| reminderTag (nvarchar(100)) | Tag/label for reminder grouping |
The reminderOffsetMetric constants are based on the following:
MinutesBefore = { Code = "M", Name = "Minute(s)" };HoursBefore = { Code = "H", Name = "Hour(s)" };DaysBefore = { Code = "D", Name = "Day(s)" };WeekDaysBefore = { Code = "WD", Name = "Week day(s)" };CalendarDaysBefore = { Code = "CD", Name = "Calendar day(s)" };MinutesAfter = { Code = "MA", Name = "Minute(s)" };HoursAfter = { Code = "HA", Name = "Hour(s)" };DaysAfter = { Code = "DA", Name = "Day(s)" };WeekDaysAfter = { Code = "WDA", Name = "Week day(s)" };CalendarDaysAfter = { Code = "CDA", Name = "Calendar day(s)" };
legalProperties
Purpose: Captures property details connected to a sharedo (work item), including title, type, tenure, and physical characteristics.
Columns:
| reference (nvarchar(200)) | Property record reference. |
| sharedoReference (nvarchar(200)) | Related Sharedo reference. |
| addressReference (nvarchar(200)) | Related address reference. |
| titleNumber (nvarchar(100)) | Land title/folio number. |
| propertyType (nvarchar(100)) | Type of property. |
| tenure (nvarchar(100)) | Tenure/ownership class. |
| description (nvarchar(max)) | Free-text description. |
| age (int) | Age of the property (years). |
| size (int) | Size/area indicator. |
| construction (nvarchar(100)) | Construction type/material. |
offerAmounts
Purpose: Stores monetary amounts for offers by reserve type.
Columns:
| reference (nvarchar(200)) | Offer amount reference. |
| offerReference (nvarchar(200)) | Related offer header reference. |
| reserveTypeCode (nvarchar(500)) | Reserve type or code. |
| amount (decimal(18,2)) | Amount offered. |
Constraints and Keys: The provided definition does not define primary keys, foreign keys, unique constraints, defaults, or indexes.
offers
Purpose: Links a Sharedo item to an offer header.
Columns:
| reference (nvarchar(200)) | Offer link/reference. |
| sharedoReference (nvarchar(200)) | Related Sharedo reference. |
| offerReference (nvarchar(200)) | Offer header reference. |
participantRelationships
Purpose: Stores relationships between participant roles, including policy/claim references.
Columns:
| reference (nvarchar(200)) | Relationship record reference. |
| leftSharedoParticipantRoleReference (nvarchar(200)) | Left role reference. |
| leftSharedoParticipantRoleType (nvarchar(200)) | Left role type. |
| rightSharedoParticipantRoleReference (nvarchar(200)) | Right role reference. |
| rightSharedoParticipantRoleType (nvarchar(200)) | Right role type. |
| sharedoTypeRoleAssociationSystemName (nvarchar(200)) | Association type/system name. |
| createdDate (datetime) | Creation timestamp. |
| policyNumber (nvarchar(100)) | Policy number, if applicable. |
| claimNumber (nvarchar(100)) | Claim number, if applicable. |
participantRoles
Purpose: Captures participants and their roles against a ShareDo item, with optional address linkage.
Columns:
| reference (nvarchar(200)) | Participant role record reference. |
| odsReference (nvarchar(200)) | ODS reference of the participant. |
| odsType (nvarchar(250)) | Type of ODS entity (person, org, user). |
| participantRoleSystemName (nvarchar(200)) | Role system name (e.g., claimant, defendant). |
| sharedoReference (nvarchar(200)) | Related Sharedo reference. |
| customReference (nvarchar(100)) | Custom reference or label. |
| addressReference (nvarchar(200)) | Linked address reference. |
| addressType (int) | Address type indicator. |
payments
Purpose: Stores payment records, including type, date, and payor associations.
Columns:
| reference (nvarchar(200)) | Payment record reference. |
| paymentTypeCode (nvarchar(500)) | Payment type code. |
| dateOfPayment (datetime) | Date/time of payment. |
| paymentDetails (nvarchar(max)) | Details or narrative of the payment. |
| parentSharedoReference (nvarchar(200)) | Parent Sharedo (e.g., matter or request) reference. |
| payorOdsReference (nvarchar(200)) | Payor ODS reference. |
paymentRequestAmounts
Purpose: Captures requested payment amounts and tax details for a payment request work item.
Columns:
| reference (nvarchar(200)) | Payment request amount record reference. |
| sharedoReference (nvarchar(200)) | Related payment request Sharedo reference. |
| reserveTypeCode (nvarchar(500)) | Reserve type/code. |
| amount (decimal(18,2)) | Amount requested. |
| reserveReason (nvarchar(max)) | Reason for the reserve/amount. |
| transactionItemType (nvarchar(200)) | Category/type of item. |
| taxAmount (decimal(18,2)) | Tax amount. |
| taxRate (decimal(18,2)) | Tax rate applied. |
phaseHistory
Purpose: Captures the phase changes for the work item, starting with the first transition that has a null 'from' and with the latest that should match the current phase in source.sharedo
Note: While the phase system names must match configuration, the transitions don't need to and can reflect the data available in the source system. Contact us for more detailed guidance and Q&A.
Columns:
| reference NVARCHAR(200) | Primary Key |
| fromPhaseSystemName NVARCHAR(200) | May be null if this is the first phase transition. Systemname matches config noting it may be an inherited phase plan. |
| toPhaseSystemName NVARCHAR(200) | Systemname matches config noting it may be an inherited phase plan. The last (by date) of the phase transitions should have a to phase that matches the value in the sharedo table. |
| transitionDateTime DATETIME | UTC, should be for a distinct datetime from other transitions for the same sharedo |
| userReference NVARCHAR(100) | Reference to the user, typically is a user in a primary ownership role |
| description NVARCHAR(MAX) | Optional text |
| transitionReason NVARCHAR(500) | Optional text |
| sharedoReference NVARCHAR(200) | The work item for which this set of phase history rows are for |
sharedoRelationships
Purpose: allows creation of arbitrary relationships between sharedos (work items), the type of link is configured in Modeller. This table is used in two scenarios - that of relating sharedos for mutual reference (left and right) and directly relating in a parent child relationship.
Columns:
| reference NVARCHAR(200) | Primary Key |
| leftSharedoReference NVARCHAR(200) | The related ‘left’ or ancestor/parent reference |
| rightSharedoReference NVARCHAR(200) | The related ‘right’ or descendant/child reference |
| sharedoRelationshipTypeSystemName NVARCHAR(200) | Either ‘parent-child’ OR a systemName from the modeller configuration for Work Type Relationships. |
tasks
Purpose: Can contain a record of both completed tasks and open tasks. Tasks are work items, so ownership and visibility (to team members) are via participantRole rows.
Note: The task type can be anything valid, though the most common is to use the base type of systemName 'task'. Form fields, comments etc. can also be data loaded as the task is a sharedo (work item).
Columns:
| reference NVARCHAR(200) | Unique key, same value as the sharedoReference |
| sharedoReference NVARCHAR(200) | Primary Key, the reference into the sharedo table for this item |
| parentSharedoReference NVARCHAR(200) | The parent of the task, e.g. a matter |
| dueDateTime DATETIME | UTC due date |
| taskContent NVARCHAR(MAX) | Optional text |
timeEntries
Purpose: The timeEntries table captures (typically billable) historic time information for a user.
Note: Time code configuration is available within Modeller. Please contact us for more detailed guidance and Q&A.
Columns:
| reference NVARCHAR(200) | Primary Key |
| sharedoReference NVARCHAR(200) | The work item / sharedo the time entry is for |
| odsReference NVARCHAR(200) | The user associated with the time entry |
| participantRoleReference NVARCHAR(200) | Optional, the participant the time entry relates to |
| startDateTime DATETIME | UTC start time |
| endDateTime DATETIME | UTC end time |
| billingNotes NVARCHAR(MAX) | Optional text |
| regenerateBillingNotes BIT | Boolean |
| categorySystemName NVARCHAR(200) | Category matching configuration |
| classificationSystemName NVARCHAR(200) | Classification matching configuration |
| isAutomatic BIT | Boolean |
timeEntrySegments
Purpose: The timeEntrySegments table captures (typically billable) historic time information for a user.
Note: Time code configuration is available within Modeller. Please contact us for more detailed guidance and Q&A.
Columns:
| reference NVARCHAR(200) | Primary Key |
| timeEntryReference NVARCHAR(200) | Parent time entry |
| timeCode NVARCHAR(200) | Code matching configuration |
| segmentValue NVARCHAR(500) | String |
Deprecated Tables
As the product evolves, tables may no longer be required as the corresponding data would be loaded via a different table design. If you are using these tables, you may need to contact us for guidance. The current list of deprecated tables is:
- feeEstimateSection
- feeEstimateElement
- settlement