tavern/migrations/20200405151030_acls.up.sql

create table if not exists public.acls
(
    id         uuid                     not null
        constraint acls_pk primary key,
    created_at timestamp with time zone not null,
    updated_at timestamp with time zone not null,
    scope      uuid                     not null,
    target     varchar                  not null,
    action     integer                  not null,
    wild       bool default false       not null,
    constraint acls_scope_action_uindex
        unique (scope, target)
);

UPDATE actor_keys AS ak
SET key_id = a.payload -> 'publicKey' ->> 'id'
FROM actors AS a
WHERE ak.actor_id = a.id
  AND ak.key_id = '';

ALTER TABLE acls
    ADD CONSTRAINT acls_checks CHECK (target <> '' AND action >= 0 AND action <= 2);

ALTER TABLE actor_keys
    ADD CONSTRAINT actor_keys_checks CHECK (key_id <> '' AND pem <> '');

ALTER TABLE actor_aliases
    ADD CONSTRAINT actor_aliases_checks CHECK (alias <> '');

ALTER TABLE actors
    ADD CONSTRAINT actors_checks CHECK (actor_id <> '');

ALTER TABLE groups
    ADD CONSTRAINT groups_checks CHECK (name <> '' AND public_key <> '' AND private_key <> '' AND display_name <> '');

ALTER TABLE objects
    ADD CONSTRAINT objects_checks CHECK (object_id <> '');

ALTER TABLE object_events
    ADD CONSTRAINT object_events_checks CHECK (activity_id <> '');

ALTER TABLE users
    ADD CONSTRAINT users_checks CHECK (email <> '' AND password <> '' AND name <> '' AND public_key <> '' AND
                                       private_key <> '' AND display_name <> '');