IDs, history tables, timestamps, etc¶

• Table of contents
• IDs, history tables, timestamps, etc
  • ID formats
  • History tables
  • Timestamps

ID formats¶

Object instances in the database have unique IDs. All IDs internally generated by the web service, e.g. voucher-ID, user-ID, transaction-ID, JR-ID, etc., are all to be treated as opaque strings, not as integers.

The rules for ID syntax are:

• opaque, meaningless string
• between 1 and 50 octets long, variable length
• one word, i.e. no spaces or special characters in the word, only letters and digits
• case sensitive
• may start with a digit or a letter
• equality test possible, i.e. if two ID strings are identical as strings and refer to the objects of the same class, then they refer to the same instance
• guaranteed unique, i.e. if two object instances of the same class exist in the system, they are guaranteed to have two separate ID strings
• unique in time, i.e. an object ID is never re-used
• ordering is meaningless, i.e. "greater than" or "less than" operation is meaningless

Note that this means that the web service may use integers as IDs, and all these rules will be fulfilled. But while representing them in JSON, the IDs will always be enclosed in double-quotes, even if they "look like" integers.

Examples of IDs:

• "a0ae48b16f90db8f3c542f44f8103701"
• "5"
• "750294852039"

Examples of strings which are not IDs:

• "a0ae48b16f90db8f3c542f4/f8103701" (there's a special character in it)
• 5 (a digit without quotes is not a string)
• "776,245,664" (commas are not permitted in IDs)
• "62.6" (the period is not permitted)

History tables¶

Transaction tables which receive updates will also have history tables which "shadow" the main tables. This will ensure that, taken in an overall database context, all updates are non-destructive, because all before-images of records can be reconstructed by analysing the history tables.

History tables will contain:

• previous images of updated records
• deleted records

The following policies will be applied for maintenance of history information:

• When a record is inserted in a main table, nothing is done to the history table. (This means that there is no history-related overhead of compute power or disk space for tables which are not modified much.)
• When a record is updated in a main table, its previous image is first inserted into the history table and then the data is updated in the main table
• When a record is physically deleted (i.e. using the SQL DELETE operation) from a main table, the to-be-deleted record is first copied into the history table
• When truncate operation is performed on the table, all the rows in the current table will be inserted to its history table

These are the implementation specifications for implementing the history feature:

• All main tables which are supposed to get the benefit of history tracking will now have corresponding history tables to "shadow" them. If the main table is called X, its shadow table will be called X_hist.
• The history maintenance feature will be implemented using database triggers.

• The shadow table will have identical schema to the main table, with two additional fields:

  • hist_when: timestamp specifying when this record was inserted in the shadow table
  • hist_op: will contain either "U", "D" or "T", indicating that this history replica is because of an u(pdate), d(eletion) or t(runcated)

• The server-side application code will connect to the database using a specific database username and password meant for business operation. (Let's say that this username is appcode.) This username will not have any rights on the shadow tables other than INSERT rights. A separate, second database user (let's say, apphist) will have SELECT rights on the shadow table, for reports. And only the administrator will be able to modify the contents of shadow tables.

There are ready-made examples on the Net about how to implement this sort of history table with a Postgres database. One well-known example is https://wiki.postgresql.org/wiki/Audit_trigger Most of these examples actually use a single history table to store before-images from all primary tables. That approach works well too -- you don't need to have separate shadow tables for each primary table. In particular, a single combined history table is easily implemented in Postgres because of its powerful jsonb column type, which allows us to store any random data structure in one column, thus giving us flexible data structure storage.

Timestamps¶

As per JSON Schema specification for date-time, for timestamps values, we will always use the format defined in RFC 3339 e.g. 1990-12-31T23:59:50Z for a global UTC timezone timestamp or 2015-12-31T23:59:50+05:30 for a timezone-included timestamp. All timestamp information in web service parameters will be communicated in these formats. If an application is used by clients across various timezones, all timestamps sent to or received from web services must be in UTC, and must be stored on the servers in UTC. All conversion of those timestamps to local timezones to make it easier for humans to read, for instance, must be done in the client software.