create_push_server_schema.sql

---
--- DB Sequences
---

CREATE SEQUENCE push_credentials_seq;
CREATE SEQUENCE push_device_registration_seq;
CREATE SEQUENCE push_message_seq;
CREATE SEQUENCE push_campaign_seq;
CREATE SEQUENCE push_campaign_user_seq;

---
--- DB Tables
---

-- Create table for application credentials used for APNS and FCM
CREATE TABLE push_app_credentials (
	id INTEGER NOT NULL CONSTRAINT push_app_credentials_pkey PRIMARY KEY,
	app_id INTEGER NOT NULL,
	ios_key_id VARCHAR(255),
	ios_private_key BYTEA,
	ios_team_id VARCHAR(255),
	ios_bundle VARCHAR(255),
	ios_environment VARCHAR(32),
	android_private_key BYTEA,
	android_project_id VARCHAR(255)
);


-- Create table for registered devices
CREATE TABLE push_device_registration (
	id INTEGER NOT NULL CONSTRAINT push_device_registration_pkey PRIMARY KEY,
	activation_id VARCHAR(37),
	user_id VARCHAR(255),
	app_id INTEGER NOT NULL,
	platform VARCHAR(255) NOT NULL,
	push_token VARCHAR(255) NOT NULL,
	timestamp_last_registered TIMESTAMP(6) NOT NULL,
	is_active BOOLEAN
);


-- Create table for optional auditing of sent push messages
CREATE TABLE push_message (
	id INTEGER NOT NULL CONSTRAINT push_message_pkey PRIMARY KEY,
	device_registration_id INTEGER NOT NULL,
	user_id VARCHAR(255) NOT NULL,
	activation_id VARCHAR(37),
	is_silent BOOLEAN DEFAULT false NOT NULL,
	is_personal BOOLEAN DEFAULT false NOT NULL,
	message_body VARCHAR(2048) NOT NULL,
	timestamp_created TIMESTAMP(6) NOT NULL,
	status INTEGER NOT NULL
);


-- Create table for push message campaigns
CREATE TABLE push_campaign (
	id INTEGER NOT NULL CONSTRAINT push_campaign_pkey PRIMARY KEY,
	app_id INTEGER NOT NULL,
	message VARCHAR(4000) NOT NULL,
	is_sent BOOLEAN DEFAULT false NOT NULL,
	timestamp_created TIMESTAMP(6) NOT NULL,
	timestamp_sent TIMESTAMP(6),
	timestamp_completed TIMESTAMP(6)
);


-- Create table for push campaign user list
CREATE TABLE push_campaign_user (
	id INTEGER NOT NULL CONSTRAINT push_campaign_user_pkey PRIMARY KEY,
	campaign_id INTEGER NOT NULL,
	user_id INTEGER NOT NULL,
	timestamp_created TIMESTAMP(6) NOT NULL
);

--
-- DB Indexes (recommended for better performance)
--

CREATE UNIQUE INDEX push_app_cred_app ON push_app_credentials (app_id);

CREATE INDEX push_device_app_token ON push_device_registration (app_id, push_token);

CREATE INDEX push_device_user_app ON push_device_registration (user_id, app_id);

CREATE UNIQUE INDEX push_device_activation ON push_device_registration (activation_id);

CREATE UNIQUE INDEX push_device_activation_token ON push_device_registration (activation_id, push_token);

CREATE INDEX push_message_status ON push_message (status);

CREATE INDEX push_campaign_sent ON push_campaign (is_sent);

CREATE INDEX push_campaign_user_campaign ON push_campaign_user (campaign_id, user_id);

CREATE INDEX push_campaign_user_detail ON push_campaign_user (user_id);