App tables
Apps can create custom tables to store specific data.
Usage
In your app manifest.json, you can define new tables adding a app_tables property like this:
...
"app_tables": [
{
"name": "appx_myapp_table_1",
"description": "Example of extra table",
"shard_key": ["id"],
"unique_key": ["id"],
"sort_key": ["created_at"],
"columns": [
{
"name": "id",
"type": "varchar"
},
{
"name": "external_id",
"type": "varchar"
},
{
"name": "created_at",
"type": "datetime"
},
{
"name": "user_id",
"type": "varchar"
},
{
"name": "merged_from_user_id",
"type": "varchar"
},
{
"name": "fields_timestamp",
"type": "json"
},
{
"name": "db_created_at",
"type": "timestamp"
},
{
"name": "db_updated_at",
"type": "timestamp"
},
{
"name": "column_varchar",
"type": "varchar",
"size": 128,
"description": "Example of varchar column",
"is_required": false
},
{
"name": "column_number",
"type": "number",
"description": "Example of number column",
"is_required": false
},
{
"name": "column_boolean",
"type": "boolean",
"description": "Example of boolean column",
"is_required": false
},
{
"name": "column_datetime",
"type": "datetime",
"description": "Example of datetime column",
"is_required": false
},
{
"name": "column_json",
"type": "json",
"description": "Example of json column",
"is_required": false
}
],
"joins": [
{
"external_table": "appx_myapp_table_2",
"relationship": "one_to_many",
"local_column": "external_id",
"external_column": "table1_external_id"
}
],
"indexes": [
{
"name": "index_column_varchar",
"columns": ["column_varchar"]
}
]
}
]
...
The table columns should follow the Table Column schema.
Specific columns
Every app table should have the following columns:
...
"columns": [
{
"name": "id",
"type": "varchar"
},
{
"name": "external_id",
"type": "varchar"
},
{
"name": "created_at",
"type": "datetime"
},
{
"name": "fields_timestamp",
"type": "json"
},
{
"name": "db_created_at",
"type": "timestamp"
},
{
"name": "db_updated_at",
"type": "timestamp"
},
...
]
...
The id and external_id columns are used to identify the records.
The external_id is the ID of the record in your external system, and the id is a SHA1 hash of the external_id used by Rimdian.
The created_at column is used to track the creation of the records in your external system.
The fields_timestamp column is used to track the changes in the record fields.
The db_created_at and db_updated_at columns are used to track the creation and update of the records in the database.
User activity
In order to join the table records to the users activity, the user_id & merged_from_user_id columns should be added to the table.
When user profiles are merged, the user_id column is updated with the new user ID, and the merged_from_user_id column is updated with the old user ID.
...
"columns": [
...
{
"name": "user_id",
"type": "varchar",
"size": 64,
"description": "user_id",
"is_required": true,
"hide_in_analytics": true
},
{
"name": "merged_from_user_id",
"type": "varchar",
"size": 64,
"description": "merged_from_user_id",
"is_required": false,
"hide_in_analytics": true
},
...
]
...
Shard key
The shard_key is used to distribute the records across the database shards.
The records are grouped by the shard_key and stored in the same shard.
It should contain at least one column.
If you define a user_id column, it’s recommended to use it as the shard_key. This way the records are stored in the same shard as the user activity.
I.e:
...
"shard_key": ["user_id"],
...
Unique key
The unique_key is used to ensure that the records are unique in the table.
It should contain at least the columns used in the shard_key.
I.e:
...
"unique_key": ["id", "user_id"],
...
Sort key
The sort_key is used to store the records in the database in a specific order.
It should contain at least one column.
If the data is time-based, it’s recommended to use the created_at column as the sort_key. This way the records are stored in the database in the same order as they were created.
I.e:
...
"sort_key": ["created_at"],
...
Joins
By declaring relationships between the app tables and other tables, the Cube.js semantic layer can join the app tables with other tables.
...
"joins": [
{
"external_table": "appx_myapp_table_2",
"relationship": "one_to_many",
"local_column": "external_id",
"external_column": "table1_external_id"
}
]
...
Relationships can be defined as one_to_one, one_to_many or many_to_one. Read more about the Joins directions.
Indexes
You can define indexes to speed up your SQL queries.
Indexes are created as HASH indexes. It means it only works for equality comparisons.
...
"indexes": [
{
"name": "index_column_varchar",
"columns": ["column_varchar"]
}
]
...