xrmverse
Documentation menu

Tables in every organization

← xrmverse documentation

Besides the system tables, every organization is seeded with account (object type code 1) and contact (2), user-owned, with activities and notes enabled and the Dataverse logical names, so that solutions and code written for Dataverse find them. Columns of account: name (primary), accountnumber, telephone1, emailaddress1, websiteurl, address1_line1, address1_city, address1_postalcode, address1_country, parentaccountid (lookup to account) and primarycontactid (lookup to contact). Columns of contact: firstname, middlename, lastname, fullname, emailaddress1, telephone1, mobilephone, jobtitle, the same four address columns and parentcustomerid, a Customer column (a lookup to account or contact, with one relationship per target). The tables are not customizable beyond what any system table allows: a column you add needs a publisher prefix (see Publishers and the prefix rule), and a solution that adds one exports account as a system table that carries only the added columns.

Every organization also has the read-only audit table (object type code 4567, entity set audits, no owner), where the audit rows are written when auditing is switched on; see Auditing.

Field-level security adds five organization-owned tables (see Field-level security): fieldsecurityprofile (entity set fieldsecurityprofiles, object type code 1200), fieldpermission (fieldpermissions, 1201), the intersects systemuserprofiles (systemuserprofilescollection, 1202) and teamprofiles (teamprofilescollection, 1203), and principalobjectattributeaccess (principalobjectattributeaccessset, 44). The type codes are from memory and not verified. Every organization holds the read-only System Administrator profile.

Custom API definitions use three user-owned tables, written by authoring or by solution import and delete (see Custom APIs): customapi (entity set customapis, object type code 9990), customapirequestparameter (customapirequestparameters, 9991) and customapiresponseproperty (customapiresponseproperties, 9992). The type codes are xrmverse's own (Dataverse assigns these tables a code of 10000 or more per environment). In every organization the Basic User role reads them at Global depth and System Customizer holds every privilege on them; an organization created before them gets these grants once at startup.

Webhooks (see Webhooks) add the organization-owned table serviceendpoint (entity set serviceendpoints, object type code 4618): serviceendpointid, name (String 256, required), description, url (String 2000), contract (choice, only 8 Webhook is accepted), authtype (choice, only 4, 5 and 6), the write-only authvalue, the read-only isauthvalueset, messageformat, messagecharset, connectionmode, userclaim, namespaceaddress, path, solutionnamespace and saskeyname. The authvalue is not a column of the table: it is stored in the metadata table meta.serviceendpoint_secret (created by the migration 0012_service_endpoint_secrets.sql), so that no read path can return it. sdkmessageprocessingstep.eventhandler is a polymorphic lookup to plugintype or serviceendpoint, with the navigation properties eventhandler_plugintype and eventhandler_serviceendpoint. System Customizer holds every privilege on serviceendpoint and AppendTo; Basic User holds none. An organization seeded by an earlier version gets the table, its privileges and the new lookup target at startup.

systemuser.applicationid (Uniqueidentifier, schema ApplicationId) is the app id of an application user, as in Dataverse: null for interactive users, written by whoever creates the application user (Web API, SDK); xrmverse never fills it from a token, and the local identity provider binds a confidential client to its user without it. An organization seeded by an earlier version gets the column at startup (#122).

userentityuisettings (user-owned, entity set userentityuisettingsset, object type code 2500, key userentityuisettingsid) keeps, per user and table (objecttypecode, the table's logical name), the last main form the user chose (lastviewedformxml: <MRUForm><Form Type="Main" Id="{form id}" /></MRUForm>), so the model-driven form opens on it again in any browser. Its other Dataverse columns (tab order, recently viewed records, lookup history) are not modelled. Basic User creates, reads and writes its own rows (Basic depth). An organization seeded by an earlier version gets the table and the grants at startup (#119).

Hierarchy security (see Security) adds these columns: on organization, ishierarchicalsecuritymodelenabled (Boolean, default false), maxdepthforhierarchicalsecuritymodel (Integer, default 3, 1 to 100) and usepositionhierarchy (Boolean, default false, true refused); on systemuser, the lookup parentsystemuserid (manager, schema ParentSystemUserId) with the hierarchical relationship user_parent_user (Delete RemoveLink, the other cascades NoCascade, from memory). account_parent_account is hierarchical too. The tables left out of hierarchy security are stored in meta.hierarchy_security_exclusion (a metadata table, not a Dataverse table). An organization seeded by an earlier version gets the columns, their defaults and the IsHierarchical flags at startup.

Access teams (see Security) add the organization-owned table teamtemplate (entity set teamtemplates, object type code 92, from memory): teamtemplateid, teamtemplatename (String 100, required), objecttypecode (Integer, required: the object type code of the table), defaultaccessrightsmask (Integer, required), description (Memo 2000) and the read-only issystem. Its privileges are prvCreateTeamTemplate, prvReadTeamTemplate, prvWriteTeamTemplate, prvDeleteTeamTemplate, prvAppendTeamTemplate and prvAppendToTeamTemplate (Global depth), held by the System Administrator role only. team gets three columns: teamtemplateid (Lookup to teamtemplate, relationship teamtemplate_Teams, Delete Cascade, from memory), regardingobjectid (a Lookup to the record of a table switched on for access teams, with one relationship team_regardingobjectid_<table> per table, derived names) and systemmanaged (Boolean, default false). An organization seeded by an earlier version gets the table and the columns at startup.

Change tracking (see Change tracking) adds one column to organization, expirechangetrackingindays (Integer, required, 0 to 365, default 7, the number of days the log of deleted rows is kept; a value outside the range is a 400), and three metadata tables in the meta schema (not Dataverse tables, created by the migration 0007_change_tracking.sql): meta.deleted_record (the log of deletions of tracked tables: a rowversion version, the table, the id and the deletion time), meta.change_tracking_table (one row per tracked table, with the version at which tracking started) and meta.change_tracking_state (one row: the version up to which the log was purged). An organization seeded by an earlier version gets the column with its default at startup.

Duplicate detection (see Duplicate detection) adds three tables, with object type codes from memory and not verified: duplicaterule (entity set duplicaterules, 4414, user-owned: name, baseentityname, matchingentityname, description, excludeinactiverecords, iscasesensitive, uniquename, the server-written baseentitytypecode, matchingentitytypecode, baseentitymatchcodetable and matchingentitymatchcodetable, and statecode 0 Inactive / 1 Active with statuscode 0 Unpublished / 1 Publishing / 2 Published; a new rule is 0/0), duplicaterulecondition (duplicateruleconditions, 4416, organization-owned in xrmverse so that it has privileges, where Learn says none: baseattributename, matchingattributename, operatorcode 0 to 6, operatorparam, ignoreblankvalues, regardingobjectid to the rule, uniquerulename) and duplicaterecord (duplicaterecords, 4415, primary key duplicateid, user-owned so that reads follow depth, where Learn says none: asyncoperationid, baserecordid, duplicaterecordid, duplicateruleid; written only by the bulk job). organization gets four Boolean columns, all default false: isduplicatedetectionenabled, isduplicatedetectionenabledforonlinecreateupdate, isduplicatedetectionenabledforimport and isduplicatedetectionenabledforofflinesync. asyncoperation.operationtype gains 7 (Duplicate Detection Rule Publish) and 8 (Bulk Duplicate Detection). Tables can carry the flag EntityMetadata.IsDuplicateDetectionEnabled. The migration 0009_duplicate_detection.sql creates the metadata table meta.matchcode_table (one row per published rule); the per-rule tables meta.matchcode_{ruleid} (objectid, matchcode binary(32)) are created when a rule is published and dropped when it is unpublished or deleted. An organization seeded by an earlier version gets the tables, the columns, the defaults and the System Customizer privileges at startup.

Currencies (see Currencies) add two tables and some columns. transactioncurrency (organization-owned, entity set transactioncurrencies, object type code 9105 from memory): currencyname (String 100), isocurrencycode (String 5, unique), currencysymbol (String 10), currencyprecision (Integer, 0 to 4), exchangerate (Decimal, 12 decimals, 0.000000000001 to 100000000000; the server accepts 0.0000000001 and up), currencytype (choice transactioncurrency_currencytype: 0 System, 1 Custom), statecode and statuscode (Active, Inactive). usersettings (user-owned, entity set usersettingscollection, object type code 150 from memory, key systemuserid) holds only transactioncurrencyid, the user's default currency (relationship transactioncurrency_usersettings). On organization: basecurrencyid (lookup to transactioncurrency, relationship basecurrency_organization, read-only after the seed), pricingdecimalprecision (Integer, 0 to 4, default 2), currencydisplayoption (choice, 0 symbol, 1 code, default 0), currencyformatcode (choice, 0 to 3, default 0) and the read-only basecurrencysymbol (String 5) and basecurrencyprecision. account and contact have the pair transactioncurrencyid (relationships transactioncurrency_account and transactioncurrency_contact, Delete Restrict) and exchangerate, and so does every table that gets a money column (relationship TransactionCurrency_<table>); each money column has a <name>_base. In every organization the Basic User role reads currencies at Global depth and can read, write and append to its own usersettings; creating, changing and deleting currencies stays with System Administrator. An organization seeded by an earlier version gets the tables, columns, base currency and grants at startup.

Activities and notes (see Activities and notes) add seven user-owned tables, except activityparty, which has no owner: activitypointer (entity set activitypointers, object type code 4200), a read-only copy of the common columns of every activity that the server writes; activityparty (activityparties, 135), one row per participant of an activity (activityid, partyid to account, contact or systemuser, participationtypemask, addressused, ispartydeleted), read-only; the activity tables email (emails, 4202), task (tasks, 4212), phonecall (phonecalls, 4210) and appointment (appointments, 4201); and annotation (annotations, 5), the notes, whose documentbody lives in the file store. The type codes are from memory. On systemuser: internalemailaddress (String 100, the address a user gets as a party). On organization: maxuploadfilesize (Integer, required, 0 to 131072000, default 5242880, the largest note body in bytes; the range is from memory). account and contact are targets of regardingobjectid and annotation.objectid with the relationships Account_ActivityPointers, Account_Emails, …, Account_Annotation and their Contact_ counterparts, and the four activity tables have notes. Every role's activity privileges are the shared prv…Activity set and its note privileges prv…Note; Basic User holds both at Basic depth. An organization seeded by an earlier version gets the tables, columns, defaults, relationships and grants at startup; a custom table named like one of the seven stops the server with the same message as for account (below).

Classic workflows (see Classic workflows) add two tables, with object type codes from memory and not verified. workflow (entity set workflows, object type code 4703, user-owned, primary name name): uniquename, description, category (0 Workflow, 1 Dialog, 2 Business Rule, 3 Action, 4 Business Process Flow, 5 Modern Flow, 6 Desktop Flow, 7 AI Flow), mode (0 Background, 1 Real-time), type (1 Definition, 2 Activation, 3 Template), scope (1 User, 2 Business Unit, 3 Parent: Child Business Units, 4 Organization), primaryentity (String 100, the table's logical name, none for a global action), xaml, ondemand, subprocess, triggeroncreate, triggerondelete, triggeronupdateattributelist (comma-separated column names), asyncautodelete, syncworkflowlogonfailure, istransacted, createstage, updatestage, deletestage (20 Pre-operation, 40 Post-operation), runas (0 Owner, 1 Calling User), processtriggerscope (1 Form, 2 Entity, 3 All Forms; 3 is unconfirmed), rank, parentworkflowid and activeworkflowid (lookups to workflow, relationships workflow_parent_workflow, Delete Cascade, and workflow_active_workflow, Delete RemoveLink), sdkmessageid (an action's message), inputparameters, introducedversion, iscustomizable, ismanaged, the server-written xrmverse_unsupportedsteps (the steps xrmverse does not run, one per line) and xrmverse_componentxml (the element of the imported file, kept for export), and statecode 0 Draft / 1 Activated with statuscode 1 Draft / 2 Activated (other statuses are a 400). processtrigger (processtriggers, 4712, organization-owned): processid (relationship process_processtrigger, Delete Cascade), primaryentitytypecode (String 100), event, controlname, controltype (1 Attribute, 2 Form Tab), formid, scope (1 Form, 2 Entity), and the stored-only pipelinestage and methodid. processtrigger is written only by solution import and by activation (a public Create, Update or Delete is a 400). workflow is also written by authoring: a public Create or Update of a Draft definition is accepted when its XAML parses, validates and has no unsupported step, and xrmverse_unsupportedsteps is still server-written only. An Update re-validates the XAML only when it sends a definition column (the XAML, mode, trigger or stage columns), so an imported Draft with unsupported steps, or of category 1, 4 or 5, can still be renamed. (See Workflow authoring as code and Classic workflows). asyncoperation uses operationtype 10 (Workflow) for background workflow jobs and has the lookup workflowactivationid (relationship Workflow_AsyncOperations, Delete RemoveLink). The migration 0013_workflows.sql creates the metadata table meta.workflow_wait (one row per job waiting on a record: asyncoperationid, entity, objectid, wake_at, seen_version). email gains five columns that a workflow's send email step sets: correlatedsubjectchanged, followemailuserpreference, isduplicatesenderunresolved (Boolean, default false), deliveryprioritycode (0 Low, 1 Normal, 2 High, default 1) and notifications (0 None, 1 and 2, default 0), as local choices; emails that existed before read NULL in them. System Customizer holds every privilege on workflow and processtrigger at Global. An organization seeded by an earlier version gets the tables, the columns and the grants at startup.

  • fullname is computed on Create and Update from the first, middle and last name, following the organization's fullnameconventioncode (0 to 7, default 0: "Last, First"; 1: "First Last"). A fullname sent on Create is a 400; on Update the value that is already stored is ignored and a different one is a 400. Conventions 2 and 3 (the middle initial is written "M.") and 6 and 7 follow our reading of the labels in the Microsoft documentation and are not verified against a real Dataverse. Changing fullnameconventioncode does not recompute the contacts that exist.
  • Phones. telephone1 and mobilephone have the format Text, as in the Microsoft documentation; the FormatName value Phone is not modelled.
  • Existing organizations. The tables are created at startup. If an organization already has a custom table named account or contact, the server does not start: it logs Organization '<org>' already has a custom table named '<table>' ... Rename or delete the custom table, then restart the server.
  • Relationships and their cascades, with the names and the CascadeConfiguration of the Microsoft documentation: contact_customer_accounts (Delete Cascade: deleting an account deletes its contacts; Assign, Merge, Reparent, Share and Unshare Cascade), contact_customer_contacts (Delete RemoveLink: the contacts under a deleted contact lose their parentcustomerid; Assign, Merge, Reparent, Share and Unshare Cascade), account_parent_account (lookup parentaccountid, Delete RemoveLink; Assign, Merge, Reparent, Share and Unshare Cascade) and account_primary_contact (lookup primarycontactid, Delete RemoveLink: deleting a contact clears the primarycontactid of the accounts; Merge Cascade). Archive and RollupView are NoCascade. Only the Delete cascades are enforced (see "Delete cascades" in the Solution section); the others are stored and reported, not applied. An organization seeded by an earlier version gets these cascades at startup (its saved relationships keep their ids); the account lookups, which had no saved relationship and the generated names account_parentaccountid_account and account_primarycontactid_contact, now carry the Dataverse names (nothing stored referred to the generated ones; the collection-valued navigation properties of account and contact change name accordingly).
  • Customer columns. A Customer column is created only with CreateCustomerRelationships (see the limits of the Solution section for the navigation properties and $expand). CreateCustomerRelationships is not declared in $metadata, and $expand of a Customer column is a 400.

The manual acceptance checklist is docs/superpowers/acceptance/accounts-contacts-checklist.md.