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.
- 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)
- 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.
- 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.
- 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.
- If you wish to add tables to the database for your own purposes, these table names must also start with the letter 'U'.
- 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.
- 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.
- 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'.
- 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.
- 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
- 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.
- 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
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".
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.
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
|Name||Base Table||Base Field||Data||Brief Description|
||Overall current state of sale, active, hold, complete.
||Broad classification of sale, normal, online.
||Textual description of security rights names.
||Text descriptions for standard writeoff reason codes
||Meaning of codes used in GoodsInHistory historical records
||Status of a purchase order
||Codes defining the symbology of a barcode
||Codes in MessageQ table used to define type of communication requested.
||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
||Code descriptions for internal business transactions