215 lines
14 KiB
SQL
215 lines
14 KiB
SQL
CREATE TABLE "signage_channel_groups" (
|
|
"id" uuid PRIMARY KEY NOT NULL,
|
|
"name" text NOT NULL,
|
|
"revision" integer DEFAULT 1 NOT NULL,
|
|
"created_at" timestamp with time zone DEFAULT now() NOT NULL,
|
|
"updated_at" timestamp with time zone DEFAULT now() NOT NULL
|
|
);
|
|
--> statement-breakpoint
|
|
CREATE TABLE "signage_channels" (
|
|
"id" uuid PRIMARY KEY NOT NULL,
|
|
"group_id" uuid NOT NULL,
|
|
"key" text NOT NULL,
|
|
"name" text NOT NULL,
|
|
"is_default" boolean DEFAULT false NOT NULL,
|
|
"created_at" timestamp with time zone DEFAULT now() NOT NULL,
|
|
"updated_at" timestamp with time zone DEFAULT now() NOT NULL,
|
|
CONSTRAINT "signage_channels_key_check" CHECK ("signage_channels"."key" ~ '^[a-z][a-z0-9]*(-[a-z0-9]+)*$')
|
|
);
|
|
--> statement-breakpoint
|
|
CREATE TABLE "signage_image_assets" (
|
|
"id" uuid PRIMARY KEY NOT NULL,
|
|
"content_sha256" text NOT NULL,
|
|
"display_name" text NOT NULL,
|
|
"original_filename" text NOT NULL,
|
|
"original_object_key" text NOT NULL,
|
|
"original_content_type" text NOT NULL,
|
|
"original_byte_size" bigint NOT NULL,
|
|
"width" integer NOT NULL,
|
|
"height" integer NOT NULL,
|
|
"encoded_width" integer NOT NULL,
|
|
"encoded_height" integer NOT NULL,
|
|
"orientation" integer,
|
|
"has_alpha" boolean DEFAULT false NOT NULL,
|
|
"status" text DEFAULT 'pending' NOT NULL,
|
|
"failure_code" text,
|
|
"expires_at" timestamp with time zone,
|
|
"created_at" timestamp with time zone DEFAULT now() NOT NULL,
|
|
"updated_at" timestamp with time zone DEFAULT now() NOT NULL,
|
|
CONSTRAINT "signage_image_assets_status_check" CHECK ("signage_image_assets"."status" in ('pending', 'ready', 'failed')),
|
|
CONSTRAINT "signage_image_assets_dimensions_check" CHECK ("signage_image_assets"."width" > 0 and "signage_image_assets"."height" > 0 and "signage_image_assets"."encoded_width" > 0 and "signage_image_assets"."encoded_height" > 0)
|
|
);
|
|
--> statement-breakpoint
|
|
CREATE TABLE "signage_image_renditions" (
|
|
"id" uuid PRIMARY KEY NOT NULL,
|
|
"asset_id" uuid NOT NULL,
|
|
"kind" text NOT NULL,
|
|
"object_key" text NOT NULL,
|
|
"content_type" text NOT NULL,
|
|
"content_sha256" text NOT NULL,
|
|
"byte_size" bigint NOT NULL,
|
|
"width" integer NOT NULL,
|
|
"height" integer NOT NULL,
|
|
"created_at" timestamp with time zone DEFAULT now() NOT NULL,
|
|
CONSTRAINT "signage_image_renditions_kind_check" CHECK ("signage_image_renditions"."kind" in ('display', 'thumbnail'))
|
|
);
|
|
--> statement-breakpoint
|
|
CREATE TABLE "signage_playback_states" (
|
|
"group_id" uuid PRIMARY KEY NOT NULL,
|
|
"active_timeline_id" uuid,
|
|
"pending_timeline_id" uuid,
|
|
"pending_requested_at" timestamp with time zone,
|
|
"current_selection_id" uuid,
|
|
"current_slot_id" uuid,
|
|
"slot_started_at" timestamp with time zone,
|
|
"slot_deadline_at" timestamp with time zone,
|
|
"paused_remaining_ms" integer,
|
|
"paused" boolean DEFAULT false NOT NULL,
|
|
"blackout" boolean DEFAULT false NOT NULL,
|
|
"static_asset_id" uuid,
|
|
"scheduled_timeline_id" uuid,
|
|
"scheduled_activation_at" timestamp with time zone,
|
|
"revision" integer DEFAULT 1 NOT NULL,
|
|
"next_sequence" bigint DEFAULT 1 NOT NULL,
|
|
"updated_at" timestamp with time zone DEFAULT now() NOT NULL,
|
|
CONSTRAINT "signage_playback_states_remaining_check" CHECK ("signage_playback_states"."paused_remaining_ms" is null or "signage_playback_states"."paused_remaining_ms" >= 0),
|
|
CONSTRAINT "signage_playback_states_window_check" CHECK ("signage_playback_states"."slot_started_at" is null or "signage_playback_states"."slot_deadline_at" is null or "signage_playback_states"."slot_started_at" <= "signage_playback_states"."slot_deadline_at")
|
|
);
|
|
--> statement-breakpoint
|
|
CREATE TABLE "signage_provider_sources" (
|
|
"id" uuid PRIMARY KEY NOT NULL,
|
|
"timeline_id" uuid NOT NULL,
|
|
"provider_id" text NOT NULL,
|
|
"source_id" text NOT NULL,
|
|
"label" text NOT NULL,
|
|
"selection_scope" text NOT NULL,
|
|
"created_at" timestamp with time zone DEFAULT now() NOT NULL,
|
|
"updated_at" timestamp with time zone DEFAULT now() NOT NULL
|
|
);
|
|
--> statement-breakpoint
|
|
CREATE TABLE "signage_selection_variants" (
|
|
"id" uuid PRIMARY KEY NOT NULL,
|
|
"selection_id" uuid NOT NULL,
|
|
"audience_key" text NOT NULL,
|
|
"is_default" boolean DEFAULT false NOT NULL,
|
|
"payload" jsonb NOT NULL
|
|
);
|
|
--> statement-breakpoint
|
|
CREATE TABLE "signage_selections" (
|
|
"id" uuid PRIMARY KEY NOT NULL,
|
|
"group_id" uuid NOT NULL,
|
|
"sequence" bigint NOT NULL,
|
|
"timeline_id" uuid,
|
|
"slot_id" uuid,
|
|
"selection_scope" text,
|
|
"provider_id" text NOT NULL,
|
|
"item_id" text NOT NULL,
|
|
"item_revision" text NOT NULL,
|
|
"renderer_contribution_id" text,
|
|
"renderer_contribution_version" text,
|
|
"presentation" jsonb,
|
|
"duration_ms" integer NOT NULL,
|
|
"resolved_at" timestamp with time zone DEFAULT now() NOT NULL,
|
|
"started_at" timestamp with time zone,
|
|
"deadline_at" timestamp with time zone,
|
|
"available_until" timestamp with time zone,
|
|
"withdrawn" boolean DEFAULT false NOT NULL,
|
|
"stale" boolean DEFAULT false NOT NULL,
|
|
"revision" integer DEFAULT 1 NOT NULL,
|
|
CONSTRAINT "signage_selections_duration_check" CHECK ("signage_selections"."duration_ms" > 0)
|
|
);
|
|
--> statement-breakpoint
|
|
CREATE TABLE "signage_timeline_slots" (
|
|
"id" uuid PRIMARY KEY NOT NULL,
|
|
"timeline_id" uuid NOT NULL,
|
|
"position" integer NOT NULL,
|
|
"kind" text NOT NULL,
|
|
"source_id" uuid,
|
|
"provider_id" text,
|
|
"item_id" text,
|
|
"enabled" boolean DEFAULT true NOT NULL,
|
|
"duration_override_ms" integer,
|
|
"eligible_from" timestamp with time zone,
|
|
"eligible_until" timestamp with time zone,
|
|
"background_mode" text,
|
|
"background_color" text,
|
|
"background_darkened" boolean,
|
|
"blur_pixels" integer,
|
|
"foreground_shadow" boolean,
|
|
"created_at" timestamp with time zone DEFAULT now() NOT NULL,
|
|
"updated_at" timestamp with time zone DEFAULT now() NOT NULL,
|
|
CONSTRAINT "signage_timeline_slots_position_check" CHECK ("signage_timeline_slots"."position" >= 0),
|
|
CONSTRAINT "signage_timeline_slots_kind_check" CHECK ("signage_timeline_slots"."kind" in ('provider-source', 'pinned-item')),
|
|
CONSTRAINT "signage_timeline_slots_reference_check" CHECK (("signage_timeline_slots"."kind" = 'provider-source' and "signage_timeline_slots"."source_id" is not null and "signage_timeline_slots"."provider_id" is null and "signage_timeline_slots"."item_id" is null)
|
|
or ("signage_timeline_slots"."kind" = 'pinned-item' and "signage_timeline_slots"."source_id" is null and "signage_timeline_slots"."provider_id" is not null and "signage_timeline_slots"."item_id" is not null)),
|
|
CONSTRAINT "signage_timeline_slots_duration_check" CHECK ("signage_timeline_slots"."duration_override_ms" is null or "signage_timeline_slots"."duration_override_ms" > 0),
|
|
CONSTRAINT "signage_timeline_slots_eligibility_check" CHECK ("signage_timeline_slots"."eligible_from" is null or "signage_timeline_slots"."eligible_until" is null or "signage_timeline_slots"."eligible_from" < "signage_timeline_slots"."eligible_until"),
|
|
CONSTRAINT "signage_timeline_slots_background_check" CHECK ("signage_timeline_slots"."background_mode" is null or "signage_timeline_slots"."background_mode" in ('blurred', 'solid', 'black'))
|
|
);
|
|
--> statement-breakpoint
|
|
CREATE TABLE "signage_timelines" (
|
|
"id" uuid PRIMARY KEY NOT NULL,
|
|
"name" text NOT NULL,
|
|
"description" text,
|
|
"eligible_from" timestamp with time zone,
|
|
"eligible_until" timestamp with time zone,
|
|
"revision" integer DEFAULT 1 NOT NULL,
|
|
"created_at" timestamp with time zone DEFAULT now() NOT NULL,
|
|
"updated_at" timestamp with time zone DEFAULT now() NOT NULL,
|
|
CONSTRAINT "signage_timelines_eligibility_check" CHECK ("signage_timelines"."eligible_from" is null or "signage_timelines"."eligible_until" is null or "signage_timelines"."eligible_from" < "signage_timelines"."eligible_until")
|
|
);
|
|
--> statement-breakpoint
|
|
CREATE TABLE "signage_viewers" (
|
|
"id" uuid PRIMARY KEY NOT NULL,
|
|
"viewer_key" text NOT NULL,
|
|
"name" text NOT NULL,
|
|
"device_id" text,
|
|
"channel_id" uuid,
|
|
"last_seen_at" timestamp with time zone,
|
|
"reported_timeline_id" uuid,
|
|
"reported_selection_id" uuid,
|
|
"reported_provider_id" text,
|
|
"reported_contribution_id" text,
|
|
"reported_playback_revision" integer,
|
|
"offline_ready" boolean DEFAULT false NOT NULL,
|
|
"cached_asset_count" integer DEFAULT 0 NOT NULL,
|
|
"last_error_code" text,
|
|
"last_error_detail" text,
|
|
"last_error_at" timestamp with time zone,
|
|
"created_at" timestamp with time zone DEFAULT now() NOT NULL,
|
|
"updated_at" timestamp with time zone DEFAULT now() NOT NULL
|
|
);
|
|
--> statement-breakpoint
|
|
ALTER TABLE "signage_channels" ADD CONSTRAINT "signage_channels_group_id_signage_channel_groups_id_fk" FOREIGN KEY ("group_id") REFERENCES "public"."signage_channel_groups"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
|
|
ALTER TABLE "signage_image_renditions" ADD CONSTRAINT "signage_image_renditions_asset_id_signage_image_assets_id_fk" FOREIGN KEY ("asset_id") REFERENCES "public"."signage_image_assets"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
|
|
ALTER TABLE "signage_playback_states" ADD CONSTRAINT "signage_playback_states_group_id_signage_channel_groups_id_fk" FOREIGN KEY ("group_id") REFERENCES "public"."signage_channel_groups"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
|
|
ALTER TABLE "signage_playback_states" ADD CONSTRAINT "signage_playback_states_active_timeline_id_signage_timelines_id_fk" FOREIGN KEY ("active_timeline_id") REFERENCES "public"."signage_timelines"("id") ON DELETE set null ON UPDATE no action;--> statement-breakpoint
|
|
ALTER TABLE "signage_playback_states" ADD CONSTRAINT "signage_playback_states_pending_timeline_id_signage_timelines_id_fk" FOREIGN KEY ("pending_timeline_id") REFERENCES "public"."signage_timelines"("id") ON DELETE set null ON UPDATE no action;--> statement-breakpoint
|
|
ALTER TABLE "signage_playback_states" ADD CONSTRAINT "signage_playback_states_static_asset_id_signage_image_assets_id_fk" FOREIGN KEY ("static_asset_id") REFERENCES "public"."signage_image_assets"("id") ON DELETE set null ON UPDATE no action;--> statement-breakpoint
|
|
ALTER TABLE "signage_playback_states" ADD CONSTRAINT "signage_playback_states_scheduled_timeline_id_signage_timelines_id_fk" FOREIGN KEY ("scheduled_timeline_id") REFERENCES "public"."signage_timelines"("id") ON DELETE set null ON UPDATE no action;--> statement-breakpoint
|
|
ALTER TABLE "signage_provider_sources" ADD CONSTRAINT "signage_provider_sources_timeline_id_signage_timelines_id_fk" FOREIGN KEY ("timeline_id") REFERENCES "public"."signage_timelines"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
|
|
ALTER TABLE "signage_selection_variants" ADD CONSTRAINT "signage_selection_variants_selection_id_signage_selections_id_fk" FOREIGN KEY ("selection_id") REFERENCES "public"."signage_selections"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
|
|
ALTER TABLE "signage_selections" ADD CONSTRAINT "signage_selections_group_id_signage_channel_groups_id_fk" FOREIGN KEY ("group_id") REFERENCES "public"."signage_channel_groups"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
|
|
ALTER TABLE "signage_selections" ADD CONSTRAINT "signage_selections_slot_id_signage_timeline_slots_id_fk" FOREIGN KEY ("slot_id") REFERENCES "public"."signage_timeline_slots"("id") ON DELETE set null ON UPDATE no action;--> statement-breakpoint
|
|
ALTER TABLE "signage_timeline_slots" ADD CONSTRAINT "signage_timeline_slots_timeline_id_signage_timelines_id_fk" FOREIGN KEY ("timeline_id") REFERENCES "public"."signage_timelines"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
|
|
ALTER TABLE "signage_timeline_slots" ADD CONSTRAINT "signage_timeline_slots_source_id_signage_provider_sources_id_fk" FOREIGN KEY ("source_id") REFERENCES "public"."signage_provider_sources"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
|
|
ALTER TABLE "signage_viewers" ADD CONSTRAINT "signage_viewers_channel_id_signage_channels_id_fk" FOREIGN KEY ("channel_id") REFERENCES "public"."signage_channels"("id") ON DELETE set null ON UPDATE no action;--> statement-breakpoint
|
|
CREATE INDEX "signage_channel_groups_name_idx" ON "signage_channel_groups" USING btree ("name");--> statement-breakpoint
|
|
CREATE UNIQUE INDEX "signage_channels_group_key_key" ON "signage_channels" USING btree ("group_id","key");--> statement-breakpoint
|
|
CREATE UNIQUE INDEX "signage_channels_group_default_key" ON "signage_channels" USING btree ("group_id") WHERE "signage_channels"."is_default";--> statement-breakpoint
|
|
CREATE UNIQUE INDEX "signage_image_assets_content_sha256_key" ON "signage_image_assets" USING btree ("content_sha256");--> statement-breakpoint
|
|
CREATE INDEX "signage_image_assets_status_idx" ON "signage_image_assets" USING btree ("status");--> statement-breakpoint
|
|
CREATE UNIQUE INDEX "signage_image_renditions_asset_kind_key" ON "signage_image_renditions" USING btree ("asset_id","kind");--> statement-breakpoint
|
|
CREATE UNIQUE INDEX "signage_provider_sources_selection_scope_key" ON "signage_provider_sources" USING btree ("selection_scope");--> statement-breakpoint
|
|
CREATE INDEX "signage_provider_sources_timeline_idx" ON "signage_provider_sources" USING btree ("timeline_id");--> statement-breakpoint
|
|
CREATE INDEX "signage_provider_sources_provider_idx" ON "signage_provider_sources" USING btree ("provider_id");--> statement-breakpoint
|
|
CREATE UNIQUE INDEX "signage_selection_variants_selection_audience_key" ON "signage_selection_variants" USING btree ("selection_id","audience_key");--> statement-breakpoint
|
|
CREATE UNIQUE INDEX "signage_selections_group_sequence_key" ON "signage_selections" USING btree ("group_id","sequence");--> statement-breakpoint
|
|
CREATE INDEX "signage_selections_group_resolved_idx" ON "signage_selections" USING btree ("group_id","resolved_at");--> statement-breakpoint
|
|
CREATE INDEX "signage_selections_provider_item_idx" ON "signage_selections" USING btree ("provider_id","item_id");--> statement-breakpoint
|
|
CREATE UNIQUE INDEX "signage_timeline_slots_position_key" ON "signage_timeline_slots" USING btree ("timeline_id","position");--> statement-breakpoint
|
|
CREATE INDEX "signage_timeline_slots_source_idx" ON "signage_timeline_slots" USING btree ("source_id");--> statement-breakpoint
|
|
CREATE INDEX "signage_timelines_name_idx" ON "signage_timelines" USING btree ("name");--> statement-breakpoint
|
|
CREATE UNIQUE INDEX "signage_viewers_viewer_key_key" ON "signage_viewers" USING btree ("viewer_key");--> statement-breakpoint
|
|
CREATE INDEX "signage_viewers_channel_idx" ON "signage_viewers" USING btree ("channel_id");--> statement-breakpoint
|
|
CREATE INDEX "signage_viewers_last_seen_idx" ON "signage_viewers" USING btree ("last_seen_at"); |