74 lines
2.3 KiB
PL/PgSQL
74 lines
2.3 KiB
PL/PgSQL
-- Bell owns Tenant/Site/quota truth. Run as the cluster administrator or a
|
|
-- migration role that can SET ROLE to bell_app.
|
|
|
|
CREATE SCHEMA IF NOT EXISTS bell AUTHORIZATION bell_app;
|
|
ALTER SCHEMA bell OWNER TO bell_app;
|
|
|
|
CREATE TABLE IF NOT EXISTS bell.schema_migrations (
|
|
version bigint PRIMARY KEY,
|
|
applied_at timestamptz NOT NULL DEFAULT clock_timestamp()
|
|
);
|
|
ALTER TABLE bell.schema_migrations OWNER TO bell_app;
|
|
|
|
CREATE TABLE IF NOT EXISTS bell.sites (
|
|
tenant_id text NOT NULL,
|
|
id text NOT NULL,
|
|
name text NOT NULL,
|
|
max_video_channels smallint NOT NULL DEFAULT 16,
|
|
version bigint NOT NULL DEFAULT 1,
|
|
created_at timestamptz NOT NULL DEFAULT clock_timestamp(),
|
|
updated_at timestamptz NOT NULL DEFAULT clock_timestamp(),
|
|
deleted_at timestamptz,
|
|
PRIMARY KEY (tenant_id, id),
|
|
CONSTRAINT bell_sites_video_quota_range
|
|
CHECK (max_video_channels BETWEEN 1 AND 128),
|
|
CONSTRAINT bell_sites_version_positive CHECK (version >= 1),
|
|
CONSTRAINT bell_sites_identity_not_blank
|
|
CHECK (btrim(tenant_id) <> '' AND btrim(id) <> '' AND btrim(name) <> '')
|
|
);
|
|
ALTER TABLE bell.sites OWNER TO bell_app;
|
|
|
|
CREATE OR REPLACE FUNCTION bell.bump_site_version()
|
|
RETURNS trigger
|
|
LANGUAGE plpgsql
|
|
SECURITY INVOKER
|
|
SET search_path = pg_catalog, bell
|
|
AS $function$
|
|
BEGIN
|
|
IF NEW.tenant_id IS DISTINCT FROM OLD.tenant_id OR NEW.id IS DISTINCT FROM OLD.id THEN
|
|
RAISE EXCEPTION 'Bell site identity is immutable';
|
|
END IF;
|
|
NEW.version := OLD.version + 1;
|
|
NEW.updated_at := clock_timestamp();
|
|
RETURN NEW;
|
|
END
|
|
$function$;
|
|
ALTER FUNCTION bell.bump_site_version() OWNER TO bell_app;
|
|
|
|
DROP TRIGGER IF EXISTS bell_sites_bump_version ON bell.sites;
|
|
CREATE TRIGGER bell_sites_bump_version
|
|
BEFORE UPDATE ON bell.sites
|
|
FOR EACH ROW EXECUTE FUNCTION bell.bump_site_version();
|
|
|
|
CREATE OR REPLACE VIEW bell.site_quota_v1 (
|
|
tenant_id,
|
|
site_id,
|
|
max_video_channels,
|
|
source_version,
|
|
source_updated_at
|
|
) AS
|
|
SELECT
|
|
site.tenant_id,
|
|
site.id,
|
|
site.max_video_channels,
|
|
site.version,
|
|
site.updated_at
|
|
FROM bell.sites AS site
|
|
WHERE site.deleted_at IS NULL;
|
|
ALTER VIEW bell.site_quota_v1 OWNER TO bell_app;
|
|
COMMENT ON VIEW bell.site_quota_v1 IS
|
|
'v1 read-only site video quota projection owned by Bell and consumed by Sense';
|
|
|
|
INSERT INTO bell.schema_migrations(version) VALUES (1)
|
|
ON CONFLICT (version) DO NOTHING;
|