24 lines
1.3 KiB
PL/PgSQL
24 lines
1.3 KiB
PL/PgSQL
|
BEGIN;
|
||
|
|
ALTER TABLE web_event DROP CONSTRAINT web_event_event_type_check;
|
||
|
|
ALTER TABLE web_event ADD CONSTRAINT web_event_event_type_check CHECK (event_type IN (
|
||
|
|
'device.presence.changed','device.binding.reset',
|
||
|
|
'conversation.started','conversation.completed','conversation.failed',
|
||
|
|
'story.created','story.revised','media.upload.completed','export.ready',
|
||
|
|
'privacy_request.updated','device.command.updated','household.member.updated'));
|
||
|
|
-- Retain metadata for the seven-day family activity view. No message content is stored here.
|
||
|
|
ALTER TABLE web_event ALTER COLUMN expires_at SET DEFAULT (now() + interval '7 days');
|
||
|
|
CREATE INDEX web_event_household_recent_idx ON web_event(household_id, occurred_at DESC);
|
||
|
|
CREATE FUNCTION household_member_web_event_trigger() RETURNS trigger LANGUAGE plpgsql AS $$
|
||
|
|
BEGIN
|
||
|
|
IF TG_OP='INSERT' OR OLD.status IS DISTINCT FROM NEW.status OR OLD.role IS DISTINCT FROM NEW.role THEN
|
||
|
|
PERFORM append_web_event(NEW.tenant_id,NEW.household_id,NULL,'household.member.updated','household',
|
||
|
|
(SELECT public_id FROM household WHERE id=NEW.household_id),NEW.status);
|
||
|
|
END IF;
|
||
|
|
RETURN NEW;
|
||
|
|
END;
|
||
|
|
$$;
|
||
|
|
CREATE TRIGGER household_member_web_event AFTER INSERT OR UPDATE ON household_member
|
||
|
|
FOR EACH ROW EXECUTE FUNCTION household_member_web_event_trigger();
|
||
|
|
|
||
|
|
COMMIT;
|