Database Schema

Intro

This has been a fork of postgraphql's forum example, which is why it is very similar, but is intended for fleetgrid.

The Postgres database is rich with features well beyond that of any other database. However, most developers do not know the extent to which they can leverage the features in Postgres to completely express their application business logic in the database.

Often developers may find themselves re-implimenting authentication and authorization in their apps, when Postgres comes with application level security features out of the box. Or perhaps developers may rewrite basic insert functions with some extra app logic where that too may be handled in the database.

This reimplementation of features that come with Postgres is not just an inefficient way to spend developer resources, but may also result in an interface that is slower than if the logic was implemented in Postgres itself. PostGraphQL aims to make developers more efficient and their APIs faster by packaging the repeatable work in one open source project that encourages community contributions.

In this tutorial we will walk through the Postgres schema design for fleetgrid with tenants who can login and schedual work orders for their customers. While we will discuss how you can use the schema we create with PostGraphQL, this article should be useful for anyone designing a Postgres schema.

The Basics

Setting Up Your Schemas

All of our database objects will go into one or two custom Postgres schemas. A schema is essentially a namespace, it allows you to create tables with the same name like a.person and b.person.

You can name your schema anything, we recommend naming your schema after your app. This way if you are working on multiple apps in the same database (this might only realistically happen in development), you can easily query the databases of the different apps. We are going to create two schemas: fg, and fg_private. To create these schemas we use the CREATE SCHEMA command.

create schema fg;
create schema fg_private;
create schema rabbitmq;

You could create more or less schemas, it is all up to you and how you want to structure your database. We decided to create two schemas. One of which, fg, is meant to hold data users can see, whereas fg_private will never be directly accessible to users.

Theoretically we want a user to be able to log in directly to our Postgres database, and only be able to create, read, update, and delete data for their user all within SQL. This is a mindshift from how we traditionally use a SQL database. Normally, we assume whoever is querying the database has full visibility into the system as the only one with database access is our application. In this tutorial, we want to restrict access at the database level. Don’t worry though! Postgres is very secure about this, users will have no more permissions then that which you explicitly grant.

Note: When starting PostGraphQL, you will want to use the name of the schema you created with the --schema option, like so: postgraphql --schema fg. Also, don’t forget to add the --watch flag, with watch mode enabled PostGraphQL will update your API as we add tables and types throughout this tutorial.

Extensions

create extension if not exists "uuid-ossp";
  1. This extension is used during table creation as id. In case you don’t like serial id, which in our case is true, an alternative to the serial primary key is UUIDs. To use UUIDs you would just need to add the popular UUID extension, uuid-ossp, in your database setup, and specify a default in your table creation. More on this below.

The Tenant Table

create type fg.tenant_industry as enum (
  'Painting',
  'Carpet Cleaning',
  'Lawn Care',
  'Residential Cleaning',
  'Snow Removal',
  'General Contracting',
  'Landscaping',
  'Appliance Repair',
  'HVAC',
  'Plumbing',
  'Home Cleaning',
  'Electrical',
  'Roofing',
  'Window Washing',
  'Pressure Washing',
  'Commercial Cleaning'
);
create table fg.tenant (
    id uuid primary key default uuid_generate_v1mc(),
    company_name text not null,
    industry fg.tenant_industry,
    address_one text,
    city text,
    state text,
    postal_code text,
    country text,
    license_name text,
    phone_business text,
    phone_fax text,
    phone_other text,
    website_url text,
    time_zone text,
    locale text,
    currency text,
    company_logo bytea,
    default_payment_term text default 'due upon receipt',
    default_payment_days integer not null default 0,
    email_main text,
    email_notification_address text,
    sms_notification text,
    is_module_hvac boolean not null default false,
    billing_interval interval,
    scheduling_interval interval,
    page_header text,
    -- integrations
    is_mailgun_integration boolean not null default false,
    mailgun_private_api_key text,
    mailgun_public_validation_key text,
    is_sendgrid_integration boolean not null default false,
    sendgrid_api_key text,
    is_stripe_integration boolean not null default false,
    stripe_publishable_key text,
    stripe_secret_key text,
    is_braintree_integration boolean not null default false,
    braintree_merchant_id text,
    braintree_public_key text,
    braintree_private_key text,
    is_quickbooks_integration boolean not null default false,
    quickbooks_client_id text,
    quickbooks_client_secret text,
    is_nexmo_integration boolean not null default false,
    nexmo_api_key text,
    nexmo_api_secret text,
    is_twilio_integration boolean not null default false,
    twilio_account_sid text,
    twilio_auth_token text,
    is_google_calendar_integration boolean not null default false,
    google_calendar_api_key text,
    -- admin
    is_deleted boolean not null default false,
    created_by text not null default current_user,
    created_at timestamptz not null default now()
);

comment on table fg.tenant is 'Tenant or Company master. All information is related to the Tenant.';
comment on column fg.tenant.company_name is 'Company Name';

The Employee Table

create table fg.employee (
    id uuid primary key default uuid_generate_v1mc(),
    sidebar_visible boolean not null default true,
    workflow_visible boolean not null default false,
    is_quality_control_approved boolean not null default false,
    navigation text,
    first_name text,
    last_name text,
    title text,
    address_one text,
    address_two text,
    city text,
    state text,
    postal_code character varying(20),
    birthday date,
    email text,
    emergency_contact_name text,
    emergency_contact_phone character varying(25),
    termination_date date,
    gender character varying(1),
    hire_date date,
    is_active boolean not null default true,
    is_assignable_to_job boolean not null default true,
    is_email_daily_schedule boolean not null default false,
    is_email_event_reminder boolean not null default false,
    is_email_notification_active boolean not null default false,
    is_mobile_event_reminder boolean not null default false,
    is_mobile_notification_active boolean not null default false,
    is_pricing_visible boolean not null default false,
    is_role_admin boolean not null default false,
    is_salaried boolean default false,
    mobile_notification_number text,
    notes text,
    observes_daylight_savings_time boolean not null default true,
    phone_home text,
    phone_mobile_personal text,
    phone_mobile_work text,
    phone_other text,
    phone_work text,
    return_date date,
    start_date date,
    work_end_time time without time zone,
    work_start_time time without time zone,
    email_verified boolean not null default false,
    status text,
    -- admin
    is_deleted boolean not null default false,
    created_by text not null default current_user,
    created_at timestamptz not null default now(),
    tenant_id uuid not null default current_setting('jwt.claims.tenant_id')::uuid
);

comment on table fg.employee is 'Employees for a Tenant. May have logins and work on jobs.';
comment on column fg.employee.is_role_admin is 'Is this an employee an administrator for the Company. Administrators can run any feature or function including creating and assigning privileges to additional employees.';

The Person Table

Now we are going to create the tables in our database which will correspond to our users. We will do this by running the Postgres CREATE TABLE command. Here is the definition for our person table:

create table fg.person (
  id uuid primary key default uuid_generate_v1mc(),
  is_quality_control_approved boolean not null default false,
  first_name text not null check (char_length(first_name) < 80),
  last_name text check (char_length(last_name) < 80),
  about text,
  created_at timestamp default now()
);

Now we have created a table with id, first_name, last_name, about, and created_at columns (we will add an updated_at column later). Let’s break down exactly what each line in this command does, we will only do this once. If you already understand, you can skip ahead.

  1. create table fg.person: This tells Postgres that we are creating a table in the fg schema named person. This table will represent all of our forum’s users.
  2. id uuid primary key: This line establishes an uuid field which is always guaranteed to be unique. Each person will have their own universaly unique string. The primary key bit is also very important. PostGraphQL will use the primary key of a table in many places to uniquely identify an object, including the globally unique id field.
  3. first_name text not null check (char_length(first_name) < 80): We want all of our users to enter their first name and last name seperately, so this column definition will create a column named first_name, of type text, that is required (not null), and that must be less than 80 characters long (check (char_length(first_name) < 80)). Check constraints are a very powerful feature in Postgres for data validation.
  4. last_name text check (char_length(last_name) < 80): This is very similar to our column definition for first_name, except it is missing not null. This means that unlike the first_name column, last_name is not required.
  5. about text: We want users to be able to express themselves! So they get to write a mini forum post which will go on their profile page.
  6. created_at timestamp default now(): This final column definition will provide us with some extra meta-information about their user. If not specified explicitly, the created_at timestamp will default to the time the row was inserted.

And that’s our person table! Pretty simple, right?

The syntax and features of the Postgres CREATE TABLE command are fairly easy to learn and understand. Creating tables is the easiest, but also the most fundamental part of your schema design.

Note: We prefer singular identifers like fg.person over fg.people because when you create a table, it is like you are creating a class in a statically typed language. Classes have singular names like “Person” while collections will often have plural names like “People.” Table as a class is a better analogy than table as a collection because Postgres itself will internally call tables “classes.”

Note: In case you don’t like serial id of our table above, an alternative to the serial primary key is UUIDs. To use UUIDs you would just need to add the popular UUID extension, uuid-ossp, in your database setup, and specify a default in your table creation. Like so:

create extension if not exists "uuid-ossp";

create table fg.person (
  id uuid primary key default uuid_generate_v1mc(),
  ...
);

If you are going to use UUIDs as the primary key, it is recommended you use uuid_generate_v1mc to generate the ids. This is because uuid_generate_v1mc is time based which means the ids will be mostly sequential which is good for your primary key index.

There are pros and cons to both approaches, choose what works best for your application!

Table Documentation

Now that we have created our table, we want to document it within the Postgres database. By adding comments to our table and its columns using the Postgres COMMENT command, we will allow tools like PostGraphQL to display rich domain specific documentation.

To add comments, just see the SQL below:

comment on table fg.person is 'A user of the forum.';
comment on column fg.person.id is 'The primary unique identifier for the person.';
comment on column fg.person.first_name is 'The person’s first name.';
comment on column fg.person.last_name is 'The person’s last name.';
comment on column fg.person.about is 'A short description about the user, written by the user.';
comment on column fg.person.created_at is 'The time this person was created.';

Incredibly simple, yet also incredibly powerful.

Note: Feel free to write your comments in Markdown! Most tools, including GraphiQL which PostGraphQL uses, will render your comments with the appropriate styles.

With this we have completed our person table, now let’s create a table for our forum posts.

The Post Table

The users of our forum will want to be able to create posts. That’s the entire reason we have a forum after all. To create the post table we go through a very similar process as creating our fg.person table, but first we want to create a type we will use in one of the columns. See the SQL below:

create type fg.post_topic as enum (
  'discussion',
  'inspiration',
  'help',
  'showcase'
);

The Postgres CREATE TYPE command will let you create a custom type in your database which will allow you to do some really cool things. You can create a composite type which is basically a typed object in GraphQL terms, you can create a range type which represents exactly what you might think, or you can create an enum type which is what we did here.

Enum types are a static set of values, you must use one of the string values that make up the enum in any column of the enum’s type. Having this type is useful for us, because we want our forum posts to have one, or none, topics so user’s may easily see what a post is about.

Note: PostGraphQL implements custom handling for user-defined types. An enum type like that defined above will be turned into a GraphQL enum that looks like:

enum PostTopic {
  DISCUSSION
  INSPIRATION
  HELP
  SHOWCASE
}

You can also create custom composite types which will turn into GraphQL object types with PostGraphQL.

create type my_schema.my_type as (
  foo integer,
  bar integer
);

Would become the following GraphQL type:

type MyType {
  foo: Int
  bar: Int
}

Now it is time to actually create our post table:

create table fg.post (
  id               uuid primary key default uuid_generate_v1mc(),
  author_id        uuid not null references fg.person(id),
  headline         text not null check (char_length(headline) < 280),
  body             text,
  topic            fg.post_topic,
  created_at       timestamp default now()
);

comment on table fg.post is 'A forum post written by a user.';
comment on column fg.post.id is 'The primary key for the post.';
comment on column fg.post.headline is 'The title written by the user.';
comment on column fg.post.author_id is 'The id of the author user.';
comment on column fg.post.topic is 'The topic this has been posted in.';
comment on column fg.post.body is 'The main body text of our post.';
comment on column fg.post.created_at is 'The time this post was created.';

Pretty basic. Our headline is twice as long as a tweet, and to use our fg.post_topic type we wrote it as the column type just as we may write integer as the column type. We also made sure to include comments.

Now that we have gone over the basics, let’s explore Postgres functions and see how we can use them to extend the functionality of our database.

The Tenant Open Close Table

--
-- Stores the open and close hours for the business
-- Record different times by having multiple rows for a
-- given tenant.
--
create table fg.tenant_open_close (
    id uuid primary key default uuid_generate_v1mc(),
    open_time time without time zone not null,
    close_time time without time zone not null,
    is_monday boolean not null default false not null,
    is_tuesday boolean not null default false not null,
    is_wednesday boolean not null default false not null,
    is_thursday boolean not null default false not null,
    is_friday boolean not null default false not null,
    is_saturday boolean not null default false not null,
    is_sunday boolean not null default false not null,
    -- admin
    is_deleted boolean not null default false,
    created_by text not null default current_user,
    created_at timestamptz not null default now(),
    tenant_id uuid not null
);

comment on table fg.tenant_open_close is 'Stores the open and close hours for the business. Record different times using multiple rows.';
comment on column fg.tenant_open_close.open_time is 'Time of the day the business opens.';
comment on column fg.tenant_open_close.close_time is 'Time of the day the business closes.';
comment on column fg.tenant_open_close.is_monday is 'Do the open adn close times apply to Monday?';
comment on column fg.tenant_open_close.is_tuesday is 'Do the open adn close times apply to Tuesday?';
comment on column fg.tenant_open_close.is_wednesday is 'Do the open adn close times apply to Wednesday?';
comment on column fg.tenant_open_close.is_thursday is 'Do the open adn close times apply to Thursday?';
comment on column fg.tenant_open_close.is_friday is 'Do the open adn close times apply to Friday?';
comment on column fg.tenant_open_close.is_saturday is 'Do the open adn close times apply to Saturday?';
comment on column fg.tenant_open_close.is_sunday is 'Do the open adn close times apply to Sunday?';

The Customer Table

create type fg.customer_type as enum (
  'Residential',
  'Commercial'
);

create table fg.customer (
    -- email, name, or company name are required
    id uuid primary key default uuid_generate_v1mc(),
    type fg.customer_type not null default 'Residential',
    parent_customer_id uuid,
    -- primary_address_id uuid references fg.customer_address(id),
    -- primary_contact_id uuid references fg.customer_contact(id),
    email text,
    company_name text,
    first_name text,
    last_name text,
    access_code text,
    access_hours text,
    is_active boolean not null default true,
    source_type_code text,
    notes text,
    tags text,
    tax_rate_id bigint,
    website_url text,
    -- admin
    is_deleted boolean not null default false,
    created_by text not null default current_user,
    created_at timestamptz not null default now(),
    tenant_id uuid not null default current_setting('jwt.claims.tenant_id')::uuid
    --
    CHECK (email <> null or company_name <> null or first_name <> null or last_name <> null)
);

comment on table fg.customer is 'Customers for tenants.';
comment on column fg.customer.parent_customer_id is 'In the event a customer has multiple locations or facilities the parent customer identifies the master.';

The Customer Address Table

create table fg.customer_address (
    -- choose: service address=0, billing address=1, parent billing address=2
    id uuid primary key default uuid_generate_v1mc(),
    customer_id uuid not null references fg.customer(id),
    name text,
    address_one text,
    address_two text,
    city text,
    state text,
    postal_code text,
    is_billing_address boolean not null default true,
    country text,
    notes text,
    tags text,
    -- location GEOGRAPHY(POINT,4326), Need to install and configure postgis for this
    latitude text, -- used to create the location
    longitude text, -- used to create the location from the data generator
    -- admin
    is_deleted boolean not null default false,
    created_by text not null default current_user,
    created_at timestamptz not null default now(),
    tenant_id uuid not null default current_setting('jwt.claims.tenant_id')::uuid
);

comment on table fg.customer_address is 'Addresses for a customer';

ALTER TABLE fg.customer
ADD COLUMN primary_address_id uuid references fg.customer_address(id);

The Customer Contact Table

create table fg.customer_contact (
    id uuid primary key default uuid_generate_v1mc(),
    customer_id uuid not null references fg.customer(id),
    first_name text not null,
    last_name text not null,
    title text,
    address_id uuid references fg.customer_address(id),
    email text,
    phone_home text,
    phone_mobile_personal text,
    phone_mobile_work text,
    phone_other text,
    phone_work text,
    notes text,
    tags text,
    -- admin
    is_deleted boolean not null default false,
    created_by text not null default current_user,
    created_at timestamptz not null default now(),
    tenant_id uuid not null default current_setting('jwt.claims.tenant_id')::uuid
);

comment on table fg.customer_contact is 'Contacts for a customer';

ALTER TABLE fg.customer
ADD COLUMN primary_contact_id uuid references fg.customer_contact(id);

The Item Category Table

create table fg.item_category (
    id uuid primary key default uuid_generate_v1mc(),
    name text not null,
    notes text,
    is_active boolean not null default true,
    -- admin
    is_deleted boolean not null default false,
    created_by text not null default current_user,
    created_at timestamptz not null default now(),
    tenant_id uuid not null default current_setting('jwt.claims.tenant_id')::uuid
);

comment on table fg.item_category is 'Tenant-wide item categories used to group related items or services offered. Example categories are "General Services", "Plumbing", "HVAC"';

The Item Table

create type fg.item_type_enum as enum (
  'Material',
  'Service'
);
create table fg.item (
    id uuid primary key default uuid_generate_v1mc(),
    item_category_id uuid not null references fg.item_category(id),
    item_name text not null,
    notes text,
    -- Material, Labor
    item_type fg.item_type_enum NOT NULL default 'Material',
    product_code text,
    product_family text,
    product_line text,
    product_name text,
    cross_reference text,
    is_active boolean not null default true,
    is_taxable boolean,
    item_uom_code text,
    unit_cost numeric(10,4),
    unit_price numeric(10,4),
    installation_time integer,
    -- admin
    is_deleted boolean not null default false,
    created_by text not null default current_user,
    created_at timestamptz not null default now(),
    tenant_id uuid not null default current_setting('jwt.claims.tenant_id')::uuid
);

comment on table fg.item is 'Items the tenant has to offer and are billed to customers. Items may be anything, as broad as "Install Air Conditioner" or narrow as an individual part.';
comment on column fg.item.item_type is 'The types of billable items. Item types are Materials or Labor. Default is Material';

The Tax Rate Table

create table fg.tax_rate (
    id uuid primary key default uuid_generate_v1mc(),
    name text not null,
    percent numeric(10,4),
    -- admin
    is_deleted boolean not null default false,
    created_by text not null default current_user,
    created_at timestamptz not null default now(),
    tenant_id uuid not null default current_setting('jwt.claims.tenant_id')::uuid
);
    
comment on table fg.tax_rate is 'Applicable tax rates.';

The Job Table

create type fg.estimate_status_enum as enum (
  'Unscheduled',
  'Scheduled',
  'In Progress',
  'Awaiting Approval',
  'Approved',
  'Rejected',
  'Closed'
);

create type fg.job_status_enum as enum (
  'Unscheduled',
  'Scheduled',
  'In Progress',
  'Completed'
);

create type fg.invoice_status_enum as enum (
  'Open',
  'Pending',
  'Paid'
);
create table fg.job (
    id uuid primary key default uuid_generate_v1mc(),
    customer_id uuid not null references fg.customer(id),
    address_id uuid references fg.customer_address(id),
    job_name text not null,
    notes text,
    tags text,
    estimate_date date,
    job_date date,
    invoice_date date,
    invoice_due_date date,
    estimate_send_to_email text,
    estimate_status fg.estimate_status_enum not null default 'Unscheduled',
    job_status fg.job_status_enum not null default 'Unscheduled',
    invoice_status fg.invoice_status_enum not null default 'Open',
    is_estimate boolean not null default false,
    is_estimate_sent_to_customer boolean not null default false,
    is_estimate_customer_approved boolean default null,
    is_invoice boolean not null default false,
    is_job boolean not null default false,
    purchase_order text,
    estimate_on_my_way_datetime timestamptz,
    estimate_schedule_start_datetime timestamptz,
    estimate_schedule_duration interval,
    estimate_actual_start_datetime timestamptz,
    estimate_actual_duration interval,
    job_on_my_way_datetime timestamptz,
    job_schedule_start_datetime timestamptz,
    job_schedule_duration interval,
    job_actual_start_datetime timestamptz,
    job_actual_duration interval,
    is_accounting_synced boolean not null default false,
    -- money totals
    is_taxable boolean not null default false,
    tax_rate_id uuid references fg.tax_rate(id),
    service_total_amount numeric(12,2),
    material_total_amount numeric(12,2),
    expense_total_amount numeric(12,2),
    balance_due_amount numeric(12,2),
    invoice_sub_total_amount numeric(12,2),
    invoice_total_amount numeric(12,2),
    paid_total_amount numeric(12,2),
    is_discount_applied boolean not null default false,
    discount_fixed_amount numeric(12,2) not null default 0,
    discount_percentage numeric(8,5) not null default 0,
    discount_total_amount numeric(12,2) not null default 0,
    discount_name text default 'Discount',
    -- admin
    is_deleted boolean not null default false,
    created_by text not null default current_user,
    created_at timestamptz not null default now(),
    tenant_id uuid not null default current_setting('jwt.claims.tenant_id')::uuid
);

create index job_customer_id on fg.job using btree (customer_id);

comment on table fg.job is 'Jobs (workorders) for a customer';
comment on column fg.job.customer_id is 'Customer for this job';
comment on column fg.job.invoice_status is 'If invoiced, contains the status. May be Open, Pending, or Paid. Defaults to Open';
comment on column fg.job.is_accounting_synced is 'If invoiced, has it been synchronized with accounting. Default false.';
comment on column fg.job.is_estimate is 'Is this an estimate? Default false.';
comment on column fg.job.job_date is 'Date the job accepted, contract signed, or agreed upon depending on the policy of the tenant. See schedule or actual columns for job scheduling.';
comment on column fg.job.job_status is 'Job status. May be New, Declined, Paused, Scheduled, Active, Completed. Default New.';
comment on column fg.job.job_schedule_start_datetime is 'Time the job is scheduled to begin.';
comment on column fg.job.job_schedule_duration is 'Scheduled duration for the job as an interval.';
comment on column fg.job.job_actual_start_datetime is 'Time the job actually started.';
comment on column fg.job.job_actual_duration is 'Actual duration for the job as an interval.';
comment on column fg.job.purchase_order is 'Optional purchase order if required by tenant or as a reference for the tenant''s customer.';

The Job Item Table

create table fg.job_item (
    id uuid primary key default uuid_generate_v1mc(),
    job_id uuid not null references fg.job(id),
    item_id uuid references fg.item(id),
    job_item_name text not null,
    notes text,
    tags text,
    line_number numeric(10,2) not null default 1,
    is_billable boolean not null default true,
    is_taxable boolean not null default false,
    item_type fg.item_type_enum NOT NULL default 'Service',
    item_uom_code text,
    service_duration integer,
    quantity numeric(10,2) not null default 1,
    tax_amount numeric(12,2) not null default 0,
    unit_cost numeric(10,4) not null default 0,
    unit_price numeric(10,4) not null default 0,
    extended_cost_amount numeric(12,2) not null default 0,
    extended_price_amount numeric(12,2) not null default 0,
    total_amount numeric(12,2) not null default 0, -- includes tax
    -- admin
    is_deleted boolean not null default false,
    created_by text not null default current_user,
    created_at timestamptz not null default now(),
    tenant_id uuid not null default current_setting('jwt.claims.tenant_id')::uuid
);

create index job_item_item_id on fg.job_item using btree (item_id);

create index job_item_job_id on fg.job_item using btree (job_id);

comment on table fg.job_item is 'Individual line items associated with a customer''s job.';
comment on column fg.job_item.item_type is 'The type of line item. May be Material or Service. Default is Service.';

The Unit Table

create table fg.unit (
    id uuid primary key default uuid_generate_v1mc(),
    customer_id uuid not null references fg.customer(id),
    address_id uuid references fg.customer_address(id),
    name text not null,
    notes text,
    tags text,
    -- HVAC Buddy
    make text default 'Carriar',
    model_number text default 'Infinity 19 VS',
    serial_number text default '24VNA9Z2019',
    filter_size text default '16x32',
    tx_valve numeric(10,2) default 0,
    refrigerant numeric(10,2) default 46,
    target_tolerance numeric(10,2) default 5,
    mrsh numeric(10,2) default 0,
    mrsh_target numeric(10,2) default 0.0,
    mrsc numeric(10,2) default 0,
    mrsc_target numeric(10,2) default 0.0,
    atm_pressure numeric(10,2) default 1,
    atm_pressure_measurement numeric(10,2) default 14.7,
    temperature numeric(10,2) default 0,
    pressure numeric(10,2) default 0,
    -- other
    altitude double precision,
    lineset_length double precision,
    rated_seer integer,
    condenser_manufacturer character varying (80),
    condenser_manufacture_date date,
    condenser_model_number text,
    condenser_rated_capacity_btuh integer,
    condenser_compressor_rated_voltage smallint,
    condenser_compressor_phase smallint,
    condenser_compressor_start_capacitor text,
    condenser_compressor_run_capacitor text,
    condenser_serial_number text,
    condenser_fan_phase smallint,
    condenser_fan_run_capacitor text,
    evaporator_manufacturer text,
    evaporator_manufacture_date date,
    evaporator_model_number text,
    evaporator_rated_capacity_btuh integer,
    evaporator_fan_phase smallint,
    evaporator_fan_run_capacitor text,
    evaporator_serial_number text,
    evaporator_target_cfm integer,
    evaporator_has_txv boolean,
    -- admin
    is_deleted boolean not null default false,
    created_by text not null default current_user,
    created_at timestamptz not null default now(),
    tenant_id uuid not null default current_setting('jwt.claims.tenant_id')::uuid
);

comment on table fg.unit is 'HVAC Unit';
comment on column fg.unit.name is 'Name of the unit.';

The Diagnostic Table

create table fg.diagnostic (
    id uuid primary key default uuid_generate_v1mc(),
    customer_id uuid not null references fg.customer(id),
    unit_id uuid not null references fg.unit(id),
    name text not null,
    notes text,
    tags text,
    -- HVAC Buddy
    sh_dew numeric(10,2) default 0.8,
    sh_target numeric(10,2) default 21.5,
    sh_actual numeric(10,2) default 22.2,
    sh_delta numeric(10,2) default 0.7,
    sh_indoor_wet_bulb numeric(10,2) default 71,
    sh_outdoor_dry_bulb numeric(10,2) default 91,
    sh_suction_line_temp numeric(10,2) default 23,
    sh_suction_line_press numeric(10,2) default 64,
    sc_bubble numeric(10,2) default 19.0,
    sc_target numeric(10,2) default 20.5,
    sc_actual numeric(10,2) default 21.0,
    sc_delta numeric(10,2) default 0.5,
    sc_indoor_wet_bulb numeric(10,2) default 60,
    sc_outdoor_dry_bulb numeric(10,2) default 90,
    sc_liquid_leaving_temp numeric(10,2) default -2,
    sc_liquid_leaving_press numeric(10,2) default 91.7,
    af_target numeric(10,2) default 14.7,
    af_actual numeric(10,2) default 16.0,
    af_delta numeric(10,2) default 1.3,
    af_return_wet_bulb numeric(10,2) default 66,
    af_return_dry_bulb numeric(10,2) default 72,
    af_supply_dry_bulb numeric(10,2) default 56,
    -- admin
    is_deleted boolean not null default false,
    created_by text not null default current_user,
    created_at timestamptz not null default now(),
    tenant_id uuid not null default current_setting('jwt.claims.tenant_id')::uuid
);

comment on table fg.diagnostic is 'HVAC Diagnostic';
comment on column fg.diagnostic.name is 'Name of the diagnostic.';

The Job Employee Table

create table fg.job_employee (
    -- associative table, primary key is the relation
    job_id uuid not null,
    employee_id uuid not null,
    -- admin
    is_deleted boolean not null default false,
    created_by text not null default current_user,
    created_at timestamptz not null default now(),
    tenant_id uuid not null
);

alter table only fg.job_employee
    add constraint job_employee_pkey primary key (job_id, employee_id);

comment on table fg.job_employee is 'Associative table links emaployees assigned to work on jobs. Future additions may include start and end time.';

Database Functions

The Postgres CREATE FUNCTION command is truly amazing. It allows us to write functions for our database in SQL, and other languages including JavaScript and Ruby!

The following is a basic Postgres function:

create function add(a int, b int) returns int as $$
 select a + b
$$ language sql stable;

Note the form. The double dollar signs ($$) open and close the function, and at the very end we have language sql stable. language sql means that the function is written in SQL, pretty obvious. If you wrote your function in Ruby it may be language plruby. The next word, stable, means that this function does not mutate the database. By default Postgres assumes all functions will mutate the database, you must mark your function with stable for Postgres, and PostGraphQL, to know your function is a query and not a mutation.

Note: If you are interested in running JavaScript or Ruby in Postgres, check out PL/V8 and PL/ruby respectively. It is recommended that you use SQL and PL/pgSQL (which comes native with Postgres) whenever you can (even if they are a pain). There is plenty of documentation and StackOverflow answers on both SQL and PL/pgSQL. However, there are alternatives if you so choose.

That function above isn’t so useful for us in our schema, so let’s write some functions which will be useful. We will define three.

First, a function which will concatenate the users first and last name to return their full name:

create function fg.person_full_name(person fg.person) returns text as $$
  select person.first_name || ' ' || person.last_name
$$ language sql stable;

comment on function fg.person_full_name(fg.person) is 'A person’s full name which is a concatenation of their first and last name.';

Second, a function which will get a summary of a forum post:

create function fg.post_summary(
  post fg.post,
  length int default 50,
  omission text default '…'
) returns text as $$
  select case
    when post.body is null then null
    else substr(post.body, 0, length) || omission
  end
$$ language sql stable;

comment on function fg.post_summary(fg.post, int, text) is 'A truncated version of the body for summaries.';

Third, a function that will get a person’s most recent forum post.

create function fg.person_latest_post(person fg.person) returns fg.post as $$
  select post.*
  from fg.post as post
  where post.author_id = person.id
  order by created_at desc
  limit 1
$$ language sql stable;

comment on function fg.person_latest_post(fg.person) is 'Get’s the latest post written by the person.';

Don’t get too stuck on the function implementations. It is fairly easy to discover how to express what you want in SQL through a quick search of the Postgres documentation (which is excellent!). These functions are here to give you some examples of what functions in Postgres look like. Also note how we added comments to our functions with the COMMENT command, just like we add comments to our tables.

Note: Any function which meets the following conditions will be treated as a computed field by PostGraphQL:

  1. The function has a table row as the first argument.
  2. The function is in the same schema as the table of the first argument.
  3. The function’s name is prefixed by the table’s name.
  4. The function is marked as stable or immutable which makes it a query and not a mutation.

All three of the above functions meet these conditions and as such will be computed fields. In GraphQL this ends up looking like:

type Person {
  id: Int!
  firstName: String!
  lastName: String
  ...
  fullName: String
  latestPost: Post
}

Set Returning Functions

Sometimes it is useful to not just return single values from your function, but perhaps entire tables. What returning a table from a function could mean is you could define a custom ordering, hide rows that were archived, or return a user’s activity feed perhaps. In our case, this Postgres feature makes it easy for us to implement search:

create function fg.search_posts(search text) returns setof fg.post as $$
  select post.*
  from fg.post as post
  where post.headline ilike ('%' || search || '%') or post.body ilike ('%' || search || '%')
$$ language sql stable;

comment on function fg.search_posts(text) is 'Returns posts containing a given search term.';

The difference with this function and the ones before is the return signature reads returns setof fg.post. This function will therefore return all of the posts that match our search condition and not just one.

Note: PostGraphQL will treat set returning functions as connections. This is what makes them so powerful for PostGraphQL users. The function above would be queryable like so:

{
  searchPosts(search: "Hello, world!", first: 5) {
    edges {
      cursor
      node {
        headline
        body
      }
    }
  }
}

Note: Postgres has awesome text searching capabilities, the function above uses a basic ILIKE pattern matching operator. If you want high quality full text searching you don’t need to look outside Postgres. Instead look into the Postgres Full Text Search functionality. It is a great feature, but a bit much for our simple example.

Note: Returning an array (returns post[]), and returning a set (returns setof post) are two very different things. When you return an array, every single value in the array will always be returned. However, when you return a set it is like returning a table. Users can paginate through a set using limit and offset, but not an array.

Triggers

You can also use Postgres functions to define triggers. Triggers in Postgres allow you to hook into events that are happening on your tables such as inserts, updates, or deletes. You define your triggers with the CREATE TRIGGER command, and all trigger functions must return the special type trigger.

To demonstrate how triggers work, we will define a trigger that sets an updated_at column on our fg.person and fg.post tables whenever a row is updated. Before we can write the trigger, we need to make sure fg.person and fg.post have an updated_at column! To do this we will use the ALTER TABLE command.

alter table fg.person add column updated_at timestamp default now();
alter table fg.post add column updated_at timestamp default now();
alter table fg.employee add column updated_at timestamp default now();
alter table fg.tenant add column updated_at timestamp default now();
alter table fg.tenant_open_close add column updated_at timestamp default now();
alter table fg.customer_address add column updated_at timestamp default now();
alter table fg.customer_contact add column updated_at timestamp default now();
alter table fg.customer add column updated_at timestamp default now();
alter table fg.tax_rate add column updated_at timestamp default now();
alter table fg.job add column updated_at timestamp default now();
alter table fg.job_item add column updated_at timestamp default now();
alter table fg.item add column updated_at timestamp default now();
alter table fg.item_category add column updated_at timestamp default now();
alter table fg.unit add column updated_at timestamp default now();
alter table fg.diagnostic add column updated_at timestamp default now();
alter table fg.job_employee add column updated_at timestamp default now();

Our updated_at column has now been added to our tables and looks exactly like our created_at column. It’s a timestamp which defaults to the time the row was created. Next, let us define our triggers:

create function fg_private.set_updated_at() returns trigger as $$
begin
  new.updated_at := current_timestamp;
  return new;
end;
$$ language plpgsql;

create trigger person_updated_at before update
  on fg.person
  for each row
  execute procedure fg_private.set_updated_at();

create trigger post_updated_at before update
  on fg.post
  for each row
  execute procedure fg_private.set_updated_at();

create trigger employee_updated_at before update
  on fg.employee
  for each row
  execute procedure fg_private.set_updated_at();

create trigger tenant_updated_at before update
  on fg.tenant
  for each row
  execute procedure fg_private.set_updated_at();

create trigger tenant_open_close_updated_at before update
  on fg.tenant_open_close
  for each row
  execute procedure fg_private.set_updated_at();

create trigger customer_address_updated_at before update
  on fg.customer_address
  for each row
  execute procedure fg_private.set_updated_at();

create trigger customer_contact_updated_at before update
  on fg.customer_contact
  for each row
  execute procedure fg_private.set_updated_at();

create trigger customer_updated_at before update
  on fg.customer
  for each row
  execute procedure fg_private.set_updated_at();

create trigger tax_rate_updated_at before update
  on fg.tax_rate
  for each row
  execute procedure fg_private.set_updated_at();

create trigger job_updated_at before update
  on fg.job
  for each row
  execute procedure fg_private.set_updated_at();

create trigger job_item_updated_at before update
  on fg.job_item
  for each row
  execute procedure fg_private.set_updated_at();

create trigger item_updated_at before update
  on fg.item
  for each row
  execute procedure fg_private.set_updated_at();

create trigger item_category_updated_at before update
  on fg.item_category
  for each row
  execute procedure fg_private.set_updated_at();

create trigger unit_updated_at before update
  on fg.unit
  for each row
  execute procedure fg_private.set_updated_at();

create trigger diagnostic_updated_at before update
  on fg.diagnostic
  for each row
  execute procedure fg_private.set_updated_at();

create trigger job_employee_updated_at before update
  on fg.job_employee
  for each row
  execute procedure fg_private.set_updated_at();

To define our trigger we ran three commands. First we created a function named set_updated_at in our fg_private schema because we want no one to directly call this function as it is simply a utility. fg_private.set_updated_at also returns a trigger and is implemented in PL/pgSQL.

After we define our fg_private.set_updated_at function, we can use it in the triggers we create with the CREATE TRIGGER command. The triggers will run before a row is updated by the UPDATE command and will execute the function on every row being updated.

Note: If you want to do some CPU intensive work in triggers, perhaps consider using Postgres’s pub/sub functionality by running the NOTIFY command in triggers and then use the LISTEN command in a worker service. If Node.js is your platform of choice, you could use the pg-pubsub package to make listening easier.


That’s about it as far as Postgres functions go! They are a fun, interesting, and useful topic to understand when it comes to good Postgres schema design. Always remember, the Postgres documentation is your best friend as you try to write your own functions. Some important documentation articles we mentioned for your reference are as follows:

Next up, we are going to learn about auth in Postgres and PostGraphQL!

Authentication and Authorization

Authentication and authorization is incredibly important whenever you build an application. You want your users to be able to login and out of your service, and only edit the content your platform has given them permission to edit. Postgres already has great support for authentication and authorization using a secure role based system, so PostGraphQL just bridges the gap between the Postgres role mechanisms and HTTP based authorization.

However, before we can dive into implementing authentication, we are missing some pretty important data in our schema. How are users supposed to even login? Not by guessing their first and last name one would hope, so we will define another table which will store user emails and passwords.

Storing Emails and Passwords

To store user emails and passwords we will create another table in the fg_private schema.

create table fg_private.person_account (
  person_id        uuid primary key references fg.person(id) on delete cascade,
  email            text not null unique check (email ~* '^.+@.+..+$'),
  phone            text not null,
  password_hash    text not null
);

comment on table fg_private.person_account is 'Private information about a person’s account.';
comment on column fg_private.person_account.person_id is 'The id of the person associated with this account.';
comment on column fg_private.person_account.email is 'The email address of the person.';
comment on column fg_private.person_account.password_hash is 'An opaque hash of the person’s password.';

Warning: Never store passwords in plaintext! The password_hash column will contain the user’s password after it has gone through a secure hashing algorithm like Bcrypt. Later in this tutorial we will show you how to securely hash a password in Postgres.

Why would we choose to create a new table in the fg_private schema instead of just adding columns to fg.person? There are a couple of answers to this question. The first and most fundamental is seperation of concerns. By moving email and password_hash to a second table we make it much harder to accidently select those values when reading fg.person. Also, users will not have the permission to directly query data from fg_private (as we will see) making this approach more secure. This approach is also good for PostGraphQL as the fg_private schema is never exposed in PostGraphQL, so you will never accidently expose password hashes in GraphQL.

Besides those arguments, moving the person’s account to a seperate table is also good database design in general. Say you have multiple types of users. Perhaps normal person users, and then ’brand‘ or ‘organization’ users. This pattern could easily allow you to go in that direction.

Note: The fg_private.person_account shares its primary key with fg.person. This way there can only be one fg_private.person_account for every fg.person, a one-to-one relationship.

Note: For an example of a much richer user profile/account/login schema, use Membership.db as a reference.

create table fg_private.employee_account (
  employee_id      uuid primary key references fg.employee(id) on delete cascade,
  tenant_id        uuid not null default current_setting('jwt.claims.tenant_id')::uuid,
  email            text not null unique check (email ~* '^.+@.+..+$'),
  phone            text not null,
  password_hash    text not null
);

comment on table fg_private.employee_account is 'Private information about a employee’s account.';
comment on column fg_private.employee_account.employee_id is 'The id of the employee associated with this account.';
comment on column fg_private.employee_account.email is 'The email address of the employee.';
comment on column fg_private.employee_account.password_hash is 'An opaque hash of the employee’s password.';

Registering Users

Before a user can log in, they need to have an account in our database. To register a user we are going to implement a Postgres function in PL/pgSQL which will create two rows. The first row will be the user’s profile inserted into fg.person, and the second will be an account inserted into fg_private.person_account.

Before we define the function, we know that we will want to hash the passwords coming into the function before inserting them into fg_private.person_account. To hash passwords we will need the Postgres pgcrypto extension. To add the extension, just do the following:

create extension if not exists "pgcrypto";

The pgcrypto extension should come with your Postgres distribution and gives us access to hashing functions like crypt and gen_salt which were specifically designed for hashing passwords.

Now that we have added pgcrypto to our database, let us define our function:

create function fg.register_console(
  company_name text,
  industry fg.tenant_industry,
  first_name text,
  last_name text,
  email text,
  phone text,
  password text
) returns fg.employee as $$
declare
  tenant fg.tenant;
  employee fg.employee;
begin
  insert into fg.tenant (company_name, industry) values
    (company_name, industry)
    returning * into tenant;

  insert into fg.employee (first_name, last_name, tenant_id) values
    (first_name, last_name, tenant.id)
    returning * into employee;

  insert into fg_private.employee_account (employee_id, tenant_id, email, phone, password_hash) values
    (employee.id, tenant.id, email, phone, crypt(password, gen_salt('bf')));

  return employee;
end;
$$ language plpgsql strict security definer;

comment on function fg.register_console(text, fg.tenant_industry, text, text, text, text, text) is 'Registers a single user and creates an account in our forum.';
create function fg.register_employee(
  first_name text,
  last_name text,
  title text,
  email text,
  phone_home text,
  phone_mobile_personal text,
  phone_mobile_work text,
  phone_other text,
  phone_work text,
  login_email text,
  phone text,
  password text
) returns fg.employee as $$
declare
  employee fg.employee;
begin
  insert into fg.employee (first_name, last_name, title, email, phone_home, phone_mobile_personal, phone_mobile_work, phone_other, phone_work) values
    (first_name, last_name, title, email, phone_home, phone_mobile_personal, phone_mobile_work, phone_other, phone_work)
    returning * into employee;

  insert into fg_private.employee_account (employee_id, email, phone, password_hash) values
    (employee.id, login_email, phone, crypt(password, gen_salt('bf')));

  return employee;
end;
$$ language plpgsql strict security definer;

comment on function fg.register_employee(text, text, text, text, text, text, text, text, text, text, text, text) is 'Registers a single employee and creates an account in our forum.';

Now lets create one for the portal:

create function fg.register_portal(
  first_name text,
  last_name text,
  email text,
  phone text,
  password text
) returns fg.person as $$
declare
  person fg.person;
begin
  insert into fg.person (first_name, last_name) values
    (first_name, last_name)
    returning * into person;

  insert into fg_private.person_account (person_id, email, phone, password_hash) values
    (person.id, email, phone, crypt(password, gen_salt('bf')));

  return person;
end;
$$ language plpgsql strict security definer;

comment on function fg.register_portal(text, text, text, text, text) is 'Registers a single user and creates an account in our forum.';

If you do not understand what is going on here, do not worry, writing PL/pgSQL requires some trial and error along with some StackOverflow searching. What’s new here compared to our other functions is that we have a new block, declare, above our function implementation which starts with begin. In that block we declare our intention to use a variable called person of type fg.person. Then, in our first insert statement, the row we insert will be saved into that person variable.

After we insert a profile into fg.person, we use the pgcrypto extension in the expression crypt(password, gen_salt('bf')) to hash the user’s password before inserting into fg_private.person_account. This way we aren’t storing the password in plaintext. Read the documentation for pgcrypto on Password Hashing Functions to learn more about these functions and their characteristics.

Warning: Be very careful with logging, while we encrypt our passwords here it may be possible that in a query or server log the password will be recorded in plain text! Be careful to configure your Postgres logs so this isn’t the case. PostGraphQL will never log the value of any variables the client gives it. Being careful with your logs and passwords is true in any system, but especially this one.

For an overview of passwords in Postgres past the pgcrypto documentation, see the answer to the StackOverflow question “How can I hash passwords in Postgres?

At the end of the implementation you will see language plpgsql strict security definer. language plpgsql we already understand, but the other words are new. The word strict means that if the function gets null input, then the output will be automatically null as well and Postgres won’t call the function. That is password cannot be null or first_name cannot be null otherwise the result will also be null and nothing will be executed. The words security definer mean that this function is executed with the privileges of the Postgres user who created it. Remember how we said users would never be able to insert into fg_private.person_account? Well this function can insert into fg_private.person_account because it uses the privileges of the definer.

Warning: Make sure that when you create a function with security definer there are no ‘holes’ a user could use to see or mutate more data than they are not allowed to. Since the above is a simple function, we are fine. If you don’t need security definer, try not to use it.

This function will create a user and their account, but how will we log the user in? Before we define a function which allows users to login, sign-in, authenticate, whatever you want to call it let us go over how auth works at a high level in PostGraphQL. While this article is trying to be somewhat PostGraphQL agnostic, the next two sections will be specific to PostGraphQL, but useful to anyone wanting to learn just a little bit more about Postgres and JSON Web Tokens (JWTs).

rabbitmq

RabbitMQ in 5 Minutes

Core Concepts:

  • Product emits messages to exchange
  • Consumer recieves messages from queue
  • Binding connects an exchange with a queue using binding key
  • Exchange compares routing key with binding key
  • Message distribution depends on exchange type
  • Exchange types: fanout, direct, topic, and headers

To make sending messages a bit easier you can setup the following functions in your database

create or replace function rabbitmq.send_message(channel text, routing_key text, message text) returns void as $$
	select	pg_notify(channel, routing_key || '|' || message);
$$ stable language sql;

create or replace function rabbitmq.on_row_change() returns trigger as $$
  declare
    routing_key text;
    row record;
  begin
    routing_key := 'row_change'
                   '.table-'::text || TG_TABLE_NAME::text || 
                   '.event-'::text || TG_OP::text;
    if (TG_OP = 'DELETE') then
        row := old;
    elsif (TG_OP = 'UPDATE') then
        row := new;
    elsif (TG_OP = 'INSERT') then
        row := new;
    end if;
    raise notice 'Routing key: %', routing_key;
    -- change 'events' to the desired channel/exchange name
    perform rabbitmq.send_message('pgchannel2', routing_key, row_to_json(row)::text);
    return null;
  end;
$$ stable language plpgsql;

After this, you can send events from your stored procedures like this

rabbitmq.send_message('topic_exchange', 'routing-key', 'Hi!');

You can stream row changes by attaching a trigger to tables

create trigger tenant_change_event
after insert or update or delete on fg.tenant
for each row execute procedure rabbitmq.on_row_change();

create trigger person_account_change_event
after insert or update or delete on fg_private.person_account
for each row execute procedure rabbitmq.on_row_change();

Postgres Roles

When a user logs in, we want them to make their queries using a specific PostGraphQL role. Using that role we can define rules that restrict what data the user may access. So what roles do we need to define for our forum example? Remember when we were connecting to Postgres and we used a URL like postgres://localhost:5432/mydb? Well, when you use a connection string like that, you are logging into Postgres using your computer account’s username and no password. Say your computer account username is buddy, then connecting with the URL postgres://localhost:5432/mydb, would be the same as connecting with the URL postgres://buddy@localhost:5432/mydb. If you wanted to connect to your Postgres database with a password it would look like postgres://buddy:password@localhost:5432/mydb. When you run Postgres locally, this account will probably be the superuser. So when you run postgraphql -c postgres://localhost:5432/mydb, you are running PostGraphQL with superuser privileges. To change that let’s create a role that PostGraphQL can use to connect to our database:

create role fg_postgraphql login password 'xyz';
create role proxyuser login password 'fleetgridisgreat';

We create this fg_postgraphql role with the CREATE ROLE command. We want to make sure our PostGraphQL role can login so we specify that with the login option and we give the user a password of ‘xyz’ with the password option. Now we will start PostGraphQL as such:

postgraphql -c postgres://fg_postgraphql:xyz@localhost:5432/mydb

When a user who does not have a JWT token makes a request to Postgres, we do not want that user to have the privileges we will give to the fg_postgraphql role, so instead we will create another role.

create role fg_anonymous;
grant fg_anonymous to fg_postgraphql;

Here we use CREATE ROLE again. This role cannot login so it does not have the login option, or a password. We also use the GRANT command to grant access to the fg_anonymous role to the fg_postgraphql role. Now, the fg_postgraphql role can control and become the fg_anonymous role. If we did not use that grant, we could not change into the fg_anonymous role in PostGraphQL.

In the way that kubernetes connects to cloudsql it uses a proxy so we'll need to add the following for that.

grant fg_anonymous to proxyuser;

Now we will start our server like so:

postgraphql   --connection postgres://fg_postgraphql:xyz@localhost:5432/mydb   --default-role fg_anonymous

There is one more role we want to create. When a user logs in we don’t want them to use the fg_postgraphql role, or the basic fg_anonymous role. So instead we will create a role that all of our logged in users will authorize with. We will call it fg_person and similarly grant it to the fg_postgraphql role.

create role fg_person;
create role fg_employee;
grant fg_person to fg_postgraphql;
grant fg_employee to fg_postgraphql;

Warning: The fg_postgraphql role will have all of the permissions of the roles granted to it. So it can do everything fg_anonymous can do and everything fg_person can do. This is why having a default role is important. We would not want an anonymous user to have admin access level because we have granted an admin role to fg_postgraphql.

we'll also try this for the proxyuser.

grant fg_person to proxyuser;

Ok, so now we have three roles. fg_postgraphql, fg_anonymous, and fg_person. We know how fg_postgraphql and fg_anonymous get used, but how do we know when a user is logged in and should be using fg_person? The answer is JSON Web Tokens.

JSON Web Tokens

PostGraphQL uses JSON Web Tokens (JWTs) for authorization. A JWT is just a JSON object that has been hashed and cryptographically signed to confirm the identity of its contents. So an object like:

{
  "a": 1,
  "b": 2,
  "c": 3
}

Would turn into a token that looks like:

eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJhIjoxLCJiIjoyLCJjIjozfQ.hxhGCCCmGV9nT1slief1WgEsOsfdnlVizNrODxfh1M8

Warning: The information in a JWT can be read by anyone, so do not put private information in a JWT. What makes JWTs secure is that unless they were signed by our secret, we can not accept the information inside the JWT as truth.

This allows PostGraphQL to securely make claims about who a user is. Attackers would not be able to fake a claim unless they had access to the private ‘secret’ you define when you start PostGraphQL with the --secret option.

When PostGraphQL gets a JWT from an HTTP request’s Authorization header, like so:

Authorization: Bearer eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJhIjoxLCJiIjoyLCJjIjozfQ.hxhGCCCmGV9nT1slief1WgEsOsfdnlVizNrODxfh1M8

It will verify the token using the secret, and then will serialize the claims in that token to the database. So for our token above PostGraphQL would effectively run:

set local jwt.claims.a to 1;
set local jwt.claims.b to 2;
set local jwt.claims.c to 3;

This way your JWT is accessible in your database rules. To get these values back out in SQL, just run the following function:

select current_setting('jwt.claims.a');

All of the ‘claims’ or properties on the JWT are serialized to the database in this way, with one exception. If you have a role property in your JWT, PostGraphQL will also set the Postgres role of the local transaction. So say you had a role of fg_person. PostGraphQL would run:

set local role to 'fg_person'
set local jwt.claims.role to 'fg_person'

Now, the user would have the permissions of the fg_person role as they execute their query.

Warning: Unless explicitly set, JWTs never expire. Once they have been issued they may never be invalidated. This is both good and bad, good in that JWTs are fast in not requiring a database lookup. Bad in that if an attacker gets their hands on a JWT you can’t stop them from using it until the token expires.

A solution to this is to use very short expiration times on your tokens and/or to use refresh tokens. A refresh token you would use whenever your JWT expires to get a new JWT without prompting the user for their password again. Refresh tokens would be stored in the database so you could easily invalidate refresh tokens.

We now know how PostGraphQL uses JWTs to authorize the user, but how does PostGraphQL create a JWT? Stay tuned.

Statistics

create type fg.stats_count as (
  jobs bigint,
  employees bigint,
  fielders bigint,
  companies bigint,
  employees_quality_control_approved bigint,
  fielders_quality_control_approved bigint
);

create function fg.stats() returns fg.stats_count as $$
SELECT 
	(select count(*) from fg.job) AS jobs,
	(select count(*) from fg.employee) AS employees,
	(select count(*) from fg.person) AS fielders,
	(select count(*) from fg.tenant) AS companies,
	(select count(*) from fg.employee as q where q.is_quality_control_approved = true) AS employees_quality_control_approved,
  (select count(*) from fg.person as q where q.is_quality_control_approved = true) AS fielders_quality_control_approved;
$$ language sql stable security definer;

comment on function fg.stats() is 'Returns the website statistics.';

Logging In

You can pass an option to PostGraphQL, called --token <identifier> in the CLI, which takes a composite type identifier. PostGraphQL will turn this type into a JWT wherever you see it in the GraphQL output. So let’s define the type we will use for our JWTs:

create type fg.jwt_token as (
  role text,
  person_id uuid,
  employee_id uuid,
  tenant_id uuid
);

That’s it. We are using the CREATE TYPE command again as we did before to create an enum type. This time we are creating a composite type. The definition for a composite type looks very much like the definition of a table type, except a composite type cannot store rows. i.e. you can’t INSERT, SELECT, UPDATE, or DELETE from a composite type. While you can’t store rows in a composite type, PostGraphQL can turn a composite type into a JWT. Now that we’ve defined this type we will want to start PostGraphQL with the --token flag:

postgraphql --token fg.jwt_token

Next we need to create the function which will actually return the token:

create function fg.authenticate_console(
  email text,
  password text
) returns fg.jwt_token as $$
declare
  employee fg_private.employee_account;
begin
  select e.* into employee
  from fg_private.employee_account as e
  where e.email = $1;

  if employee.password_hash = crypt(password, employee.password_hash) then
    return ('fg_employee', null, employee.employee_id, employee.tenant_id)::fg.jwt_token;
  else
    return null;
  end if;
end;
$$ language plpgsql strict security definer;

comment on function fg.authenticate_console(text, text) is 'Creates a JWT token that will securely identify a person and give them certain permissions.';

and one for fielder accounts:

create function fg.authenticate_portal(
  email text,
  password text
) returns fg.jwt_token as $$
declare
  person fg_private.person_account;
begin
  select p.* into person
  from fg_private.person_account as p
  where p.email = $1;

  if person.password_hash = crypt(password, person.password_hash) then
    return ('fg_person', person.person_id, null, null)::fg.jwt_token;
  else
    return null;
  end if;
end;
$$ language plpgsql strict security definer;

comment on function fg.authenticate_portal(text, text) is 'Creates a JWT token that will securely identify a person and give them certain permissions.';

This function will return null if the user failed to authenticate, and a JWT token if the user succeeds. Returning null could mean that the password was incorrect, a user with their email doesn’t exist, or the client forgot to pass email and/or password arguments. It is then up to the client to raise an error when encountering null. If a user with the provided email does exist, and the provided password checks out with password_hash in fg_private.person_account then we return an instance of fg.jwt_token which will then be converted into an actual JWT by PostGraphQL.

There are two main parts to our function body. The first is:

select a.* into account
from fg_private.person_account as a
where a.email = $1;

This code will select a single account from fg_private.person_account using the provided email value. The $1 here is just another way to write the email argument. If we had wrote email = email or even a.email = email, Postgres would not have known which email we were referring to, so instead we just used a substitute for the email argument which depends on its placement in the identifer $1. If we succesfully find a person with that email, we store it in the account variable. If we do not find anything, account will be null. The second part of our function is:

if account.password_hash = crypt(password, account.password_hash) then
  return ('fg_person', account.person_id)::fg.jwt_token;
else
  return null;
end if;

This is an if/else statement that checks to see if the plaintext password argument we were provided matches the password hash that was stored in our fg_private.person_account’s password_hash table. If there is a match, then we return a JWT token. Otherwise we return null. The password match check is done in the code account.password_hash = crypt(password, account.password_hash). To better understand how this works, read the documentation for pgcrypto on password hashing functions.

In order to construct a fg.jwt_token we use the Postgres composite value input syntax which looks like: ('fg_person', account.person_id). Then we cast that composite value with ::fg.jwt_token. The order in which the values go is the order in which they were originally defined. Since we defined role first and person_id second, this JWT will have a role of fg_person and a person_id of account.person_id.

Warning: Be careful about logging around this function too.

Now that we know how to get JWTs for our users, let’s use the JWTs.

Using the Authorized User

Before we define permissions for our user, let’s utilize the fact that they are logged in by defining a quick Postgres function.

create function fg.current_person() returns fg.person as $$
  select *
  from fg.person
  where id = current_setting('jwt.claims.person_id')::uuid
$$ language sql stable;

comment on function fg.current_person() is 'Gets the person who was identified by our JWT.';

one for the current employee...

create function fg.current_employee() returns fg.employee as $$
  select *
  from fg.employee
  where id = current_setting('jwt.claims.employee_id')::uuid
$$ language sql stable;

comment on function fg.current_employee() is 'Gets the employee who was identified by our JWT.';

and one for the current tenant...

create function fg.current_tenant() returns fg.tenant as $$
  select *
  from fg.tenant
  where id = current_setting('jwt.claims.tenant_id')::uuid
$$ language sql stable;

comment on function fg.current_tenant() is 'Gets the tenant who was identified by our JWT.';

This is a simple function that we can use in PostGraphQL or our database to get the person who is currently executing the query — by means of the token in the request header. The one new concept here is current_setting('jwt.claims.person_id')::uuid. As we discussed before, PostGraphQL will serialize your JWT to the database in the form of transaction local settings. Using the current_setting function is how we access those settings. Also note that we cast the value to an uuid with ::uuid. This is because the Postgres current_setting function will always return a string, if you need another data type, you will likely need to cast to that data type.

Now, let’s use the JWT to define permissions.

Grants

The highest level of permission that can be given to roles using the Postgres are access privileges assigned using the GRANT command. The access privileges defined by GRANT work on no smaller level than the table level. As you can allow a role to select an value from a table, or delete any value in a table. We will look at how to restrict access on a row level next.

-- after schema creation and before function creation
alter default privileges revoke execute on functions from public;

grant usage on schema fg to fg_anonymous, fg_person, fg_employee;
grant all on schema rabbitmq TO fg_employee;

grant select on table fg.person to fg_anonymous, fg_person, fg_employee;
grant update, delete on table fg.person to fg_person, fg_employee;

grant select on table fg.post to fg_anonymous, fg_person, fg_employee;
grant insert, update, delete on table fg.post to fg_person, fg_employee;

grant select on table fg.tenant to fg_anonymous, fg_person, fg_employee;
grant insert, update, delete on table fg.tenant to fg_person, fg_employee;

grant select on table fg.customer_address to fg_anonymous, fg_person, fg_employee;
grant insert, update, delete on table fg.customer_address to fg_person, fg_employee;

grant select on table fg.customer_contact to fg_anonymous, fg_person, fg_employee;
grant insert, update, delete on table fg.customer_contact to fg_person, fg_employee;

grant select on table fg.customer to fg_anonymous, fg_person, fg_employee;
grant insert, update, delete on table fg.customer to fg_person, fg_employee;

grant select on table fg.tax_rate to fg_anonymous, fg_person, fg_employee;
grant insert, update, delete on table fg.tax_rate to fg_person, fg_employee;

grant select on table fg.job to fg_anonymous, fg_person, fg_employee;
grant insert, update, delete on table fg.job to fg_person, fg_employee;

grant select on table fg.job_item to fg_anonymous, fg_person, fg_employee;
grant insert, update, delete on table fg.job_item to fg_person, fg_employee;

grant select on table fg.item to fg_anonymous, fg_person, fg_employee;
grant insert, update, delete on table fg.item to fg_person, fg_employee;

grant select on table fg.item_category to fg_anonymous, fg_person, fg_employee;
grant insert, update, delete on table fg.item_category to fg_person, fg_employee;

grant select on table fg.unit to fg_anonymous, fg_person, fg_employee;
grant insert, update, delete on table fg.unit to fg_person, fg_employee;

grant select on table fg.diagnostic to fg_anonymous, fg_person, fg_employee;
grant insert, update, delete on table fg.diagnostic to fg_person, fg_employee;

grant select on table fg.employee to fg_anonymous, fg_person, fg_employee;
grant insert, update, delete on table fg.employee to fg_person, fg_employee;

-- grant usage on sequence fg.post_id_seq to fg_person;

grant execute on function fg.person_full_name(fg.person) to fg_anonymous, fg_person, fg_employee;
grant execute on function fg.post_summary(fg.post, integer, text) to fg_anonymous, fg_person, fg_employee;
grant execute on function fg.person_latest_post(fg.person) to fg_anonymous, fg_person, fg_employee;
grant execute on function fg.search_posts(text) to fg_anonymous, fg_person, fg_employee;
grant execute on function fg.authenticate_console(text, text) to fg_anonymous, fg_person, fg_employee;
grant execute on function fg.authenticate_portal(text, text) to fg_anonymous, fg_person, fg_employee;
grant execute on function fg.stats() to fg_anonymous, fg_person, fg_employee;
grant execute on function fg.current_tenant() to fg_anonymous, fg_person, fg_employee;
grant execute on function fg.current_person() to fg_anonymous, fg_person, fg_employee;
grant execute on function fg.current_employee() to fg_anonymous, fg_person, fg_employee;
grant execute on function fg.register_console(text, fg.tenant_industry, text, text, text, text, text) to fg_anonymous;
grant execute on function fg.register_employee(text, text, text, text, text, text, text, text, text, text, text, text) to fg_employee;
grant execute on function fg.register_portal(text, text, text, text, text) to fg_anonymous;
grant execute on function rabbitmq.send_message(text, text, text) to fg_employee;

See how we had to grant permissions on every single Postgres object we have defined so far? Postgres permissions work as a whitelist and not a blacklist (except for functions), so therefore no one has more access than you explicitly give them. Let’s walk through the grants:

  1. alter default privileges ...: By default, functions can be executable by public. Since we're applying our fine-grained control over function permissions here, we remove the default grant. Note that this line needs to be placed before any function definition.
  2. grant usage on schema fg to fg_anonymous, fg_person: We say that anonymous users (fg_anonymous) and logged in users (fg_person) may use the objects in the fg schema. This does not mean that those roles can use anything they want in the schema, it just allows the roles to know the schema exists. Also note that we did not grant usage for the fg_private schema.
  3. grant select on table fg.person to fg_anonymous, fg_person: We give anonymous users and logged in users the ability to read all of the rows in the fg.person table.
  4. grant update, delete on table fg.person to fg_person: Here we give only logged in users the ability to update and delete rows from the fg.person table. This means that anonymous users can never update or delete a person. However, it does mean that users can update and delete any rows in the table. We will fix this later.
  5. grant select ... and grant insert, update, delete ...: We do the same thing with these two grants as we did with the grants above. The only difference here is that we also give signed in users the ability to insert into fg.post. We do not allow anyone to insert directly into fg.person, instead users should use the fg.register_person function.
  6. grant usage on sequence fg.post_id_seq to fg_person: When a user creates a new fg.post they will also need to get the next value in the fg.post_id_seq because we use the serial data type for the id column. A sequence also exists for our person table (fg.person_id_seq), but since we are only creating people through fg.register_person and that function specifies security definer, we don’t need to grant access to the person id sequence.
  7. grant execute ...: We have to give the anonymous user and logged in users access to all of the Postgres functions we define. All of the functions are executable by both types of users, except fg.register_person which we only let anonymous users execute. There’s no need for logged in users to register a new user!

This provides basic permissions for all of our Postgres objects, but as we mentioned before users can update and delete all and any persons or posts. For obvious reasons we don’t want this, so let’s define row level security next.

Row Level Security

In Postgres 9.5 (released January 2016) Row Level Security (RLS) was introduced. RLS allows us to specify access to the data in our Postgres databases on a row level instead of a table level. In order to enable row level security on our tables we first need to run the following:

alter table fg.person enable row level security;
alter table fg.post enable row level security;

Before running these commands, the fg_person and fg_anonymous roles could see every row in the table with a select * from fg.person query. After running these two commands those same roles can’t. By enabling row level security, our roles don’t have any access to read or write to a table that you don’t explicitly give, so to re-enable access to all the rows we will define RLS policies with the CREATE POLICY command.

create policy select_person on fg.person for select
  using (true);

create policy select_post on fg.post for select
  using (true);

Now both anonymous users and logged in users can see all of our fg.person and fg.post rows again. We also want signed in users to be able to only update and delete their own row in fg.person.

create policy update_person on fg.person for update to fg_person, fg_employee
  using (id = current_setting('jwt.claims.person_id')::uuid);

create policy delete_person on fg.person for delete to fg_person, fg_employee
  using (id = current_setting('jwt.claims.person_id')::uuid);

We use the current person_id from our JWT and only allow updates and deletes on rows with the same id. Also note how we added to fg_person. This is because we only want these policies to apply for the fg_person role.

That’s all we need to define for our person table. Now let’s define three policies for our posts table. One for INSERT, UPDATE, and DELETE.

create policy insert_post on fg.post for insert to fg_person, fg_employee
  with check (author_id = current_setting('jwt.claims.person_id')::uuid);

create policy update_post on fg.post for update to fg_person, fg_employee
  using (author_id = current_setting('jwt.claims.person_id')::uuid);

create policy delete_post on fg.post for delete to fg_person, fg_employee
  using (author_id = current_setting('jwt.claims.person_id')::uuid);

These policies are very similar to the ones before, except that the insert_post policy uses with check instead of using like our other policies. The difference between with check and using is roughly that using is applied before any operation occurs to the table’s rows. So in the case of updating a post, one could not update a row that does not have the appropriate author_id in the first place. with check is run after an operation is applied. If the with check fails the operation will be rejected. So in the case of an insert, Postgres sets all of the columns as specified and then compares against with check on the new row. You must use with check with INSERT commands because there are no rows to compare against before insertion, and you must use using with DELETE commands because a delete changes no rows only removes current ones.

That’s it! We have succesfully creating a Postgres schema embedded with our business logic. When we use this schema with PostGraphQL we will get a well designed GraphQL API that we can be used in our frontend application.

The final argument list for starting our PostGraphQL server using the CLI would be as follows:

postgraphql   --connection postgres://fg_postgraphql:xyz@localhost:5432   --schema fg   --default-role fg_anonymous   --secret fleetgridisgreat   --token fg.jwt_token

employee

alter table fg.employee enable row level security;

create policy select_employee on fg.employee for select to fg_person, fg_employee
  using (tenant_id = current_setting('jwt.claims.tenant_id')::uuid);

create policy insert_employee on fg.employee for insert to fg_person, fg_employee
  with check (tenant_id = current_setting('jwt.claims.tenant_id')::uuid);

create policy update_employee on fg.employee for update to fg_person, fg_employee
  using (tenant_id = current_setting('jwt.claims.tenant_id')::uuid);

create policy delete_employee on fg.employee for delete to fg_person, fg_employee
  using (tenant_id = current_setting('jwt.claims.tenant_id')::uuid);

tenant

alter table fg.tenant enable row level security;

create policy select_tenant on fg.tenant for select to fg_person, fg_employee
  using (id = current_setting('jwt.claims.tenant_id')::uuid);

create policy insert_tenant on fg.tenant for insert to fg_person, fg_employee
  with check (id = current_setting('jwt.claims.tenant_id')::uuid);

create policy update_tenant on fg.tenant for update to fg_person, fg_employee
  using (id = current_setting('jwt.claims.tenant_id')::uuid);

create policy delete_tenant on fg.tenant for delete to fg_person, fg_employee
  using (id = current_setting('jwt.claims.tenant_id')::uuid);

tenant_open_close

alter table fg.tenant_open_close enable row level security;

create policy select_tenant_open_close on fg.tenant_open_close for select to fg_person, fg_employee
  using (tenant_id = current_setting('jwt.claims.tenant_id')::uuid);

create policy insert_tenant_open_close on fg.tenant_open_close for insert to fg_person, fg_employee
  with check (tenant_id = current_setting('jwt.claims.tenant_id')::uuid);

create policy update_tenant_open_close on fg.tenant_open_close for update to fg_person, fg_employee
  using (tenant_id = current_setting('jwt.claims.tenant_id')::uuid);

create policy delete_tenant_open_close on fg.tenant_open_close for delete to fg_person, fg_employee
  using (tenant_id = current_setting('jwt.claims.tenant_id')::uuid);

customer_address

alter table fg.customer_address enable row level security;

create policy select_customer_address on fg.customer_address for select to fg_person, fg_employee
  using (tenant_id = current_setting('jwt.claims.tenant_id')::uuid);

create policy insert_customer_address on fg.customer_address for insert to fg_person, fg_employee
  with check (tenant_id = current_setting('jwt.claims.tenant_id')::uuid);

create policy update_customer_address on fg.customer_address for update to fg_person, fg_employee
  using (tenant_id = current_setting('jwt.claims.tenant_id')::uuid);

create policy delete_customer_address on fg.customer_address for delete to fg_person, fg_employee
  using (tenant_id = current_setting('jwt.claims.tenant_id')::uuid);

customer_contact

alter table fg.customer_contact enable row level security;

create policy select_customer_contact on fg.customer_contact for select to fg_person, fg_employee
  using (tenant_id = current_setting('jwt.claims.tenant_id')::uuid);

create policy insert_customer_contact on fg.customer_contact for insert to fg_person, fg_employee
  with check (tenant_id = current_setting('jwt.claims.tenant_id')::uuid);

create policy update_customer_contact on fg.customer_contact for update to fg_person, fg_employee
  using (tenant_id = current_setting('jwt.claims.tenant_id')::uuid);

create policy delete_customer_contact on fg.customer_contact for delete to fg_person, fg_employee
  using (tenant_id = current_setting('jwt.claims.tenant_id')::uuid);

customer

alter table fg.customer enable row level security;

create policy select_customer on fg.customer for select to fg_person, fg_employee
  using (tenant_id = current_setting('jwt.claims.tenant_id')::uuid);

create policy insert_customer on fg.customer for insert to fg_person, fg_employee
  with check (tenant_id = current_setting('jwt.claims.tenant_id')::uuid);

create policy update_customer on fg.customer for update to fg_person, fg_employee
  using (tenant_id = current_setting('jwt.claims.tenant_id')::uuid);

create policy delete_customer on fg.customer for delete to fg_person, fg_employee
  using (tenant_id = current_setting('jwt.claims.tenant_id')::uuid);

tax_rate

alter table fg.tax_rate enable row level security;

create policy select_tax_rate on fg.tax_rate for select to fg_person, fg_employee
  using (tenant_id = current_setting('jwt.claims.tenant_id')::uuid);

create policy insert_tax_rate on fg.tax_rate for insert to fg_person, fg_employee
  with check (tenant_id = current_setting('jwt.claims.tenant_id')::uuid);

create policy update_tax_rate on fg.tax_rate for update to fg_person, fg_employee
  using (tenant_id = current_setting('jwt.claims.tenant_id')::uuid);

create policy delete_tax_rate on fg.tax_rate for delete to fg_person, fg_employee
  using (tenant_id = current_setting('jwt.claims.tenant_id')::uuid);

job

alter table fg.job enable row level security;

create policy select_job on fg.job for select to fg_person, fg_employee
  using (tenant_id = current_setting('jwt.claims.tenant_id')::uuid);

create policy insert_job on fg.job for insert to fg_person, fg_employee
  with check (tenant_id = current_setting('jwt.claims.tenant_id')::uuid);

create policy update_job on fg.job for update to fg_person, fg_employee
  using (tenant_id = current_setting('jwt.claims.tenant_id')::uuid);

create policy delete_job on fg.job for delete to fg_person, fg_employee
  using (tenant_id = current_setting('jwt.claims.tenant_id')::uuid);

job_item

alter table fg.job_item enable row level security;

create policy select_job_item on fg.job_item for select to fg_person, fg_employee
  using (tenant_id = current_setting('jwt.claims.tenant_id')::uuid);

create policy insert_job_item on fg.job_item for insert to fg_person, fg_employee
  with check (tenant_id = current_setting('jwt.claims.tenant_id')::uuid);

create policy update_job_item on fg.job_item for update to fg_person, fg_employee
  using (tenant_id = current_setting('jwt.claims.tenant_id')::uuid);

create policy delete_job_item on fg.job_item for delete to fg_person, fg_employee
  using (tenant_id = current_setting('jwt.claims.tenant_id')::uuid);

item

alter table fg.item enable row level security;

create policy select_item on fg.item for select to fg_person, fg_employee
  using (tenant_id = current_setting('jwt.claims.tenant_id')::uuid);

create policy insert_item on fg.item for insert to fg_person, fg_employee
  with check (tenant_id = current_setting('jwt.claims.tenant_id')::uuid);

create policy update_item on fg.item for update to fg_person, fg_employee
  using (tenant_id = current_setting('jwt.claims.tenant_id')::uuid);

create policy delete_item on fg.item for delete to fg_person, fg_employee
  using (tenant_id = current_setting('jwt.claims.tenant_id')::uuid);

item_category

alter table fg.item_category enable row level security;

create policy select_item_category on fg.item_category for select to fg_person, fg_employee
  using (tenant_id = current_setting('jwt.claims.tenant_id')::uuid);

create policy insert_item_category on fg.item_category for insert to fg_person, fg_employee
  with check (tenant_id = current_setting('jwt.claims.tenant_id')::uuid);

create policy update_item_category on fg.item_category for update to fg_person, fg_employee
  using (tenant_id = current_setting('jwt.claims.tenant_id')::uuid);

create policy delete_item_category on fg.item_category for delete to fg_person, fg_employee
  using (tenant_id = current_setting('jwt.claims.tenant_id')::uuid);

unit

alter table fg.unit enable row level security;

create policy select_unit on fg.unit for select to fg_person, fg_employee
  using (tenant_id = current_setting('jwt.claims.tenant_id')::uuid);

create policy insert_unit on fg.unit for insert to fg_person, fg_employee
  with check (tenant_id = current_setting('jwt.claims.tenant_id')::uuid);

create policy update_unit on fg.unit for update to fg_person, fg_employee
  using (tenant_id = current_setting('jwt.claims.tenant_id')::uuid);

create policy delete_unit on fg.unit for delete to fg_person, fg_employee
  using (tenant_id = current_setting('jwt.claims.tenant_id')::uuid);

diagnostic

alter table fg.diagnostic enable row level security;

create policy select_diagnostic on fg.diagnostic for select to fg_person, fg_employee
  using (tenant_id = current_setting('jwt.claims.tenant_id')::uuid);

create policy insert_diagnostic on fg.diagnostic for insert to fg_person, fg_employee
  with check (tenant_id = current_setting('jwt.claims.tenant_id')::uuid);

create policy update_diagnostic on fg.diagnostic for update to fg_person, fg_employee
  using (tenant_id = current_setting('jwt.claims.tenant_id')::uuid);

create policy delete_diagnostic on fg.diagnostic for delete to fg_person, fg_employee
  using (tenant_id = current_setting('jwt.claims.tenant_id')::uuid);

job_employee

alter table fg.job_employee enable row level security;

create policy select_job_employee on fg.job_employee for select to fg_person, fg_employee
  using (tenant_id = current_setting('jwt.claims.tenant_id')::uuid);

create policy insert_job_employee on fg.job_employee for insert to fg_person, fg_employee
  with check (tenant_id = current_setting('jwt.claims.tenant_id')::uuid);

create policy update_job_employee on fg.job_employee for update to fg_person, fg_employee
  using (tenant_id = current_setting('jwt.claims.tenant_id')::uuid);

create policy delete_job_employee on fg.job_employee for delete to fg_person, fg_employee
  using (tenant_id = current_setting('jwt.claims.tenant_id')::uuid);

Conclusion

You should now be equipped with the knowledge to go out and design your own Postgres schema. If you have any questions, encounter a bug, or just want to say thank you, don’t hesitate to open an issue, we’d love to hear from you. The PostGraphQL community wants to invest in making you a productive developer so that you can invest back into PostGraphQL.

<!-- TODO: More next steps and calls to action -->
  

About

We are technologically disrupting industries by focusing on one thing; field service.

fleetgrid is owned and operated by the Burandt family.

This site is copyright © fleetgrid 2018. Design and logo copyright © fleetgrid 2018.

v0.0.28

2

What are you waiting for?

Get started with fleetgrid today. It takes minutes to fill in our signup form then just seconds to add your first customer; you will be scheduling work orders and getting paid before you know it.

fleetgrid

fleetgrid

9
2