128 lines
4.4 KiB
PL/PgSQL
128 lines
4.4 KiB
PL/PgSQL
-- Bell owns Area and capture-policy truth. Sense records only the highest
|
|
-- projection version it has observed and the version used for admission.
|
|
|
|
CREATE TABLE IF NOT EXISTS bell.areas (
|
|
tenant_id text NOT NULL,
|
|
site_id text NOT NULL,
|
|
id text NOT NULL,
|
|
name text NOT NULL,
|
|
capture_policy text NOT NULL DEFAULT 'video_allowed',
|
|
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_areas_site_fk FOREIGN KEY (tenant_id, site_id)
|
|
REFERENCES bell.sites(tenant_id, id),
|
|
CONSTRAINT bell_areas_capture_policy CHECK (
|
|
capture_policy IN ('video_allowed', 'non_imaging_only')
|
|
),
|
|
CONSTRAINT bell_areas_version_positive CHECK (version >= 1),
|
|
CONSTRAINT bell_areas_identity_not_blank CHECK (
|
|
btrim(tenant_id) <> '' AND btrim(site_id) <> ''
|
|
AND btrim(id) <> '' AND btrim(name) <> ''
|
|
)
|
|
);
|
|
ALTER TABLE bell.areas OWNER TO bell_app;
|
|
|
|
CREATE OR REPLACE FUNCTION bell.bump_area_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.site_id IS DISTINCT FROM OLD.site_id
|
|
OR NEW.id IS DISTINCT FROM OLD.id THEN
|
|
RAISE EXCEPTION 'Bell Area identity and Site are immutable';
|
|
END IF;
|
|
NEW.version := OLD.version + 1;
|
|
NEW.updated_at := clock_timestamp();
|
|
RETURN NEW;
|
|
END
|
|
$function$;
|
|
ALTER FUNCTION bell.bump_area_version() OWNER TO bell_app;
|
|
|
|
DROP TRIGGER IF EXISTS bell_areas_bump_version ON bell.areas;
|
|
CREATE TRIGGER bell_areas_bump_version
|
|
BEFORE UPDATE ON bell.areas
|
|
FOR EACH ROW EXECUTE FUNCTION bell.bump_area_version();
|
|
|
|
CREATE OR REPLACE VIEW bell.area_policy_v1 (
|
|
tenant_id,
|
|
site_id,
|
|
area_id,
|
|
capture_policy,
|
|
source_version,
|
|
source_updated_at
|
|
) AS
|
|
SELECT
|
|
area.tenant_id,
|
|
area.site_id,
|
|
area.id,
|
|
area.capture_policy,
|
|
area.version,
|
|
area.updated_at
|
|
FROM bell.areas AS area
|
|
JOIN bell.sites AS site
|
|
ON site.tenant_id = area.tenant_id AND site.id = area.site_id
|
|
WHERE area.deleted_at IS NULL AND site.deleted_at IS NULL;
|
|
ALTER VIEW bell.area_policy_v1 OWNER TO bell_app;
|
|
COMMENT ON VIEW bell.area_policy_v1 IS
|
|
'v1 read-only Area capture-policy projection owned by Bell and consumed by Sense';
|
|
|
|
ALTER TABLE sense.devices ADD COLUMN IF NOT EXISTS area_id text;
|
|
ALTER TABLE sense.devices ADD COLUMN IF NOT EXISTS area_policy_source_version bigint;
|
|
|
|
DO $constraints$
|
|
BEGIN
|
|
IF NOT EXISTS (
|
|
SELECT 1 FROM pg_constraint
|
|
WHERE conrelid = 'sense.devices'::regclass
|
|
AND conname = 'sense_devices_area_not_blank'
|
|
) THEN
|
|
ALTER TABLE sense.devices ADD CONSTRAINT sense_devices_area_not_blank
|
|
CHECK (area_id IS NULL OR btrim(area_id) <> '');
|
|
END IF;
|
|
IF NOT EXISTS (
|
|
SELECT 1 FROM pg_constraint
|
|
WHERE conrelid = 'sense.devices'::regclass
|
|
AND conname = 'sense_devices_area_version_positive'
|
|
) THEN
|
|
ALTER TABLE sense.devices ADD CONSTRAINT sense_devices_area_version_positive
|
|
CHECK (area_policy_source_version IS NULL OR area_policy_source_version >= 1);
|
|
END IF;
|
|
IF NOT EXISTS (
|
|
SELECT 1 FROM pg_constraint
|
|
WHERE conrelid = 'sense.devices'::regclass
|
|
AND conname = 'sense_devices_tenant_site_id_unique'
|
|
) THEN
|
|
ALTER TABLE sense.devices ADD CONSTRAINT sense_devices_tenant_site_id_unique
|
|
UNIQUE (tenant_id, site_id, id);
|
|
END IF;
|
|
END
|
|
$constraints$;
|
|
|
|
CREATE TABLE IF NOT EXISTS sense.area_policy_projection_state (
|
|
tenant_id text NOT NULL,
|
|
site_id text NOT NULL,
|
|
area_id text NOT NULL,
|
|
source_version bigint NOT NULL,
|
|
synced_at timestamptz NOT NULL,
|
|
PRIMARY KEY (tenant_id, site_id, area_id),
|
|
CONSTRAINT sense_area_projection_identity_not_blank CHECK (
|
|
btrim(tenant_id) <> '' AND btrim(site_id) <> '' AND btrim(area_id) <> ''
|
|
),
|
|
CONSTRAINT sense_area_projection_version_positive CHECK (source_version >= 1)
|
|
);
|
|
ALTER TABLE sense.area_policy_projection_state OWNER TO sense_app;
|
|
|
|
CREATE INDEX IF NOT EXISTS sense_devices_area_idx
|
|
ON sense.devices(tenant_id, site_id, area_id);
|
|
|
|
INSERT INTO bell.schema_migrations(version) VALUES (2)
|
|
ON CONFLICT (version) DO NOTHING;
|
|
INSERT INTO sense.schema_migrations(version) VALUES (2)
|
|
ON CONFLICT (version) DO NOTHING;
|