In-app campaigns 'priority' to 'rank' migration

Current `priority` system

In-app campaigns currently use a priority column (integer) to determine the order in which campaigns are evaluated and shown for each customer.

Key properties of the current system:

The priority value is unique within each customer_id group.
Lower priority numbers mean higher precedence (i.e., priority = 1 is the highest).
Priorities are always contiguous, starting from 1 up to N (the total number of campaigns for that customer). There are no gaps.
The UI always displays campaigns ordered by ascending priority.
Customers can reorder campaigns in the UI by moving them up or down in the list.

How priorities change:

When a new campaign is created, it is assigned the highest priority (priority = 1). All existing campaigns for the same customer have their priorities incremented by 1.
When a campaign is deleted, all campaigns with a lower priority (higher number) for the same customer have their priorities decremented by 1 to fill the gap.
Priorities can also be changed in bulk (e.g., when reordering campaigns in the UI) via a dedicated API endpoint that allows setting the priority of multiple campaigns at once. UI sends the request only with the campaigns whose priority has changed. This is done to reduce the number of updates to the database and BigQuery.

Drawbacks:

Any change (creation, deletion, or reordering) requires updating the priorities of multiple campaigns to maintain contiguity and uniqueness. This can be costly, as even a simple campaign creation updates all existing campaigns for that customer in the database.
The more campaigns a customer has, the more expensive each change becomes.

Summary of invariants and constraints:

Database constraint on (customer_id, application_id, priority) prevents conflicts within each app as a safety net
Business logic enforces broader uniqueness: (customer_id, priority) pairs are unique globally across all applications
This means priorities 1, 2, 3… are assigned sequentially across ALL campaigns for a customer, regardless of which application they belong to
Priorities are always contiguous (no gaps), starting from 1 for each customer_id group.
No two campaigns for the same customer can have the same priority.
All changes to priorities must be atomic and consistent to avoid gaps or duplicates (performed in DB transaction).

Known issues and migration pain points:

Any change to priorities (create, delete, reorder) can require updating many rows, leading to performance bottlenecks.
The current system is not scalable for customers with many campaigns.
Migrating to a system that allows non-contiguous, floating-point ranks will require careful handling of these invariants and may need data cleanup or transformation.
Concurrent modifications (e.g., two users reordering at once) rely on DB constraints and transaction isolation to prevent corruption.

Web API endpoints which affect `priority`

Create Campaign

Endpoint: POST /customers/:customerId/campaigns
Effect: When a new campaign is created, it is assigned the highest priority (priority = 1). All existing campaigns for the same customer have their priorities incremented by 1.

Delete Campaign

Endpoint: DELETE /customers/:customerId/campaigns/:campaignId
Effect: When a campaign is deleted, all campaigns with a lower priority (higher number) for the same customer have their priorities decremented by 1 to fill the gap.

Bulk Update (Reordering) Priorities

Endpoint: PUT /customers/:customerId/campaign-priorities
Effect: Allows setting the priorities of multiple campaigns at once. The request body must contain an array of objects with id and priority fields. This endpoint is used when campaigns are reordered in the UI. UI sends the request only with the campaigns whose priority has changed. Instead of updating all 20 campaigns, backend updates only those campaigns from the request in the database and BigQuery.
Example request: Here we see 2 contiguous batches of campaigns being updated. In this case only 7 campaigns are updated, not 20. The first batch is C, A, B, the second batch is D, E, F, G. The batch at minimum always has at least 2 campaigns, since even if you move only one campaign, you will affect at least one more campaign.

{
  "campaignCount": 20,
  "lastUpdatedAt": "2025-01-01T00:00:00Z",
  "updates": [
    { "id": "C", "priority": 5 },
    { "id": "A", "priority": 6 },
    { "id": "B", "priority": 7 },
    { "id": "D", "priority": 11 },
    { "id": "E", "priority": 12 },
    { "id": "F", "priority": 13 },
    { "id": "G", "priority": 14 }
  ]
}

Examples of how `priority` changes on each action

1. Create Campaign

Suppose you have no campaigns. You create three campaigns in sequence:

Create Campaign C → Priority: 1
Create Campaign B → Priority: 1 (C becomes 2)
Create Campaign A → Priority: 1 (B becomes 2, C becomes 3)

Resulting priorities:

A: 1 (highest)
B: 2
C: 3

2. Delete Campaign

Suppose you have three campaigns with priorities:

A: 1
B: 2
C: 3

If you delete campaign B (priority 2):

A: 1 (unchanged)
C: 2 (was 3, now decremented to fill the gap)

Resulting priorities:

A: 1
C: 2

3. Bulk Update (Reordering) Priorities

Suppose you have three campaigns:

A: 1
B: 2
C: 3

You want to move C to the top, so you send a bulk update:

[
  { "id": "C", "priority": 1 },
  { "id": "A", "priority": 2 },
  { "id": "B", "priority": 3 }
]

Resulting priorities:

C: 1 (now highest)
A: 2
B: 3

4. Priorities independent per customer, not per application

Suppose a customer has two applications, App1 and App2, and the following initial campaigns:

App1:

Campaign X: priority 1
Campaign Y: priority 2

App2:

Campaign Z: priority 3

Now, add a new campaign to App1:

Create Campaign W in App1 → Priority: 1 (X becomes 2, Y becomes 3, Z becomes 4)

Resulting priorities:

App1:

W: 1 (new)
X: 2
Y: 3

App2:

Z: 4

Now, add a new campaign to App2:

Create Campaign Q in App2 → Priority: 1 (W becomes 2, X becomes 3, Y becomes 4, Z becomes 5)

Resulting priorities:

App1:

W: 2
X: 3
Y: 4

App2:

Q: 1 (new)
Z: 5

Now, delete Campaign X from App1:

Delete Campaign X (priority 3) in App1

Resulting priorities:

App1:

W: 1 (was 2, now decremented)
Y: 3 (was 4, now decremented)

App2:

Q: 1
Z: 4 (was 5, now decremented)

This shows that campaign priorities are global per customer, affecting all campaigns regardless of their application.

References in the codebase

1. Create Campaign

Web API Layer (Controller):

server/processes/web/routes/campaigns/post-create-campaign.ts — createCampaignHandler handles the POST request to create a campaign.
server/processes/web/routes/campaigns/index.ts — Registers the route: POST /customers/:customerId/campaigns → createCampaignHandler

Service Layer:

server/services/campaign-service.ts — CampaignService.createCampaign orchestrates campaign creation and calls the DB layer.

DB/Repository Layer:

server/models/campaigns.ts — Campaigns.create inserts the campaign and updates priorities of all existing campaigns for the customer.

2. Delete Campaign

Web API Layer (Controller):

server/processes/web/routes/campaigns/id/delete-campaign.ts — deleteCampaignHandler handles the DELETE request for a campaign.
server/processes/web/routes/campaigns/index.ts — Registers the route: DELETE /customers/:customerId/campaigns/:campaignId → deleteCampaignHandler

Service Layer:

server/services/campaign-service.ts — CampaignService.deleteCampaign orchestrates campaign deletion and calls the DB layer.

DB/Repository Layer:

server/models/campaigns.ts — Campaigns.delete deletes the campaign and decrements priorities of remaining campaigns to fill the gap.

3. Bulk Update (Reordering) Priorities

Web API Layer (Controller):

server/processes/web/routes/campaign-priorities/put-set-priorities.ts — setCampaignPrioritiesHandler handles the PUT request for bulk updating priorities.
server/processes/web/routes/campaign-priorities/index.ts — Registers the route: PUT /customers/:customerId/campaign-priorities → setCampaignPrioritiesHandler

Service Layer:

server/services/campaign-service.ts — CampaignService.updatePriorities orchestrates the bulk update and calls the DB layer.

DB/Repository Layer:

server/models/campaigns.ts — Campaigns.updatePriorities updates the priorities of the specified campaigns in the database.

New `rank` system

To address the limitations of the current priority system, we are introducing a new system based on a rank column (double-precision floating-point number).

Key properties and goals:

The rank column replaces the integer priority column.
Higher rank values mean higher precedence (i.e., rank = 10.0 is higher than rank = 9.9).
Ranks do not need to be contiguous integers; they can be any floating-point value, but they must be unique within each customer.
There will be a uniqueness constraint on the database level to ensure each customer’s campaigns have unique ranks.
The main goal is to allow efficient reordering and insertion of campaigns without having to update many rows.
In rare cases, rebalancing of ranks might be needed if many campaigns have very close ranks due to repeated reordering. The unique constraint prevents identical ranks, ensuring no collisions occur.
The UI should display user-friendly, whole-number ranks (1..N) to users, rather than the internal floating-point values used for ordering.

How the `rank` system works

Insertion:
- When a new campaign is created, it is assigned a rank value higher than any existing campaign for that customer, specifically the current highest rank incremented by 1 (e.g., if the highest rank is 8.5, the new campaign gets 9.5).
Reordering:
- To move a campaign between two others, assign it a rank value between the ranks of its neighbors (e.g., between 10.0 and 9.0, assign 9.5).
- This allows reordering without updating any other campaigns' ranks.
- Ranks of neighbouring campaigns need to be read.
- In case of batch reordering in the UI, instead of updating all campaigns, we can see which campaigns were moved where and update only ranks of those campaigns, by setting the rank value in between the neighbouring campaigns.
- Moving a campaign to the top is done by setting its rank to the current highest rank incremented by 1 (e.g., if the highest rank is 8.5, the campaign gets 9.5).
- Moving a campaign to the bottom is done by setting its rank to the current lowest rank decremented by 1 (e.g., if the lowest rank is 0.5, the campaign gets -0.5).
Deletion:
- Deleting a campaign does not require updating the ranks of any other campaigns.

Examples of how `rank` changes on each action

1. Create Campaign

Suppose you have no campaigns. You create three campaigns in sequence:

Create Campaign C → Rank: 1.0
- No changes to other campaigns (none exist).
Create Campaign B → Rank: 2.0 (higher than existing, incremented by 1)
- No changes to other campaigns (C remains 1.0).
Create Campaign A → Rank: 3.0 (higher than existing, incremented by 1)
- No changes to other campaigns (B remains 2.0, C remains 1.0).

Resulting ranks:

A: 3.0 (highest)
B: 2.0
C: 1.0

2. Delete Campaign

Suppose you have:

B: 4.0
A: 3.0
C: 2.5

If you delete campaign A (rank 3.0):

No changes to other campaigns' ranks (B remains 4.0, C remains 2.5).

Resulting ranks:

B: 4.0
C: 2.5

3. Reorder Campaigns

Suppose you have three campaigns:

A: 3.0
B: 2.0
C: 1.0

You want to move C between A and B:

Assign C a rank between A and B, e.g., 2.5
No changes to other campaigns' ranks (A remains 3.0, B remains 2.0).

Resulting ranks:

A: 3.0
C: 2.5 (moved)
B: 2.0

You want to move B to the top:

Assign B a rank by incrementing the highest rank by 1, e.g., 4.0
No changes to other campaigns' ranks (A remains 3.0, C remains 2.5).

Resulting ranks:

B: 4.0 (moved to top)
A: 3.0
C: 2.5

Moving to the bottom:

You want to move A to the bottom (below C which has rank 1.0):

Assign A a rank lower than the current lowest rank, e.g., 0.0
No changes to other campaigns' ranks (B remains 4.0, C remains 1.0).

Resulting ranks:

B: 4.0
C: 1.0
A: 0.0 (moved to bottom)

Moving A even further down:

If you later want to move B below A:

Assign B a rank lower than A’s current rank, e.g., -1.0
No changes to other campaigns' ranks (C remains 1.0, A remains 0.0).

Resulting ranks:

C: 1.0
A: 0.0
B: -1.0 (now at bottom)

Migration considerations

A new rank column will be added to the database table.
Rank values can be any floating-point number (positive, negative, or zero).
Existing priority column will still be kept.
Rank will be calculated and filled for all campaigns based on priority column.
Migration needs to be done atomically and writes to campaigns table must be blocked during that time so that rank is calculated correctly (table-level DB lock).
We will continue updating both priority and rank columns during the transition period.
Only after we make sure that the new rank system works fine, will we remove the priority column and related code.
API will return rank values to the UI as they are (floating point numbers).
The UI must convert rank values into nicely readable monotonically increasing whole numbers (1, 2, 3, 4, …).
UI will have a feature flipper to switch between priority-based and rank-based system.
Device Event Service (serving campaigns to users) will have a feature flipper to switch between priority-based and rank-based system.

`priority` to `rank` calculation

We will calculate rank values for all campaigns in each customer_id group based on their current priority values as follows:

For each customer_id group, fetch all campaigns ordered by ascending priority (i.e., priority = 1 is first, then 2, etc.).
Assign the highest rank value to the campaign with the highest priority (priority = 1), and decrease the rank for each subsequent campaign.
The mapping will be:
Campaign with priority = 1 → rank = N.0 (where N is the total number of campaigns for that customer)
Campaign with priority = 2 → rank = N-1.0
Campaign with priority = 3 → rank = N-2.0
…
Campaign with priority = N → rank = 1.0

Note: We choose to start from 1.0 for convenience during migration, but the system can work with any starting value since ranks can be any floating-point number.

This ensures that the order of campaigns by rank matches the order by priority, but with higher rank values meaning higher precedence.

Example:

Suppose a customer has 3 campaigns:

Campaign	priority	rank (after migration)
A	1	3.0
B	2	2.0
C	3	1.0

PostgreSQL SQL Example:

-- Add the new 'rank' column to the campaign table
ALTER TABLE campaign ADD COLUMN rank double precision;

-- Acquire an exclusive lock to prevent writes during migration
LOCK TABLE campaign IN ACCESS EXCLUSIVE MODE;

-- Update rank based on priority for each customer_id group
WITH ordered_campaigns AS (
  SELECT
    id,
    ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY priority ASC) AS rownum,
    COUNT(*) OVER (PARTITION BY customer_id) AS total
  FROM campaign
)
UPDATE campaign
SET rank = (ordered_campaigns.total - ordered_campaigns.rownum + 1)::double precision
FROM ordered_campaigns
WHERE campaign.id = ordered_campaigns.id;

Note:

The LOCK TABLE … IN ACCESS EXCLUSIVE MODE; statement ensures that no other reads or writes can occur on the campaign table during the migration, guaranteeing data consistency.
This migration should be performed during a maintenance window to minimize impact on users.

Additional Details

Edge Cases & Rebalancing

Rank Collisions: The unique constraint on the rank column prevents campaigns from having identical rank values within the same customer. Any attempt to insert or update a campaign with a duplicate rank will result in a database constraint violation, ensuring data integrity.
Rebalancing: If many reorderings cause ranks to become very close together (e.g., 1.00001, 1.00002, 1.00003), a background job can renormalize ranks to spread them out more evenly. This is not required initially but should be considered for long-term maintenance.

Quick Comparison Table

Feature Old System (priority) New System (rank)

Feature	Old System (`priority`)	New System (`rank`)
Data type	Integer	Double (floating-point)
Uniqueness	Enforced	Enforced
Contiguity	Enforced	Not enforced
Insert cost	O(N)	O(1)
Delete cost	O(N)	O(1)
Reorder cost	O(N)	O(1)
UI display	1, 2, 3, …	1, 2, 3, … (mapped)
DB constraint	Unique, contiguous	Unique, non-contiguous
Rebalancing needed	Never	Rarely (if at all)

Data type

Integer

Double (floating-point)

Uniqueness

Enforced

Contiguity

Enforced

Not enforced

Insert cost

O(N)

O(1)

Delete cost

O(N)

O(1)

Reorder cost

O(N)

O(1)

UI display

1, 2, 3, …

1, 2, 3, … (mapped)

DB constraint

Unique, contiguous

Unique, non-contiguous

Rebalancing needed

Never

Rarely (if at all)

Migration plan and completion tracking

Database migration

Status: ✅ Completed

Steps

Migrate Up:

Add nullable rank column to the database table
Backfill rank column based on priority column for each (customer_id) group
Add index on (application_id, rank) for faster ordering/filtering in Device Event Service.
Add unique constraint on (customer_id, rank) to ensure unique ranks per customer
Make rank column not nullable

Migrate Down:

Remove unique constraint on (customer_id, rank)
Remove index on (application_id, rank)
Remove rank column from the database table

Backend API changes

Status: ✅ Completed

Model changes (`server/models/campaigns.ts`)

Status: ✅ Completed

Add rank field to Campaign type
Add rank to safeColumns array
Add validation schema for rank field
Update tests to pass with rank column
Update table schema constants to include rank

Campaign creation (`server/processes/web/routes/campaigns/post-create-campaign.ts`)

Status: ✅ Completed

[X] Add calculateNextRank query helper for determining initial rank values
[X] Update Campaigns.create to set initial rank value
[X] Add tests for rank-based campaign creation

Campaign deletion (`server/processes/web/routes/campaigns/id/delete-campaign.ts`)

Status: ✅ Already Compatible

Current implementation already works with rank system during transition
No code changes needed - rank column deleted automatically with campaign
Priority logic preserved for backward compatibility during transition
Add tests for rank-based campaign deletion (verify ranks of other campaigns unchanged)

Campaign reordering (`server/processes/web/routes/campaign-priorities`)

Status: ✅ Completed

Add CampaignRanks class for ranks calculation
Modify Campaigns.updatePriorities to calculate and update ranks alongside priorities
Add tests for rank-based campaign reordering

API Response Migration: Calculated Priority from Rank

Status: ✅ Completed

Instead of returning the priority column directly from the database, the API will return a "calculated priority" based on the rank column. This calculated priority will be computed using ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY rank DESC), which gives campaigns with higher ranks lower priority numbers (maintaining the existing UI expectation where priority=1 is highest precedence).

This migration is controllable on a customer-by-customer basis using a feature flipper.

Key Changes

API Behavior: Campaign API endpoints will return calculated priority instead of database priority when flipper is enabled
Calculation Logic: ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY rank DESC) converts rank-based ordering to priority numbers
Backward Compatibility: When flipper is disabled, API continues to return database priority column
Customer Control: Feature flipper allows gradual rollout and easy rollback per customer

Environment Variable Configuration

The feature is controlled by the CALCULATED_PRIORITY_ENABLED_CUSTOMER_IDS environment variable:

Comma-separated customer IDs: '12345,40549,31230' - enables for specific customers only
Star wildcard: '*' - enables for all customers
Empty/unset: disabled for all customers

Affected API Endpoints

Get All Campaigns
- Endpoint: GET /customers/:customerId/campaigns
- Change: priority field in response calculated from rank when flipper enabled
Get Single Campaign
- Endpoint: GET /customers/:customerId/campaigns/:campaignId
- Change: priority field in response calculated from rank when flipper enabled

Implementation Tasks

Add CALCULATED_PRIORITY_ENABLED_CUSTOMER_IDS environment variable
Add flipper configuration using existing buildIsEnabledFor pattern
Modify Campaigns.getCampaigns() to calculate priority from rank when enabled
Modify Campaigns.get() to calculate priority from rank when enabled
Add tests for both flipper states
Log warnings when calculated priority is different from DB priority

SQL Logic for Calculated Priority

When the flipper is enabled, campaigns are queried with calculated priority:

For multiple campaigns:

SELECT
  *,
  ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY rank DESC) AS priority
FROM campaign
WHERE customer_id = ?
ORDER BY rank DESC;

For single campaign (optimized with CTE + JOIN):

WITH campaign_priorities AS (
  SELECT id,
         ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY rank DESC) AS priority
  FROM campaign
  WHERE customer_id = ?
)
SELECT c.*, cp.priority
FROM campaign c
JOIN campaign_priorities cp ON c.id = cp.id
WHERE c.customer_id = ? AND c.id = ?;

This ensures: - Higher rank values get lower priority numbers (rank=10.5 → priority=1) - Priority numbers are contiguous (1, 2, 3, …) as expected by UI - Ordering matches rank-based precedence system - Single campaign queries use CTE + JOIN pattern: CTE calculates priorities (id + priority only), main query fetches full campaign data

In-app campaigns 'priority' to 'rank' migration

Current priority system

Web API endpoints which affect priority

Examples of how priority changes on each action

1. Create Campaign

2. Delete Campaign

3. Bulk Update (Reordering) Priorities

4. Priorities independent per customer, not per application

References in the codebase

1. Create Campaign

2. Delete Campaign

3. Bulk Update (Reordering) Priorities

New rank system

How the rank system works

Examples of how rank changes on each action

1. Create Campaign

2. Delete Campaign

3. Reorder Campaigns

Migration considerations

priority to rank calculation

Additional Details

Edge Cases & Rebalancing

Quick Comparison Table

Migration plan and completion tracking

Database migration

Steps

Backend API changes

Model changes (server/models/campaigns.ts)

Campaign creation (server/processes/web/routes/campaigns/post-create-campaign.ts)

Campaign deletion (server/processes/web/routes/campaigns/id/delete-campaign.ts)

Campaign reordering (server/processes/web/routes/campaign-priorities)

API Response Migration: Calculated Priority from Rank

Key Changes

Environment Variable Configuration

Affected API Endpoints

Implementation Tasks

SQL Logic for Calculated Priority

Current `priority` system

Web API endpoints which affect `priority`

Examples of how `priority` changes on each action

New `rank` system

How the `rank` system works

Examples of how `rank` changes on each action

`priority` to `rank` calculation

Model changes (`server/models/campaigns.ts`)

Campaign creation (`server/processes/web/routes/campaigns/post-create-campaign.ts`)

Campaign deletion (`server/processes/web/routes/campaigns/id/delete-campaign.ts`)

Campaign reordering (`server/processes/web/routes/campaign-priorities`)