Fieldpine Logo Documentation Home  

The database in Fieldpine POS is central to the complete system. While the structure is open to see and some parts documented there are a number of rules that must be followed in order to ensure system stability, now and in the future. The rules basically boil down to "play nice, and don't risk causing harm to the POS"

StructureThe Point of Sale has both a physical database layer (what you see in the database) and a logical layer (what the various APIs deal with).

If you are interfacing, you should in general be using the API interface (highlighted #1 on the picture) rather than the database. This is especially true if you wish to write or edit records, as only the API ensures accuracy, validation and audit tracking. Of course, creating high volume reports and some other functions are often easier with SQL database engines, so the physical structure is documented.

One of the main advantages of this seperation between API and physical database is it allows changes to the physical database over time without having to re-engineer customer specific interfaces. With this approach, tables and fields can be altered without affecting the APIs used by the POS itself and external applications

The rest of this document is talking about the physical SQL table layer (Highlighted #2 on the picture)

There are two types of tables in the SQL database, "physical" or internal tables which are the actual data used by the POS itself. These are the tables you should try and avoid. There are also "readonly" or "public" tables which contain extracts of live data for use in your external systems. You are strongly advised to use the readonly tables wherever possible.

Failure to follow these rules will result in no support, or charges for time spent.

  1. You are free to read the database as you see fit. You should however not configure high volume reading applications that would starve the system of resource for POS processing. Remember that you are solely responsible for ensuring accuracy of what you read. Reading the database directly is a low level interface and you need to handle all special cases, especially flags and row controls. The system also utilises pending transaction type tables, so facts might be not fully complete if pending transactions still exist. Some fields may be stored using encryption and/or obfustification techniques (these are documented when used)

  2. Do not write (meaning insert, update or delete) any field or table unless it is your own field or table. There are exceptions to this rule for specific cases. This rule is not meant to stop functionality and system integration, it is to ensure that undocumented updating does not occur.

    Any table that is replicated, regardless of its the name (ie, this applies to customer table names that start with the letter 'U') should not be altered in any way except using approved applications (Database utilities, ODBC and SQL are NOT approved applications). Fieldpine Systems use a distributed temporal database model, typically stored in either a relational backend or direct files. The side effect of this is that reading data directly from the underlying database may not be cache-coherent at the instant it is read or changed, and changes may be overwritten without warning by approved applications.

    The underlying transactional databases are physical representations of data, not logical facts.

    Fieldpine Systems are also increasingly required to conform to regulations around auditing, personal information security and finance reporting. (eg Tax Law, Personal data protection, Sarbanes-Oxley, Credit card security standards etc) Direct writing to the database without correct audit trails often violates these rules.

  3. The central POS servers are architected around being able to be shutdown for short periods of time (even during the day) without overly impacting trading operations. Do not design external applications that would not permit these shutdowns, they make operations and support much easier.

  4. If you wish to add columns to database tables, the names must start with the letter 'U' (for user). Keep in mind that Fieldpine POS is a distributed database system and remote databases may not be the same type or even from the same vendor, so not all datatypes may be available. Do not add more than around 50 columns to POS tables without written approval.

  5. If you wish to add tables to the database for your own purposes, these table names must also start with the letter 'U'.

  6. Do not use the "bit" storage method present in some databases. Define the field as a integer and use the boolean operator in POSMETADEF files to instruct the POS it is a boolean field. Do not use any other vendor specific datatypes for any column.

  7. Do not create unique indexes on column groups you believe to be unique, unless it is documented that it will be unique for all time. This also applies to triggers, constraints and other database level controls. If the tables are site specific tables that you have created you may of course define whatever indexing you wish on those tables.

    You may add indexes to support reporting performance as necessary.

  8. While you are free to create triggers and database external functions, you should try and avoid creating these that require remote systems to be functional. The intent here is to minimise the external system dependencies which would impact the POS. As Fieldpine do not use triggers within the POS (due to varying levels of support in different database engines); naming restrictions on triggers and functions only reserve certain prefixs of: 'trig' 'gds' 'gnap' 'fpos' and 'fdl'.

  9. This documentation of database structure is supplied as-is. Fieldpine reserve the right to change or add anything at any time, without warning.

The following physical schema information is available for reading. Not all fields or tables are documented for customer use.

Major Tables

Major Reference Tables

Supporting Tables

Analysis Tables

The following public or "readonly" tables are defined. Readonly tables are identified in the database as they include the literal text "readonly" in the table name

Readonly tables react according to what columns are present. If a column is defined, the system will populate it. This means you can define only those fields you are interested in and avoid storage space for other data. You can define as many columns as the database engine you are using will support.

  1. When using the database, Fieldpine software is able to dynamically handle column presence or not. If a column is present, Fieldpine will populate it, if not the storing to the database is silently ignored.
  2. Some columns are automatically populated by the database layers, as such there meaning is fixed regardless of which table they appear in.
    Field NameDescription
    ridReplicaton id. Used internally
    ridutcReplication id based in UTC. Used internally
    physkeyPhysical key. A globally unique value identifying this exact row
    laneSet to the current systems lane number
    locationSet to the current systems location number
    tidTeller Id#
    RvvRecord Version Vector. Used by distributed transactions to assist with edit conflicts
    RcsRow Checksum. Placed on rows to allow external changes to be detected.
    TushiThe system reference key of the implementor for a row
    TushoThe system reference key of the originator for a row
  3. Fieldpine uses a mesh distributed database. For more details about the distributed layer above the physical database, refer here

Row Checksumming

Some tables and versions may include an RCS column. If present, each time the system alters a record in the database it calculates a new RCS. This checksum allows the system to detect external changes to the database.

Record Version Vectors (RVV)

RVV values when present on a row are used for distributed transaction control. When a row is updated, and new RVV is generated and from the RVV value, another node receiving the RVV transaction can see if the update has already been applied or not.

An RVV consists of the following

An RVV string of 'b0000000100000002' is an RVV of (1,2) stored in Ascii format.

When a new row version is created, a random number is generated and added to the front of the RVV list. For example a new version of 'b0000000100000002' might create 'ba782cf940000000100000002'. When selecting a random number, the value 0 is never used, and any existing number is not repeated.

RVVs are typically a maximum of 5 numbers long and when they exceed this length any excess is dropped.

TUSH System References

The TUSH block and field is used to hold system identifying information. This information is useful for forensic analysis to show where data originated from in an authoritive form. The system is a system of record and information is collected in order to strongly indicate where data originated from.

The information in a TUSH block is not fixed, rather it is collection of fields that systems can voluntarily provide to assist, some examples of captured information is

The size of a TUSH block can be large, a "hardware fingerprint" is generated from the TUSH block. A hardware fingerprint is a alphanumeric value that encodes some parts of the TUSH. Hardware fingerprints can change on a computer under normal operation, although this is not super common.

The physkey for a TUSH record can be stored in tushi and tusho fields on some tables.

If your system has the table "tushlog" present, the system will attempt to store all new hardware fingerprint details as they are seen.

Reference Data

The POS uses indicator values in various tables and data structures. These are well known values that mean something specific. In general, the database does not include this reference data as the POS itself does not need it, however, it is often required if using external reporting tools.

NameBase TableBase FieldDataBrief Description
SalePhaseSalesPhase XML JSON Excel Overall current state of sale, active, hold, complete.
SalesTypeSalessalestype XML JSON Excel Broad classification of sale, normal, online.
RightsName XML JSON Excel Textual description of security rights names.
WriteOffCodes XML JSON Excel Text descriptions for standard writeoff reason codes
GihCodegoodsinhistorycode XML JSON Excel Meaning of codes used in GoodsInHistory historical records
PoStatepoheaderstate XML JSON Excel Status of a purchase order
BarcodeSymbologiesbarcodessymbology XML JSON Excel Codes defining the symbology of a barcode
MessageSrcmessageqgensrc XML JSON Excel Codes in MessageQ table used to define type of communication requested.
Flow XML JSON Code Flow Points for technical support. Flow must be fetched by value, a complete download is not possible. This API is designed for automatic access by reporting tools
TUBT XML JSON Code descriptions for internal business transactions