Push Server Database

PowerAuth Push Server requires several database tables in order to work.

You can download DDL scripts for supported databases:

Tables

push_device_registration Push Devices Table

Stores push tokens specific for a given device.

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 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);
Name Type Info Note
id INTEGER primary key, index, autoincrement Unique device registration ID.
activation_id VARCHAR(37) index Activation ID associated with given push token record.
user_id INTEGER index Associated user ID.
app_id INTEGER index Associated application ID.
platform VARCHAR(30) - Mobile OS Platform (“ios”, “android”).
push_token VARCHAR(255) - Push token associated with a given device. Type of the token is determined by the platform column.
timestamp_last_registered TIMESTAMP - Timestamp of the last device registration.
is_active BOOLEAN - PowerAuth activation status (boolean), used as an activation status cache so that communication with PowerAuth Server can be minimal.
Name Primary References Description
push_device_registration_pkey Y id Primary key for table records
Name Unique Columns Description
push_device_app_token N app_id, push_token Index for faster lookup by push token for given app.
push_device_user_app N user_id, app_id Index for faster lookup by user ID for given app.
push_device_activation Y activation_id Index for faster lookup by activation ID.
push_device_activation_token Y activation_id, push_token Index for faster lookup by push token with constraints to activation ID.

push_app_credentials Push Service Credentials Table

Stores per-app credentials used for communication with APNs / FCM.

CREATE TABLE push_app_credentials (
    id INTEGER NOT NULL CONSTRAINT push_app_credentials_pkey PRIMARY KEY,
    app_id VARCHAR(255) 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 UNIQUE INDEX push_app_cred_app ON push_app_credentials (app_id);
Name Type Info Note
id INTEGER primary key, index, autoincrement Unique credential record ID.
app_id VARCHAR(255) index Associated application ID.
ios_key_id VARCHAR(255) - Key ID used for identifying a private key in APNs service.
ios_private_key BYTEA - Binary representation of P8 file with private key used for Apple’s APNs service.
ios_team_id VARCHAR(255) - Team ID used for sending push notifications.
ios_bundle VARCHAR(255) - Application bundle ID, used as a APNs “topic”.
ios_environment VARCHAR(32) - Per-application APNs environment setting. NULL or unknown value inherits from global server configuration, values development or production override the settings.
android_private_key BYTEA - Firebase service account private key used when obtaining access tokens for FCM HTTP v1 API.
android_project_id VARCHAR(255) - Firebase project ID, used when sending push messages using FCM.
Name Primary References Description
push_app_credentials_pkey Y id Primary key for table records
Name Unique Columns Description
push_app_cred_app Y app_id Index for faster lookup by application.

push_message Push Messages Table

Stores individual messages that were sent by the push server and their sent status.

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 TEXT NOT NULL,
    timestamp_created TIMESTAMP(6) NOT NULL,
    status INTEGER NOT NULL
);

CREATE INDEX push_message_status ON push_message (status);
Name Type Info Note
id INTEGER primary key, index, autoincrement Unique message record ID.
device_registration_id INTEGER index Associated device registration (device that is used to receive the message), for the purpose of resend on fail operation.
user_id INTEGER index Associated user ID.
activation_id VARCHAR(37) index PowerAuth activation ID.
is_silent BOOLEAN - Flag indicating if the message was “silent” (0 = NO, 1 = YES).
is_personal BOOLEAN - Flag indicating if the message was “personal” - sent only on active devices (0 = NO, 1 = YES).
message_body TEXT - Payload of the message in a unified server format. This format is later translated in a platform specific payload.
timestamp_created TIMESTAMP - Date and time when the record was created.
status INTEGER - Value indicating message send status. (-1 = FAILED, 0 = PENDING, 1 = SENT).
Name Primary References Description
push_message_pkey Y id Primary key for table records
Name Unique Columns Description
push_message_status N status Index for easier data split by the push sending status.

push_campaign Push Campaigns Table

Stores particular campaigns together with notification messages.

CREATE TABLE push_campaign (
    id INTEGER NOT NULL CONSTRAINT push_campaign_pkey PRIMARY KEY,
    app_id INTEGER NOT NULL,
    message TEXT NOT NULL,
    is_sent BOOLEAN DEFAULT false NOT NULL,
    timestamp_created TIMESTAMP(6) NOT NULL,
    timestamp_sent TIMESTAMP(6),
    timestamp_completed TIMESTAMP(6)
);

CREATE INDEX push_campaign_sent ON push_campaign (is_sent);
Name Type Info Note
id INTEGER primary key, index, autoincrement Unique campaign record ID.
app_id INTEGER index Associated Application identifier.
message TEXT - Certain notification that is written in unified format.
sent BOOLEAN - Flag indicating if campaign was successfully sent.
timestamp_created TIMESTAMP - Timestamp of campaign creation.
timestamp_sent TIMESTAMP - Timestamp of campaign sending initiation.
timestamp_completed TIMESTAMP - Timestamp of campaign successful sending (all messages sent).
Name Primary References Description
push_campaign_pkey Y id Primary key for table records
Name Unique Columns Description
push_campaign_sent N is_sent Index for easier data split by the push campaign sending status.

push_campaign_user Push Campaign Users Table

Stores users who are going to get notification from specific campaign.

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
);

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);
Name Type Info Note
id INTEGER primary key, index, autoincrement Unique user ID.
campaign_id INTEGER index Identifier of campaign that is user related to.
user_id INTEGER index Identifier of user, can occur multiple times in different campaigns.
timestamp_created TIMESTAMP - Timestamp of user creation (assignment to the campaign).
Name Primary References Description
push_campaign_user_pkey Y id Primary key for table records
Name Unique Columns Description
push_campaign_user_campaign N campaign_id, user_id Index for easier campaign lookup for user by campaign ID.
push_campaign_user_detail N user_id Index for easier lookup by user ID.

push_inbox Message Inbox

Stores the messages to be delivered to particular users.

CREATE TABLE push_inbox (
    id INTEGER NOT NULL CONSTRAINT push_inbox_pk PRIMARY KEY,
    inbox_id VARCHAR(37) NOT NULL,
    user_id VARCHAR(255) NOT NULL,
    type VARCHAR(32) NOT NULL;
    subject TEXT NOT NULL,
    summary TEXT NOT NULL;
    body TEXT NOT NULL,
    read BOOLEAN DEFAULT false NOT NULL,
    timestamp_created TIMESTAMP NOT NULL,
    timestamp_read TIMESTAMP
);

CREATE INDEX push_inbox_id ON push_inbox (inbox_id);
CREATE INDEX push_inbox_user ON push_inbox (user_id);
CREATE INDEX push_inbox_user_read ON push_inbox (user_id, read);
Name Type Info Note
id INTEGER primary key, index, autoincrement Unique message ID.
inbox_id INTEGER index Identifier of message that is publicly visible.
user_id VARCHAR(255) index Identifier of user.
type VARCHAR(32) - Message type (text or html).
subject TEXT - Message subject.
summary TEXT - Message summary.
body TEXT - Message body.
read BOOLEAN index Indication of in the message was read.
timestamp_created TIMESTAMP - Timestamp of message creation.
timestamp_read TIMESTAMP - Timestamp of when the message was read.
Name Primary References Description
push_inbox_pk Y id Primary key for table records
Name Unique Columns Description
push_inbox_id N inbox_id Index for easier lookup for message by ID.
push_inbox_user N user_id Index for easier lookup for message by user ID.
push_inbox_user_read N user_id, read Index for easier lookup for message by user ID and read status.

push_inbox Message Inbox Mapping Table

Stores the messages to application mapping.

CREATE TABLE push_inbox_app (
    app_credentials_id INTEGER NOT NULL,
    inbox_id           INTEGER NOT NULL,
    CONSTRAINT push_inbox_app_pk PRIMARY KEY (inbox_id, app_credentials_id)
);
Name Type Info Note
app_credentials_id INTEGER PK Application credentials ID.
inbox_id INTEGER PK Unique message ID.
Name Primary References Description
push_inbox_app_pk Y inbox_id, app_credentials_id Primary key for table records
Name Unique Columns Description
push_inbox_id N inbox_id Index for easier lookup for message by ID.
push_inbox_user N user_id Index for easier lookup for message by user ID.
push_inbox_user_read N user_id, read Index for easier lookup for message by user ID and read status.

Sequences

push_credentials_seq Push Credentials Sequence

Sequence for application credentials registered in the system.

CREATE SEQUENCE push_credentials_seq;

push_device_registration_seq Push Device Registration Sequence

Sequence for device registrations in the system.

CREATE SEQUENCE push_device_registration_seq;

push_message_seq Push Message Sequence

Sequence for push messages sent by the system.

CREATE SEQUENCE push_message_seq;

push_campaign_seq Push Campaign Sequence

Sequence for push campaigns that are created in the system.

CREATE SEQUENCE push_campaign_seq;

push_campaign_user_seq Push Campaign User Sequence

Sequence for user assignments to campaigns.

CREATE SEQUENCE push_campaign_user_seq;
Last updated on Sep 14, 2023 (09:39) Edit on Github Send Feedback
Search

1.6.x

PowerAuth Push Server