-- -- PostgreSQL database dump -- \restrict 72KM05IzNyW7A1TPKp4X6PDt23eMqgwTMDUiZru0Prf9RQcWtauAkd8x8zdv1UB -- Dumped from database version 16.15 (Ubuntu 16.15-1.pgdg22.04+2) -- Dumped by pg_dump version 16.15 (Ubuntu 16.15-1.pgdg22.04+2) SET statement_timeout = 0; SET lock_timeout = 0; SET idle_in_transaction_session_timeout = 0; SET client_encoding = 'UTF8'; SET standard_conforming_strings = on; SELECT pg_catalog.set_config('search_path', '', false); SET check_function_bodies = false; SET xmloption = content; SET client_min_messages = warning; SET row_security = off; -- -- Name: public; Type: SCHEMA; Schema: -; Owner: postgres -- -- *not* creating schema, since initdb creates it ALTER SCHEMA public OWNER TO postgres; -- -- Name: SCHEMA public; Type: COMMENT; Schema: -; Owner: postgres -- COMMENT ON SCHEMA public IS ''; SET default_tablespace = ''; SET default_table_access_method = heap; -- -- Name: ActivationCodes; Type: TABLE; Schema: public; Owner: classnotify -- CREATE TABLE public."ActivationCodes" ( "Code" character varying(50) NOT NULL, "TenantId" text NOT NULL, "ClassroomId" character varying(50) NOT NULL, "ExpiresAt" timestamp with time zone NOT NULL, "IsUsed" boolean NOT NULL, "UsedAt" timestamp with time zone, "UsedByDeviceId" text, "InstallationId" character varying(100) ); ALTER TABLE public."ActivationCodes" OWNER TO classnotify; -- -- Name: Classrooms; Type: TABLE; Schema: public; Owner: classnotify -- CREATE TABLE public."Classrooms" ( "Id" text NOT NULL, "TenantId" text NOT NULL, "DisplayName" character varying(100) NOT NULL, "Grade" character varying(50), "RoomNumber" character varying(50), "CreatedAt" timestamp with time zone NOT NULL ); ALTER TABLE public."Classrooms" OWNER TO classnotify; -- -- Name: DeviceCredentials; Type: TABLE; Schema: public; Owner: classnotify -- CREATE TABLE public."DeviceCredentials" ( "DeviceId" text NOT NULL, "InstallationId" character varying(100) NOT NULL, "DeviceTokenHash" text NOT NULL, "EncryptedRecoverableToken" text NOT NULL, "CreatedAt" timestamp with time zone NOT NULL, "RotatedAt" timestamp with time zone ); ALTER TABLE public."DeviceCredentials" OWNER TO classnotify; -- -- Name: Devices; Type: TABLE; Schema: public; Owner: classnotify -- CREATE TABLE public."Devices" ( "Id" text NOT NULL, "TenantId" text NOT NULL, "ClassroomId" text NOT NULL, "DeviceTokenHash" text NOT NULL, "RawDeviceToken" text, "InstallationId" character varying(100), "Hostname" character varying(200), "IsActive" boolean NOT NULL, "CreatedAt" timestamp with time zone NOT NULL, "LastHeartbeatAt" timestamp with time zone ); ALTER TABLE public."Devices" OWNER TO classnotify; -- -- Name: DeviceCommands; Type: TABLE; Schema: public; Owner: classnotify -- CREATE TABLE public."DeviceCommands" ( "Id" character varying(100) NOT NULL, "TenantId" character varying(100) NOT NULL, "DeviceId" character varying(100) NOT NULL, "CommandType" character varying(50) NOT NULL, "Parameters" text, "CreatedBy" character varying(100) NOT NULL, "CreatedAt" timestamp with time zone NOT NULL, "ExpiresAt" timestamp with time zone NOT NULL, "StartedAt" timestamp with time zone, "CompletedAt" timestamp with time zone, "Status" character varying(50) NOT NULL, "ResultCode" integer, "ResultMessage" character varying(1000) ); ALTER TABLE public."DeviceCommands" OWNER TO classnotify; -- -- Name: MigrationMarkers; Type: TABLE; Schema: public; Owner: classnotify -- CREATE TABLE public."MigrationMarkers" ( "Id" character varying(100) NOT NULL, "MigratedAt" timestamp with time zone NOT NULL, "Details" text ); ALTER TABLE public."MigrationMarkers" OWNER TO classnotify; -- -- Name: NotificationReceipts; Type: TABLE; Schema: public; Owner: classnotify -- CREATE TABLE public."NotificationReceipts" ( "Id" character varying(100) NOT NULL, "NotificationId" character varying(100) NOT NULL, "DeviceId" character varying(100) NOT NULL, "Status" character varying(50) NOT NULL, "DeviceReceivedAt" timestamp with time zone, "DisplayedAt" timestamp with time zone, "AcknowledgedAt" timestamp with time zone, "FailedAt" timestamp with time zone, "FailureReason" character varying(500), "UpdatedAt" timestamp with time zone NOT NULL ); ALTER TABLE public."NotificationReceipts" OWNER TO classnotify; -- -- Name: Notifications; Type: TABLE; Schema: public; Owner: classnotify -- CREATE TABLE public."Notifications" ( "Id" character varying(100) NOT NULL, "TenantId" text NOT NULL, "ClassroomId" character varying(50) NOT NULL, "SenderUserId" text, "SenderName" character varying(100) NOT NULL, "Message" text NOT NULL, "Title" character varying(200) NOT NULL, "Priority" character varying(50) NOT NULL, "CreatedAt" timestamp with time zone NOT NULL, "ExpiresAt" timestamp with time zone, "Voice" boolean DEFAULT true NOT NULL, "Shake" boolean DEFAULT true NOT NULL, "DurationSeconds" integer DEFAULT 15 NOT NULL ); ALTER TABLE public."Notifications" OWNER TO classnotify; -- -- Name: Tenants; Type: TABLE; Schema: public; Owner: classnotify -- CREATE TABLE public."Tenants" ( "Id" text NOT NULL, "Name" character varying(200) NOT NULL, "IsActive" boolean NOT NULL, "CreatedAt" timestamp with time zone NOT NULL ); ALTER TABLE public."Tenants" OWNER TO classnotify; -- -- Name: Users; Type: TABLE; Schema: public; Owner: classnotify -- CREATE TABLE public."Users" ( "Id" text NOT NULL, "TenantId" text NOT NULL, "Username" character varying(100) NOT NULL, "PasswordHash" text NOT NULL, "FullName" character varying(100) NOT NULL, "Role" character varying(50) NOT NULL, "CreatedAt" timestamp with time zone NOT NULL ); ALTER TABLE public."Users" OWNER TO classnotify; -- -- Name: __EFMigrationsHistory; Type: TABLE; Schema: public; Owner: classnotify -- CREATE TABLE public."__EFMigrationsHistory" ( "MigrationId" character varying(150) NOT NULL, "ProductVersion" character varying(32) NOT NULL ); ALTER TABLE public."__EFMigrationsHistory" OWNER TO classnotify; -- -- Name: ActivationCodes PK_ActivationCodes; Type: CONSTRAINT; Schema: public; Owner: classnotify -- ALTER TABLE ONLY public."ActivationCodes" ADD CONSTRAINT "PK_ActivationCodes" PRIMARY KEY ("Code"); -- -- Name: Classrooms PK_Classrooms; Type: CONSTRAINT; Schema: public; Owner: classnotify -- ALTER TABLE ONLY public."Classrooms" ADD CONSTRAINT "PK_Classrooms" PRIMARY KEY ("TenantId", "Id"); -- -- Name: DeviceCredentials PK_DeviceCredentials; Type: CONSTRAINT; Schema: public; Owner: classnotify -- ALTER TABLE ONLY public."DeviceCredentials" ADD CONSTRAINT "PK_DeviceCredentials" PRIMARY KEY ("DeviceId"); -- -- Name: Devices PK_Devices; Type: CONSTRAINT; Schema: public; Owner: classnotify -- ALTER TABLE ONLY public."Devices" ADD CONSTRAINT "PK_Devices" PRIMARY KEY ("Id"); -- -- Name: DeviceCommands PK_DeviceCommands; Type: CONSTRAINT; Schema: public; Owner: classnotify -- ALTER TABLE ONLY public."DeviceCommands" ADD CONSTRAINT "PK_DeviceCommands" PRIMARY KEY ("Id"); -- -- Name: MigrationMarkers PK_MigrationMarkers; Type: CONSTRAINT; Schema: public; Owner: classnotify -- ALTER TABLE ONLY public."MigrationMarkers" ADD CONSTRAINT "PK_MigrationMarkers" PRIMARY KEY ("Id"); -- -- Name: NotificationReceipts PK_NotificationReceipts; Type: CONSTRAINT; Schema: public; Owner: classnotify -- ALTER TABLE ONLY public."NotificationReceipts" ADD CONSTRAINT "PK_NotificationReceipts" PRIMARY KEY ("Id"); -- -- Name: Notifications PK_Notifications; Type: CONSTRAINT; Schema: public; Owner: classnotify -- ALTER TABLE ONLY public."Notifications" ADD CONSTRAINT "PK_Notifications" PRIMARY KEY ("Id"); -- -- Name: Tenants PK_Tenants; Type: CONSTRAINT; Schema: public; Owner: classnotify -- ALTER TABLE ONLY public."Tenants" ADD CONSTRAINT "PK_Tenants" PRIMARY KEY ("Id"); -- -- Name: Users PK_Users; Type: CONSTRAINT; Schema: public; Owner: classnotify -- ALTER TABLE ONLY public."Users" ADD CONSTRAINT "PK_Users" PRIMARY KEY ("Id"); -- -- Name: __EFMigrationsHistory PK___EFMigrationsHistory; Type: CONSTRAINT; Schema: public; Owner: classnotify -- ALTER TABLE ONLY public."__EFMigrationsHistory" ADD CONSTRAINT "PK___EFMigrationsHistory" PRIMARY KEY ("MigrationId"); -- -- Name: IX_ActivationCodes_ClassroomId; Type: INDEX; Schema: public; Owner: classnotify -- CREATE INDEX "IX_ActivationCodes_ClassroomId" ON public."ActivationCodes" USING btree ("ClassroomId"); -- -- Name: IX_ActivationCodes_Code; Type: INDEX; Schema: public; Owner: classnotify -- CREATE UNIQUE INDEX "IX_ActivationCodes_Code" ON public."ActivationCodes" USING btree ("Code"); -- -- Name: IX_ActivationCodes_InstallationId; Type: INDEX; Schema: public; Owner: classnotify -- CREATE INDEX "IX_ActivationCodes_InstallationId" ON public."ActivationCodes" USING btree ("InstallationId"); -- -- Name: IX_ActivationCodes_TenantId; Type: INDEX; Schema: public; Owner: classnotify -- CREATE INDEX "IX_ActivationCodes_TenantId" ON public."ActivationCodes" USING btree ("TenantId"); -- -- Name: IX_Classrooms_TenantId_Id; Type: INDEX; Schema: public; Owner: classnotify -- CREATE INDEX "IX_Classrooms_TenantId_Id" ON public."Classrooms" USING btree ("TenantId", "Id"); -- -- Name: IX_DeviceCredentials_DeviceId; Type: INDEX; Schema: public; Owner: classnotify -- CREATE UNIQUE INDEX "IX_DeviceCredentials_DeviceId" ON public."DeviceCredentials" USING btree ("DeviceId"); -- -- Name: IX_DeviceCredentials_InstallationId; Type: INDEX; Schema: public; Owner: classnotify -- CREATE UNIQUE INDEX "IX_DeviceCredentials_InstallationId" ON public."DeviceCredentials" USING btree ("InstallationId"); -- -- Name: IX_Devices_Id; Type: INDEX; Schema: public; Owner: classnotify -- CREATE UNIQUE INDEX "IX_Devices_Id" ON public."Devices" USING btree ("Id"); -- -- Name: IX_Devices_InstallationId; Type: INDEX; Schema: public; Owner: classnotify -- CREATE UNIQUE INDEX "IX_Devices_InstallationId" ON public."Devices" USING btree ("InstallationId") WHERE ("InstallationId" IS NOT NULL); -- -- Name: IX_Devices_TenantId; Type: INDEX; Schema: public; Owner: classnotify -- CREATE INDEX "IX_Devices_TenantId" ON public."Devices" USING btree ("TenantId"); -- -- Name: IX_Devices_TenantId_ClassroomId; Type: INDEX; Schema: public; Owner: classnotify -- CREATE INDEX "IX_Devices_TenantId_ClassroomId" ON public."Devices" USING btree ("TenantId", "ClassroomId"); -- -- Name: IX_DeviceCommands_DeviceId_Status; Type: INDEX; Schema: public; Owner: classnotify -- CREATE INDEX "IX_DeviceCommands_DeviceId_Status" ON public."DeviceCommands" USING btree ("DeviceId", "Status"); -- -- Name: IX_DeviceCommands_Status_ExpiresAt; Type: INDEX; Schema: public; Owner: classnotify -- CREATE INDEX "IX_DeviceCommands_Status_ExpiresAt" ON public."DeviceCommands" USING btree ("Status", "ExpiresAt"); -- -- Name: IX_DeviceCommands_TenantId_DeviceId_CreatedAt; Type: INDEX; Schema: public; Owner: classnotify -- CREATE INDEX "IX_DeviceCommands_TenantId_DeviceId_CreatedAt" ON public."DeviceCommands" USING btree ("TenantId", "DeviceId", "CreatedAt" DESC); -- -- Name: IX_NotificationReceipts_DeviceId_UpdatedAt; Type: INDEX; Schema: public; Owner: classnotify -- CREATE INDEX "IX_NotificationReceipts_DeviceId_UpdatedAt" ON public."NotificationReceipts" USING btree ("DeviceId", "UpdatedAt" DESC); -- -- Name: IX_NotificationReceipts_NotificationId_DeviceId; Type: INDEX; Schema: public; Owner: classnotify -- CREATE UNIQUE INDEX "IX_NotificationReceipts_NotificationId_DeviceId" ON public."NotificationReceipts" USING btree ("NotificationId", "DeviceId"); -- -- Name: IX_NotificationReceipts_DeviceId_Status; Type: INDEX; Schema: public; Owner: classnotify -- CREATE INDEX "IX_NotificationReceipts_DeviceId_Status" ON public."NotificationReceipts" USING btree ("DeviceId", "Status"); -- -- Name: IX_Notifications_TenantId_ClassroomId_ExpiresAt; Type: INDEX; Schema: public; Owner: classnotify -- CREATE INDEX "IX_Notifications_TenantId_ClassroomId_ExpiresAt" ON public."Notifications" USING btree ("TenantId", "ClassroomId", "ExpiresAt"); -- -- Name: IX_Notifications_ClassroomId_CreatedAt; Type: INDEX; Schema: public; Owner: classnotify -- CREATE INDEX "IX_Notifications_ClassroomId_CreatedAt" ON public."Notifications" USING btree ("ClassroomId", "CreatedAt" DESC); -- -- Name: IX_Notifications_TenantId_CreatedAt; Type: INDEX; Schema: public; Owner: classnotify -- CREATE INDEX "IX_Notifications_TenantId_CreatedAt" ON public."Notifications" USING btree ("TenantId", "CreatedAt" DESC); -- -- Name: IX_Users_TenantId; Type: INDEX; Schema: public; Owner: classnotify -- CREATE INDEX "IX_Users_TenantId" ON public."Users" USING btree ("TenantId"); -- -- Name: IX_Users_Username; Type: INDEX; Schema: public; Owner: classnotify -- CREATE UNIQUE INDEX "IX_Users_Username" ON public."Users" USING btree ("Username"); -- -- Name: ActivationCodes FK_ActivationCodes_Tenants_TenantId; Type: FK CONSTRAINT; Schema: public; Owner: classnotify -- ALTER TABLE ONLY public."ActivationCodes" ADD CONSTRAINT "FK_ActivationCodes_Tenants_TenantId" FOREIGN KEY ("TenantId") REFERENCES public."Tenants"("Id") ON DELETE RESTRICT; -- -- Name: Classrooms FK_Classrooms_Tenants_TenantId; Type: FK CONSTRAINT; Schema: public; Owner: classnotify -- ALTER TABLE ONLY public."Classrooms" ADD CONSTRAINT "FK_Classrooms_Tenants_TenantId" FOREIGN KEY ("TenantId") REFERENCES public."Tenants"("Id") ON DELETE CASCADE; -- -- Name: DeviceCommands FK_DeviceCommands_Devices_DeviceId; Type: FK CONSTRAINT; Schema: public; Owner: classnotify -- ALTER TABLE ONLY public."DeviceCommands" ADD CONSTRAINT "FK_DeviceCommands_Devices_DeviceId" FOREIGN KEY ("DeviceId") REFERENCES public."Devices"("Id") ON DELETE CASCADE; -- -- Name: DeviceCommands FK_DeviceCommands_Tenants_TenantId; Type: FK CONSTRAINT; Schema: public; Owner: classnotify -- ALTER TABLE ONLY public."DeviceCommands" ADD CONSTRAINT "FK_DeviceCommands_Tenants_TenantId" FOREIGN KEY ("TenantId") REFERENCES public."Tenants"("Id") ON DELETE CASCADE; -- -- Name: Devices FK_Devices_Classrooms_TenantId_ClassroomId; Type: FK CONSTRAINT; Schema: public; Owner: classnotify -- ALTER TABLE ONLY public."Devices" ADD CONSTRAINT "FK_Devices_Classrooms_TenantId_ClassroomId" FOREIGN KEY ("TenantId", "ClassroomId") REFERENCES public."Classrooms"("TenantId", "Id") ON DELETE RESTRICT; -- -- Name: Devices FK_Devices_Tenants_TenantId; Type: FK CONSTRAINT; Schema: public; Owner: classnotify -- ALTER TABLE ONLY public."Devices" ADD CONSTRAINT "FK_Devices_Tenants_TenantId" FOREIGN KEY ("TenantId") REFERENCES public."Tenants"("Id") ON DELETE RESTRICT; -- -- Name: NotificationReceipts FK_NotificationReceipts_Devices_DeviceId; Type: FK CONSTRAINT; Schema: public; Owner: classnotify -- ALTER TABLE ONLY public."NotificationReceipts" ADD CONSTRAINT "FK_NotificationReceipts_Devices_DeviceId" FOREIGN KEY ("DeviceId") REFERENCES public."Devices"("Id") ON DELETE CASCADE; -- -- Name: NotificationReceipts FK_NotificationReceipts_Notifications_NotificationId; Type: FK CONSTRAINT; Schema: public; Owner: classnotify -- ALTER TABLE ONLY public."NotificationReceipts" ADD CONSTRAINT "FK_NotificationReceipts_Notifications_NotificationId" FOREIGN KEY ("NotificationId") REFERENCES public."Notifications"("Id") ON DELETE CASCADE; -- -- Name: Notifications FK_Notifications_Tenants_TenantId; Type: FK CONSTRAINT; Schema: public; Owner: classnotify -- ALTER TABLE ONLY public."Notifications" ADD CONSTRAINT "FK_Notifications_Tenants_TenantId" FOREIGN KEY ("TenantId") REFERENCES public."Tenants"("Id") ON DELETE CASCADE; -- -- Name: Users FK_Users_Tenants_TenantId; Type: FK CONSTRAINT; Schema: public; Owner: classnotify -- ALTER TABLE ONLY public."Users" ADD CONSTRAINT "FK_Users_Tenants_TenantId" FOREIGN KEY ("TenantId") REFERENCES public."Tenants"("Id") ON DELETE RESTRICT; -- -- Name: SCHEMA public; Type: ACL; Schema: -; Owner: postgres -- REVOKE USAGE ON SCHEMA public FROM PUBLIC; GRANT ALL ON SCHEMA public TO classnotify; -- -- PostgreSQL database dump complete -- \unrestrict 72KM05IzNyW7A1TPKp4X6PDt23eMqgwTMDUiZru0Prf9RQcWtauAkd8x8zdv1UB