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.

App tables are removed from the database when the apps are deleted.

​
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"]
        }
    ]
    ...
Extra columnsCube schemas