Crux database schema - remiges-tech/crux GitHub Wiki

These are the database tables the Crux service uses.

These will need to be in a database which can be accessed from multiple Crux services running on multiple servers, therefore a network-wide database is required.

All timestamps will be in UTC, in case you haven't read our standards documents.

realm: the master table of realms

Note that there is no web service call to manipulate this table's records. Records appear and disappear here by magic.

This table will have:

  • id: an automatically incremented mandatory unique integer
  • shortname: mandatory, unique, a one-word string, always in lower-case, following the syntactic rules of identifiers in modern PL
  • shortnamelc: mandatory, a fully lower-case version of the value in shortname, useful for indexing and case insensitive searches
  • longname: mandatory, a descriptive string
  • setby: mandatory, string
  • setat: mandatory, timestamp
  • payload: mandatory, JSONB, carrying all sorts of information about the history and geography of this realm. Crux does not use this information, other than to engage with the IMS, which is the master module for realms.

app: the master table of apps

This table will have:

  • id: an automatically incremented mandatory unique integer
  • realm: the value of the realm.shortname column to which this app belongs
  • shortname: mandatory, a one-word string, following the syntactic rules of identifiers in modern PL
  • shortnamelc: mandatory, a full lower-case version of shortname, which can be indexed and used for case insensitive matching
  • longname: mandatory, a descriptive string
  • setby: mandatory, string
  • setat: mandatory, timestamp

realmslice: containers for rulesets and associated data

This table will contain:

  • id: automatically incremented mandatory unique integer
  • realm: the value of the realm.shortname column to which this slice belongs
  • descr: string holding a string for human reference. Not processed by Crux.
  • active: boolean, not NULL
  • activateat: timestamp, may be NULL
  • deactivateat: timestamp, may be null

A record will never have both activateat and deactivateat holding values at the same time. This is better explained in the specs for the web service calls realmslice_activate() and realmslice_deactivate().

One record will be added to this table at the time each new realm is created. All realms are created with one slice each.

config: editable configuration attributes

This table will have

  • realm: mandatory, string, the realm ID
  • slice: integer, value from realmslice.id, mandatory, indicates the slice to which this record belongs
  • name: a one-word string, all caps. Mandatory.
  • descr: string, human readable description of what this attribute signifies
  • val: string, the value of this attribute. May be NULL initially.
  • ver: 32-bit integer, will be auto-incremented by the system, starting with 1
  • setby: string, the user ID of the user who last set the value of this attribute. Will carry the reserved word SYSTEM if no human user created this entry.
  • setat: timestamp, the timestamp in UTC of the last configSet() on this attribute

The (realm, slice, name) tuple will be unique.

The table will be populated with records at the system initialisation time, and new records will be inserted by the system every time a new realm is added, but some of the records may have val == NULL. Subsequent configSet().

capgrant: user-capability mappings

This table will have:

  • id: mandatory, internally generated auto-incrementing integer, unique key
  • realm: mandatory, string, the realm ID
  • user: mandatory, the user ID who owns the capability. Always lower-case.
  • app: the app name, in case this capability is an app-level one. Will be NULL for realm-level capabilities. Will be lower-case.
  • cap: mandatory, string, the capability name. Always lower-case.
  • from: timestamp, may be NULL. If present, it indicates a start time after which this record will be active.
  • to: timestamp, may be NULL. If present, it indicates an expiry timestamp for this record.
  • setat: mandatory, timestamp, the time at which the capGrant() was called
  • setby: mandatory, user ID of the user who did the capGrant() call. Always lower-case.

Any revocations of cap-grants must be reported in full to the data-change log, reproducing the entire record which is being deleted, with all its fields.

Just having an isdeleted field may not be good enough for this table, since records will keep getting added and removed here, and very similar records may get added today, removed next week, and added again next month. But if we all feel that the isdeleted flag is a good idea, we can do that too.

If we use the isdeleted approach to record deletion, then the tuple (realm, user, app, cap, setat) will be a unique key. If not, then (realm, user, app, cap) will be a unique key. This means that the system cannot remember multiple capability grants for the same tuple of (realm, user, app, cap) with different values of from and to. The system does not allow you to "queue" capability grants for multiple different slices of time in the future for the same (user, app, cap) tuple.

deactivated: all deactivated things

This table will have:

  • id: mandatory, unique, auto-incremented integer
  • realm: mandatory, string, the realm ID
  • user: optional, string, the user ID of a deactivated user, may be NULL for realm-level deactivation
  • deactby: mandatory, string, the user ID which called the deactivate() or userDeactivate() call
  • deactat: mandatory, timestamp, the time at which the deactivation happened

Both deactivate() and userDeactivate() will create records in this table; activate() and userActivate() will remove them.

schema: table of all schema

This table will contain

  • id: mandatory, unique, internally generated auto-incremented integer
  • realm: mandatory, string, the realm ID
  • slice: mandatory, integer, the value of realmslice.id from a record in realmslice
  • app: mandatory, string, the app name. Lower-case.
  • BRWF: mandatory, single char, B to indicate that this is a business rules schema, W to indicate that this is a workflow schema
  • class: mandatory, string, the name of a class, strictly lower-case, syntactic rules of identifiers for modern PL
  • patternschema: mandatory, a JSONB block holding the pattern schema for the class
  • actionschema: mandatory, a JSONB block holding the action schema for the class
  • createdat, createdby, editedat, editedby: the usual

The tuple (realm,slice,app,class) will be unique in the table. The id field will also be unique, but will not be visible to the WSC client.

ruleset: table of rulesets

This table holds all rulesets for the business rules engine and all workflow specifications for the workflow engine. It will contain

  • id: mandatory, unique, internally generated auto-incremented integer
  • realm: mandatory, string, the realm ID
  • slice: mandatory, integer, the value of realmslice.id
  • app: mandatory, string, the app name. Lower-case.
  • BRWF: mandatory, single char, B to indicate that this is a business rules schema, W to indicate that this is a workflow schema
  • class: mandatory, string, the name of a class, strictly lower-case, syntactic rules of identifiers for modern PL.
  • setname: mandatory, string, the name of the ruleset, strictly lower-case, syntactic rules of identifiers for modern PL
  • schemaid: mandatory, integer, specifies the ID of the schema for this ruleset
  • is_active: boolean, always false when BRrulesetNew() creates a new ruleset
  • is_internal: mandatory, boolean. true indicates that this ruleset has not been defined by a human, but by the rule manager front-end. This flag has bearing only on the rule manager GUI operation.
  • ruleset: mandatory, a JSONB block with the ruleset as an array of rules
  • createdat, createdby, editedat, editedby: the usual

The value of class in any record must be present in the class column of schema in records which have the same values of realm, app, and BRWF as the corresponding record here.

wfinstance: active workflow instances

This table has one record for each workflow instance active or "live" in the system. When an entity starts on a journey to walk through a process, and begins executing a multi-step workflow, it needs to maintain its own state and remember which step it is executing. Once its current step is done, it needs to call the workflow engine to know what the next step will be. This remembering of the current half-complete step is done in this table.

For instance, if bank account creation is a multi-step workflow process, then each bank account which is currently undergoing creation is an active workflow instance, and each has completed zero or more steps and is working on its current, half-complete, step. So, each such workflow instance will have its record in wfinstance.

The table will contain

  • id: internal auto-incrementing integer, globally unique
  • entityid: mandatory, integer, identifies uniquely the entity which is walking through the workflow process. For instance, if bank account creation requires a workflow, then each half-created bank account application will have its own unique ID in this column.
  • slice: mandatory, the realmslice to which this workflow belongs
  • app: mandatory, the app under to which this workflow belongs
  • class: mandatory, the name of the workflow process this entity will execute
  • workflow: mandatory, the name of the workflow definition, i.e. the ruleset name, which this instance will step through
  • step: mandatory, string, will have one of the values listed in the actionschema.tasks of the class named in the record. This specifies the current step being executed for the entity or instance in the current workflow.
  • loggedat: timestamp, mandatory, will specify the timestamp at which this record was inserted
  • doneat: timestamp; will be null initially, will carry the timestamp of completion when the step completes
  • nextstep: mandatory, string, the name of the step which will be submitted to the matching engine for the next lookup, after this step is completed. This value only really has significance if multiple concurrent steps are currently being executed for this instance.
  • parent: the ID of the row which is the parent of this step

stepworkflow for when steps are themselves workflows

This table will contain a record only if a step which emerges out of a workflow is itself supposed to be executed in the form of a sequence of steps of a sub-workflow. Hence the name stepworkflow specifies those steps which are themselves implemented as workflows.

The table will contain

  • slice: mandatory: the usual
  • app: the app ID to which these workflows belong
  • step: the name of the step which emerges from certain workflows of this app, e.g. "aadhaarchk" or "bankclearance"
  • workflow: the name of the workflow to be traversed to execute the step named above