edge_props_schema.sql

create extension
    btree_gin;

drop table
    if exists public.codex_edge_props;

create table
    public.codex_edge_props (
        id serial primary key,
        type varchar(240) not null,
        key varchar(240) not null,
        props jsonb default '{}' not null,
        -- used to cascade props to children (and cascade delete)
        context_keys varchar(240) array default '{}' not null,
        -- meta fields
        created_by_id integer references auth_user not null,
        updated_dtime timestamp default now(),
        created_dtime timestamp default now() not null
    );


-- create index on type
create index codex_edge_props_type on public.codex_edge_props(type);

create index codex_edge_props_key on public.codex_edge_props(key);

-- https://stackoverflow.com/a/51580052
-- http://www.databasesoup.com/2015/01/tag-all-things.html
create index codex_edge_pros_context_keys on public.codex_edge_props using GIN(context_keys);

-- add some test data
insert into public.codex_edge_props (type, key, props, context_keys, created_by_id) 
  values ('theme', 'space:1', '{"color": "#00ff00"}', '{}', 1);

-- perms
grant select, insert, update, delete on public.codex_edge_props to pathwright_read_only;
-- grant the id sequence to pathwright_read_only
grant usage, select on public.codex_edge_props_id_seq to pathwright_read_only;