Create New Entity

Table of Contents

Overview
Pre-Requisites
Goal
Creating the Backend API
- Creating the Database Table
- Defining Permissions
Creating the Management System Workflow
Committing the Database Migration
Next steps

Overview

In this guide, you learn how to add a new entity type. It will show you how a new entity can be added to the backend API, and how to create the typical Explorer, Create, and Details stations for the Management System workflows of that entity.

Pre-Requisites

Make sure that you have completed the OTT template setup. You can find the downloadable package here.

Goal

When you open the Axinom Mosaic Management System, you currently see a view like this:

The goal of this guide is to create a new workflow that allows the management of Channels, including the needed sub-stations and backend APIs and add it as a tile to the home screen.

On a high-level the guide consists of two parts:

Creating the Backend APIs for the new entity
Creating the Stations in the Management UI that the editors will use

Creating the Backend API

First we’ll start with creating the backend API, so the stations we will implement in the second step will be able to load and store Channel data.

Creating the Database Table

To add a new entity type you start by creating a new table that will store all the data associated with our new Channels type.
To add a table, you first need to access the current.sql file. This file is located under services/media/service/migrations/current.sql.

Before we start adding SQL to that file, we’re going to give the migration that we’re about to create a name. To do this, replace the first line in the file with:

--! Message: channels-added

This name will be used when committing the migration to identify it.

Create the General Table Structure

You can manually write SQL to create the table or use a Visual Studio Code snippet that is part of the template. Follow the steps below to easily generate the general table structure, database constraints, permissions, database indexes, grants, and database notifications for your new entity type.

Once you have opened the current.sql file, type ax-add-table. This causes a pop-up to appear:

Hit 'tab' while ax-add-table is selected in the pop-up menu. This inserts a code template for creating a new table. After adding the code snippet, Visual Studio Code automatically highlights the first text part that will be replaced.

The first thing that you need to replace is the table_name. Replace it with the name that you want the table to have. By convention, this should be the plural of your entity type name, in snake casing. In our example, it is set to channels.
Push the tab key which automatically moves you to the next item to be replaced: ENTITY. Replace this with the name of the (singular) entity name in all caps, in our case CHANNEL.
Push tab once again. You are prompted to define the database schema. For now, leave it as app_public. This schema is used for tables that should appear in the GraphQL API.
Hit tab to continue to the next prompt. This prompt asks if you wish to adjust the database role that should have access to this table. For tables that should appear in the GraphQL API we use DATABASE_GQL_ROLE. So we leave this unchanged and move on to the final prompt with tab.
The final prompt asks you to specify what you want the singular entry (in Capital Camel Case) to be called. You could call it Channel.

Once all this information has been inserted, you should have the following result:

--! Message: channels-added

DROP TABLE IF EXISTS app_public.channels CASCADE;
CREATE TABLE app_public.channels (
  id INT NOT NULL PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
  title TEXT NOT NULL,
  unique_column TEXT NOT NULL,
  boolean_column BOOLEAN NOT NULL DEFAULT FALSE,
  optional_text_column TEXT,
  optional_int_column INT,
  optional_datetime_column TIMESTAMPTZ,
  optional_date_column DATE,

  CONSTRAINT title_max_length CHECK(ax_utils.constraint_max_length(title, 100, 'The title can only be %2$s characters long.')),
  CONSTRAINT title_not_empty CHECK(ax_utils.constraint_not_empty(title, 'The title cannot be empty.'))
);
SELECT ax_utils.define_audit_date_fields_on_table('channels', 'app_public');
SELECT ax_utils.define_audit_user_fields_on_table('channels', 'app_public', ':DEFAULT_USERNAME');

SELECT ax_utils.define_authentication('CHANNELS_VIEW,CHANNELS_EDIT,ADMIN', 'CHANNELS_EDIT,ADMIN', 'channels', 'app_public');

SELECT ax_utils.define_unique_constraint('unique_column', 'channels', 'app_public');

GRANT SELECT, DELETE ON app_public.channels TO ":DATABASE_GQL_ROLE";
GRANT INSERT (
  title,
  unique_column
) ON app_public.channels TO ":DATABASE_GQL_ROLE";
GRANT UPDATE (
  title,
  boolean_column,
  optional_text_column,
  optional_int_column,
  optional_datetime_column,
  optional_date_column
) ON app_public.channels TO ":DATABASE_GQL_ROLE";

SELECT ax_utils.define_subscription_triggers('id', 'channels', 'app_public', 'channels', 'Channel');
SELECT ax_utils.define_indexes_with_id('title', 'channels', 'app_public');
SELECT ax_utils.define_indexes_with_id('unique_column', 'channels', 'app_public');
SELECT ax_utils.define_indexes_with_id('optional_text_column', 'channels', 'app_public');
SELECT ax_utils.define_indexes_with_id('optional_datetime_column', 'channels', 'app_public');
SELECT ax_utils.define_like_index('title', 'channels', 'app_public');
SELECT ax_utils.define_index('boolean_column', 'channels', 'app_public');

-- TODO: After a new table is added, new GraphQL queries/mutations will (likely) become available based on GRANT statements.
--       Newly added operations must be assigned to permissions in PermissionDefinition to become accessible.
-- TODO: Adjust SubscriptionsPlugin by adding a subscription GraphQL endpoint, e.g. `SubscriptionsPluginFactory('channels', 'Channel', 'UUID')`

Adjust the Create Table SQL

The template snippet generated a CREATE table statement with several example table columns. For this guide, we need a few different columns. Start by renaming the unique_column to stream_url. This column will hold the URL for the channel stream.
Add a new optional column via description TEXT that will hold the channel description data.
Now delete all the columns that are provided in the CREATE TABLE statement that have a prefix of optional_ as well as the boolean_column.

Your create table SQL should now look like this:

CREATE TABLE app_public.channels (
  id INT NOT NULL PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
  title TEXT NOT NULL,
  description TEXT,
  stream_url TEXT NOT NULL,

  CONSTRAINT title_max_length CHECK(ax_utils.constraint_max_length(title, 100, 'The title can only be %2$s characters long.')),
  CONSTRAINT title_not_empty CHECK(ax_utils.constraint_not_empty(title, 'The title cannot be empty.'))
);

The next two lines add audit columns to the channels table. The first line adds the columns created_date and updated_date. Those are automatically filled when a table row is inserted or updated.
The second line adds columns that hold the user name who created the table row and who last updated it: created_user and updated_user.

SELECT ax_utils.define_audit_date_fields_on_table('channels', 'app_public');
SELECT ax_utils.define_audit_user_fields_on_table('channels', 'app_public', ':DEFAULT_USERNAME');

We don’t need to adjust anything there.

The line with the ax_utils.define_authentication call contains already the desired permission definition. It allows users with permissions CHANNELS_VIEW, CHANNELS_EDIT or ADMIN to select data from the channels table. And it allows users with permissions CHANNELS_EDIT or ADMIN to insert/update/delete from that table.

The next line holds a command to define a unique constraint. Please adjust the column name from unique_column to stream_url. This ensures that the stream_url must be a unique value for each channel.

The next section defines the database grants to the database role :DATABASE_GQL_ROLE. With those grants you can define which fields from the table should be available in the GraphQL API for queries and create/update mutations. A delete grant will expose the delete mutation in the GraphQL API.

SELECT should always be allowed for all fields.

Note
You can use PostGraphile smart tags in your code to further limit which fields should be available in queries.
DELETE should also have a full grant if you want to expose a delete mutation
INSERT is often a bit more restricted so less fields might be exposed
UPDATE should expose all fields that should be updatable from the API

You can adjust the grants to whatever you prefer. But for our example please remove the columns that we removed from the create table statement and add the description and stream_url to insert/update so it will look like this:

GRANT SELECT, DELETE ON app_public.channels TO ":DATABASE_GQL_ROLE";
GRANT INSERT (
  title,
  stream_url
) ON app_public.channels TO ":DATABASE_GQL_ROLE";
GRANT UPDATE (
  title,
  description,
  stream_url
) ON app_public.channels TO ":DATABASE_GQL_ROLE";

Beside the query and mutation endpoints, GraphQL offers also subscriptions. The line that contains SELECT ax_utils.define_subscription_triggers enables the channels table to be used with GraphQL subscriptions. You can leave that line as-is.

Finally you should create table indexes to allow for faster sorting and filtering. Please adjust the section to the following:

SELECT ax_utils.define_indexes_with_id('title', 'channels', 'app_public');
SELECT ax_utils.define_like_index('title', 'channels', 'app_public');
SELECT ax_utils.define_like_index('description', 'channels', 'app_public');

For consistent paging PostGraphile includes the database ID e.g. (ORDER BY title ASC, id). Therefor it is often best to create a combined index. The define_indexes_with_id utility function does this for you.

The next two lines create an index that improves the "like" filter performance for the title and description columns.

Once finished, the code should look like this:

--! Message: channels-added

DROP TABLE IF EXISTS app_public.channels CASCADE;
CREATE TABLE app_public.channels (
  id INT NOT NULL PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
  title TEXT NOT NULL,
  description TEXT,
  stream_url TEXT NOT NULL,

  CONSTRAINT title_max_length CHECK(ax_utils.constraint_max_length(title, 100, 'The title can only be %2$s characters long.')),
  CONSTRAINT title_not_empty CHECK(ax_utils.constraint_not_empty(title, 'The title cannot be empty.'))
);
SELECT ax_utils.define_audit_date_fields_on_table('channels', 'app_public');
SELECT ax_utils.define_audit_user_fields_on_table('channels', 'app_public', ':DEFAULT_USERNAME');

SELECT ax_utils.define_authentication('CHANNELS_VIEW,CHANNELS_EDIT,ADMIN', 'CHANNELS_EDIT,ADMIN', 'channels', 'app_public');

SELECT ax_utils.define_unique_constraint('stream_url', 'channels', 'app_public');

GRANT SELECT, DELETE ON app_public.channels TO ":DATABASE_GQL_ROLE";
GRANT INSERT (
  title,
  stream_url
) ON app_public.channels TO ":DATABASE_GQL_ROLE";
GRANT UPDATE (
  title,
  description,
  stream_url
) ON app_public.channels TO ":DATABASE_GQL_ROLE";

SELECT ax_utils.define_subscription_triggers('id', 'channels', 'app_public', 'channels', 'Channel');

SELECT ax_utils.define_indexes_with_id('title', 'channels', 'app_public');
SELECT ax_utils.define_like_index('title', 'channels', 'app_public');
SELECT ax_utils.define_like_index('description', 'channels', 'app_public');

Since we’re running the service in dev mode, the migrations will be immediately applied, once the current.sql gets saved. Based on the above definitions the GraphQL API will now include the following queries:

channel - query a channel by ID
channels - query all channels with filter and paging support
channelByStreamUrl - query a channel by its unique stream URL.

The GraphQL API will now also have mutation API endpoints for the channels entity:

createChannel - create a new channel
deleteChannel - delete a channel by ID
updateChannel - update a channel by ID
deleteChannelByStreamUrl - delete a channel by its unique stream URL
updateChannelByStreamUrl - update a channel by its unique stream URL

Defining Permissions

Now that the table has been added to the database, you need to define, which of the GraphQL API endpoints can be accessed with which permission.

We want users with the ADMIN or CHANNELS_EDIT permission to perform all queries and mutations for the new entity. In addition, we want the CHANNELS_VIEW permissions to use only the query API endpoints.

It is a good practice to first group the operations into query and mutate operations to assign them to the permissions more easily:

Navigate to services/media/service/src/domains. There, create a new folder channels.
Inside that folder, create a new document called operation-groups.ts.
To start, import the queries and mutations from the generated graphql operations module.
```
import {
  Mutations as M,
  Queries as Q,
} from '../../generated/graphql/operations';
```
Once you have imported these, continue by defining an array that contains all the read operations for our channel type. The code for this is as follows:
```
export const ChannelsReadOperations = [
  Q.channel,
  Q.channels,
  Q.channelByStreamUrl,
];
```

Next, create a similar array for the mutate operations:

export const ChannelsMutateOperations = [
  M.createChannel,
  M.deleteChannel,
  M.updateChannel,
  M.deleteChannelByStreamUrl,
  M.updateChannelByStreamUrl,
];

Now, that we have grouped the operations, we need to define which permissions they belong to. To do that, go to services/media/service/src/domains/permission-definition.ts.

This file defines all permissions that the service differentiates and which operations are permitted by each permission.

Start by adding the following import statement on top of the file, to import the Operations arrays we just created:

import {
  ChannelsMutateOperations,
  ChannelsReadOperations,
} from './channels/operation-groups';

Then add both operations groups to the ADMIN permission. To do so, find the ADMIN object and spread the content of both operation arrays like this:

{
  key: 'ADMIN',
  title: 'Admin',
  gqlOperations: [
    // keep all existing operations and spread those two:
    ...ChannelsReadOperations,
    ...ChannelsMutateOperations,
  ],
},

As the second step, add the two new dedicated permissions, so that not everyone has to have the ADMIN permission to have access to the new table that you created. To do so, navigate further down the document to find the end of the permissions definitions array . Here, add the CHANNELS_VIEW and CHANNELS_EDIT objects as new elements of that array, like this:

  {
    key: 'CHANNELS_VIEW',
    title: 'Channels: View',
    gqlOperations: [...ChannelsReadOperations],
  },
  {
    key: 'CHANNELS_EDIT',
    title: 'Channels: Edit',
    gqlOperations: [...ChannelsReadOperations, ...ChannelsMutateOperations],
  },

Once these have been added, the file should look like this: