Standard Columns

This section lists standard table columns which are used throughout the NEON schema.

Primary Key columns

All primary and foreign key column names end with _id.

'member_id'

• The primary key column of ng_member_tb.
• Used across all NEON tables to identify a Member.
• Defined as VARCHAR(15) CHARSET ascii COLLATE ascii_bin NOT NULL.
• Automatically and randomly generated by NEON when a Member is created.
• Cannot ever be changed or reused if/after a Member is deleted
• Consists of upper and lowercase letters, digits and underscore

'entity_id' (list_id, sheet_id etc)

• The primary key column of ng_entity_tb.
• Used across all of NEON to identify an Entity. When a table only deals with an Entity of a specific type, the entity_id column is renamed to represent the specific type of entity that it refers to, for example list_id, sheet_id, offer_id, orderset_id etc.
• Defined as VARCHAR(15) CHARSET ascii COLLATE ascii_bin NOT NULL.
• Can optionally be specified when an entity is created. Otherwise it gets generated automatically and randomly.
• Cannot ever be changed or reused if/after an Entity is deleted
• Consists of lowercase letters, digits and underscore. Always starts with underscore or a lowercase letter. This is to allow it to be used as objects within NEON scripts. Uppercase letters are not allowed. This is to be able to distinguish the the value from the Entity's name.
• Any database objects generated when an Entity is installed get their names from the Entity Id. For example if you create a Data List with the id 'profile', it will automatically generate a database table called ngdl_profile_tb.

Other primary keys

Some tables, especially log or activity tables, use an increasing integer or bigint as their primary key.

Those tables promise that new records always have a higher id number than existing previous ones. This is helpful when sorting or finding all new records since last execution etc.

Business related columns

NEON is designed to be integrated with and work well with your business. Both the APIs and the NEON tables therefore allow you to look up and process rows based on your own business identifiers. NEON also allows categorising rows and attaching arbitrary additional business information as needed.

The following standard columns are available:

'biz_id' (biz_order_id etc)

Short for Business Id. This acts as an alternative unique key which can be used to reference a Member, Entity, Order, Wallet transaction etc. This enables you to operate NEON based on your own standard identifiers, rather than having to expose of deal with NEON's internal generated identifiers.

• Optional, but if specified they must be unique. An error is returned if for example two Members are given the same business id.
• Up to 50 characters long, case sensitive, UTF8. Must not start with a dollar sign or underscore
• When querying the API based on biz_Id, the values have to be prefixed by the dollar sign, for example $myUniqueMemberId.

'biz_state'

An optional row state which can be used when NEON's own states aren't sufficient. It has no meaning to NEON.

• Optional, Up to 15 characters long, UTF8, case sensitive
• Can be added/updated/removed freely by the calling application, and is not validated or relied upon by NEON
• Searchable, including through wildcards and query expressions

'category'

All Members, Entities, Order lines, Wallet transactions etc can be categorized as per your business needs.

The field is always called category.

• Optional, up to 50 characters long, UTF8, case insensitive
• Can be added/updated/removed freely by the calling application, and is not validated or relied upon by NEON
• Searchable, including through wildcards and query expressions.
• Can be referenced in scripts.

'data'

This is a JSON column which allows a calling application to store any type of Json data together with transaction rows.

• The Json must be a structure { "field": value, ... }, and it's expected to only hold one level. Deeper structures can be specified, but they can't be used in scripts or searched through the NEON API.
• UTF8 and case sensitive.
• Can be added/updated/removed freely by the calling application, and is not validated or relied upon by NEON
• Can be referenced in scripts

'dimensions'

All transactions can be decorated with one or more dimensions. Dimensions differ from the other columns in this section because they do play a central role in NEON and are therefore validated.

Standard NEON columns

This section describes some of NEON's standard columns, which are found in many NEON tables and also within Json Entity configurations.

'created' (DATETIME(3))

This mandatory columns holds the timestamp when the row was created.

It is set automatically when the row is created (must not be specified in the insert statement).

It must never be updated. This is important since jobs and other functionality depend on being able to find new rows being added based on the created timestamp.

'updated' (DATETIME(3))

This mandatory column holds the timestamp of when the row was last updated.

Is it set to the same value as the created column when the row is created, and is updated automatically every time the row is updated, to show when the row was last changed.

It should never be set manually in insert or update statements.. This is important since jobs and other functionality depend on being able to find rows that have been updated based on the updated timestamp.

📘

NOTE that if a record is updated to the same values as before then NEON may or may not modify the updated column.

'type' and 'sub_type'

The 'type' field is used within NEON to identify the type of the row.

• Always case sensitive and mandatory, declared as VARCHAR(15) CHARSET ascii COLLATE ascii_bin NOT NULL
• The value is set on insert, and only accepts specific pre-defined values that are understood by NEON.
• It must never be modified. Changing this value will cause functionality and configurations within NEON to fail.
• If the clients want to type rows themselves, they can use the category column.

Some types also have optional sub types. For example the LIST Entity type has sub-types declaring what type of List it is, for example TAG, DATA, COUNTER etc. The same rules apply to the sub_type column as to the type column. The value must never be modified.

'state' and '*_state'

The state column is used within NEON to keep track of the state of a row.

• Always case sensitive and mandatory, declared as VARCHAR(15) CHARSET ascii COLLATE ascii_bin NOT NULL
• The initial state is set on insert, and only accepts specific pre-defined values that are understood by NEON.
• The state value will change over the life of the row based on API calls and internal NEON logic. It should not be modified directly by SQL, as this may cause inconsistencies. An example is the Entity state - When calling the API to change the state from DRAFT to INSTALLED, the API will validate the configuration, create new database objects, set up security etc.
• Clients should instead use the biz_state column to keep track of business related states.

'name' (VARCHAR(100))

This can be viewed as an editable primay key.

• A unique internal name that can be given to Entities and used instead of the Entity id.
• The name is mandatory, case sensitive and up to 100 characters.
• It needs to start with a letter and can only contain letters, digits and underscore. It must contain at least one uppercase character (to distinguish it from entity id in searches).
• It can optionally be prefixed with @ when used to look up rows through the API, or when used to reference Entities' tables in SQL queries.
• The name can be changed any time, but doing so risks breaking API calls or SQL statements that rely on it.

'dimensions' and 'dimcomb_id'

Dimensions are stored together with transactional data, such as Members, Orders, Wallet transactions etc.

The 'Dimensions' columns are always stored as JSON and are never mandatory. They contain a flat key-value structure of those dimension values that have been set. No empty string or null dimension values are allowed.

When dimensions need to be used as primary keys (normally only in reporting and statistical tables), the JSON gets converted into a fixed string representation and stored in a column called dimcomb_id.

There are stored functions available for searching and converting between dimcomb_id and dimensions values.