Create New Entity
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.
See also: Extend Existing 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 tochannels
. -
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 caseCHANNEL
. -
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.NoteYou 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 folderchannels
. -
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:
After have declared the permissions to access the data and change it, the backend side of our new entity type is ready. In the next step, we create the workflow for the new entity type in the Management System.
Creating the Management System Workflow
To add the workflow for the new entity type we want to create the following things:
-
A Home tile as entry into the Channel management stations
-
A 'Explorer' station that allows browsing and finding channels
-
A 'Create' station that allows the creation of new channel entities
-
A 'Details' station that allows editing of existing channels
Adding the Home Tile
Let’s start by adding a tile as an entry to the Channels
on the home page. When this
tile is clicked, the user should be redirected to an 'Explorer' Station that lists all
the entities. From there, the user should be able to navigate to a 'Create' Station
to create new entities, or a 'Detail' Station to view and edit the properties of
existing entities or perform other actions on entities.
To achieve that, we need to simply make a call to the registerTile
function on the
PiletAPI object the Micro-Frontend gets passed in the setup
method inside the root index.tsx
file. In order to keep maintainability, we won’t add the call directly to that file though.
Instead we will create a similar file structure the rest of the code on the template also uses:
-
Go to
services/media/workflows/src/Stations
and create a new folderChannels
which holds all the 'Channels'-related code. -
Once you have created this folder, you can navigate inside it and create a new document
registrations.tsx
and add the following content:import { PiletApi } from '@axinom/mosaic-portal'; import React from 'react'; import { Extensions } from '../../externals'; export function register(app: PiletApi, extensions: Extensions): void { app.registerTile({ kind: 'home', name: 'channels', path: '/channels', label: 'Channels', icon: ( <svg version="1.1" xmlns="http://www.w3.org/2000/svg" viewBox="0 0 40 40"> <path vectorEffect="non-scaling-stroke" fill="none" stroke="#00467D" strokeWidth="2" d="M10.4,33.5h9.1 M39.1,31V6.5h-38V31h16.5 M35.4,28.5L21.8,14.2c-1.1-1.2-3-1.2-4.2-0.1L13.5,18 c-1.2,1.1-1.2,3-0.1,4.2l13.6,14.3c1.1,1.2,3,1.2,4.2,0.1l4.1-3.9C36.5,31.6,36.5,29.7,35.4,28.5z M17.7,18.5 c-1.2,1.1-1.3,3.1-0.1,4.3s3.1,1.3,4.3,0.1c1.2-1.1,1.3-3.1,0.1-4.3C20.8,17.4,18.9,17.3,17.7,18.5z M22.8,27.1 c-0.3,0.3-0.3,0.8,0,1.2s0.8,0.3,1.2,0c0.3-0.3,0.3-0.8,0-1.2S23.1,26.8,22.8,27.1z M25.9,24.1c-0.3,0.3-0.3,0.8,0,1.2 s0.8,0.3,1.2