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"

The 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

• Sales Header detail about each sale
• Salelines Holds individual item records on a sale. Child rows of sales.
• Payments Holds individual payments on a sale. Child rows of sales.
• EftposReceipts Log of eftpos payments on a sale. Child rows of sales.

Major Reference Tables

• Barcodes Known barcodes lookup table
• Pricemaps Conditional pricing reference data (sets base price)
• PriceOptions Conditional pricing reference data (defines discounts and special offers)
• QtyDiscounts Volume and time based pricing for a single product

Supporting Tables

• Locations Master information about stores and warehouses..
• NetworkLane Information about each lane seen in the system and holds links to which location it resides.
• Timeschedule Holds details of PosCommands to be executed on a given repeating time.
• TushLog Log of System Reference keys
• CredLog Log of Credential authorisations used
• SaleLog Log of major actions performed against sales as a whole. Data in this table is interesting to auditors and support staff.
• SaleChangesAfterComplete Information about a sale that was applied after the sale was completed.

Analysis Tables

• Customersdcid Records details of derived customers
• Salesdcid Records links between sales and derived customers

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.

• Normal Sales Header detail about each normal completed sale. When a sale is completed normally, rather than void, it is written to this table
• Business Transactions Summary list of all business level transactions received. A business transaction is a high level operation performed by the POS, such as "create a sale", "edit a customer", "redeem a voucher".

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 Name	Description
   rid	Replicaton id. Used internally
   ridutc	Replication id based in UTC. Used internally
   physkey	Physical key. A globally unique value identifying this exact row
   lane	Set to the current systems lane number
   location	Set to the current systems location number
   tid	Teller Id#
   Rvv	Record Version Vector. Used by distributed transactions to assist with edit conflicts
   Rcs	Row Checksum. Placed on rows to allow external changes to be detected.
   Tushi	The system reference key of the implementor for a row
   Tusho	The 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

• A single byte (character) indicating the encoding for this RVV.
• If the encoding byte is 'a', then the remaining data is a list of unsigned 32 bit numbers.
• If the encoding byte is 'b', then the remaining data is a list of unsigned 32 bit numbers, where each number is printed using 8 hex characters.

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

• Current System Name (ComputerName)
• Current IP address
• Computer manufacturer and model
• System serial number
• Harddrive serial number
• Processor identification numbers
• Network MAC addresses.
• Neighbouring or source MAC addresses
• Browser User-Agent
• RmSystem keys

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.

Name	Base Table	Base Field	Data	Brief Description
SalePhase	Sales	Phase	XML JSON Excel	Overall current state of sale, active, hold, complete.
SalesType	Sales	salestype	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
GihCode	goodsinhistory	code	XML JSON Excel	Meaning of codes used in GoodsInHistory historical records
PoState	poheader	state	XML JSON Excel	Status of a purchase order
BarcodeSymbologies	barcodes	symbology	XML JSON Excel	Codes defining the symbology of a barcode
MessageSrc	messageq	gensrc	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