Implement Regional Sequence Generators for User Table

[PallaviChitrada10]

Following the Architectural Decision Record: User Identity & Multi-Region Data Residency, the following schema changes need to be implemented in the database to support multi-region user data residency.

Tasks for:
[ ] Implement sequence generator for the us-east (Virginia) region
[ ] Implement sequence generator for the eu-west (Ireland) region
[ ] Implement sequence generator for the eu-north-1 (Stockholm) region

[PallaviChitrada10]

this is a high priority, which should be done within 24 hours before implementation. Your script should also have the testing output. Please take this in.

[Suhasrv2403]

Sequence Generator Update — Review Changes

Approach

Two versions of generate_sid() are implemented for comparison:

Version 1 — Hardcoded prefix
Region identity is explicitly written into the function as a hardcoded string. Each region has its own copy with a different value. Simple to read and debug — the region is immediately obvious from the code.

Version 2 — Math extract
Region identity is derived from the sequence value itself. Since each region starts at a multiple of 20B, dividing by 20B naturally produces the region segment. Same function works identically across all regions — no hardcoding needed anywhere.

	Version 1 (Prefix)	Version 2 (Math)
Same function across regions?	No — one copy per region	Yes — identical everywhere
If sequence drifts slightly	No impact on prefix	Region segment slightly off
If sequence overflows range	Prefix lies about region silently	Naturally increments to next segment
Readability	Immediately clear	Must trace math to understand
Risk	Low	Medium — hidden contract on sequence start

---

ID Format

Non-EU users: SID-001-XXX-XXX-XXX-XXX

EU users: SID-EU-XXX-XXX-XXX-XXX

---

Sequence Ranges

Region	Role	Prefix	Range
Virginia	Normal non-EU	SID-001	1 → 20B
Ireland	DR Virginia	SID-001	20B → 40B
Stockholm	DR Virginia	SID-001	40B → 60B
Ireland	Normal EU	SID-EU	1 → 20B
Stockholm	DR Ireland	SID-EU	20B → 40B

---

DR Chain

• Virginia goes down → Ireland takes over non-EU users (20B → 40B)
• Virginia goes down + Ireland goes down → Stockholm takes over non-EU users (40B → 60B)
• Ireland goes down → Stockholm takes over EU users (20B → 40B)

---

Collision Safety

All ranges are non-overlapping and fully ascending. No cross-region coordination needed during DR — each region owns its range independently. On recovery, records sync back as-is with no primary key violations since IDs from different regions occupy completely separate numeric spaces.

[PallaviChitrada10]

Why would the character length of user_id be 25? It should be the storage type 255.
SID-EU- is 7 chars + 15 digits + 4 hyphens = 26 chars → 27 total, which exceeds VARCHAR(25).
Let’s go with VARCHAR(255); that would help us with the existing architecture.

[PallaviChitrada10]

I have a better approach for this, which I have looked into. @Suhasrv2403, please have this included and tested.
Instead of having a mathematical approach, which is much harder to explain, let's style our sequence generator using SUBSTRING.
String formatting cost (LPAD+SUBSTRING vs FLOOR+MOD) is negligible either way; both are simple, constant-time operations per row, done once at insert. Postgres won't notice the difference even at millions of rows/day.
Let's go and check if we can lean toward SUBSTRING purely because it's fewer expressions to double-check when reviewing/debugging.

-- ============================================================
-- VIRGINIA (Normal, non-EU) — SID-00-, range 1 → 20B
-- ============================================================

CREATE SEQUENCE virginia_dev_saayam_rdbms.user_id_seq
START WITH 1
INCREMENT BY 1
MINVALUE 1
MAXVALUE 19999999999
NO CYCLE;

CREATE OR REPLACE FUNCTION virginia_dev_saayam_rdbms.generate_sid()
RETURNS TRIGGER AS $$
DECLARE
seq_id BIGINT;
padded TEXT;
BEGIN
seq_id := nextval('virginia_dev_saayam_rdbms.user_id_seq');
padded := LPAD(seq_id::TEXT, 15, '0');
NEW.user_id := 'SID-00-' ||
SUBSTRING(padded FROM 1 FOR 3) || '-' ||
SUBSTRING(padded FROM 4 FOR 3) || '-' ||
SUBSTRING(padded FROM 7 FOR 3) || '-' ||
SUBSTRING(padded FROM 10 FOR 3) || '-' ||
SUBSTRING(padded FROM 13 FOR 3);
RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER before_insert_users
BEFORE INSERT ON virginia_dev_saayam_rdbms.users
FOR EACH ROW
EXECUTE FUNCTION virginia_dev_saayam_rdbms.generate_sid();

-- ============================================================
-- IRELAND
-- Normal EU -> SID-EU-, range 1 → 20B
-- DR Virginia (non-EU failover) -> SID-00-, range 20B → 40B
-- ============================================================

CREATE SEQUENCE ireland_dev_saayam_rdbms.user_id_eu_seq
START WITH 1
INCREMENT BY 1
MINVALUE 1
MAXVALUE 19999999999
NO CYCLE;

CREATE SEQUENCE ireland_dev_saayam_rdbms.user_id_dr_seq
START WITH 20000000000
INCREMENT BY 1
MINVALUE 20000000000
MAXVALUE 39999999999
NO CYCLE;

CREATE OR REPLACE FUNCTION ireland_dev_saayam_rdbms.generate_sid()
RETURNS TRIGGER AS $$
DECLARE
seq_id BIGINT;
padded TEXT;
prefix TEXT;
BEGIN
IF NEW.is_eu THEN
seq_id := nextval('ireland_dev_saayam_rdbms.user_id_eu_seq');
prefix := 'SID-EU-';
ELSE
seq_id := nextval('ireland_dev_saayam_rdbms.user_id_dr_seq');
prefix := 'SID-00-';
END IF;
padded := LPAD(seq_id::TEXT, 15, '0');
NEW.user_id := prefix ||
SUBSTRING(padded FROM 1 FOR 3) || '-' ||
SUBSTRING(padded FROM 4 FOR 3) || '-' ||
SUBSTRING(padded FROM 7 FOR 3) || '-' ||
SUBSTRING(padded FROM 10 FOR 3) || '-' ||
SUBSTRING(padded FROM 13 FOR 3);
RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER before_insert_users
BEFORE INSERT ON ireland_dev_saayam_rdbms.users
FOR EACH ROW
EXECUTE FUNCTION ireland_dev_saayam_rdbms.generate_sid();

-- ============================================================
-- STOCKHOLM
-- DR Virginia (non-EU failover) -> SID-00-, range 40B → 60B
-- DR Ireland (EU failover) -> SID-EU-, range 20B → 40B
-- ============================================================

CREATE SEQUENCE stockholm_dev_saayam_rdbms.user_id_dr_seq
START WITH 40000000000
INCREMENT BY 1
MINVALUE 40000000000
MAXVALUE 59999999999
NO CYCLE;

CREATE SEQUENCE stockholm_dev_saayam_rdbms.user_id_eu_dr_seq
START WITH 20000000000
INCREMENT BY 1
MINVALUE 20000000000
MAXVALUE 39999999999
NO CYCLE;

CREATE OR REPLACE FUNCTION stockholm_dev_saayam_rdbms.generate_sid()
RETURNS TRIGGER AS $$
DECLARE
seq_id BIGINT;
padded TEXT;
prefix TEXT;
BEGIN
IF NEW.is_eu THEN
seq_id := nextval('stockholm_dev_saayam_rdbms.user_id_eu_dr_seq');
prefix := 'SID-EU-';
ELSE
seq_id := nextval('stockholm_dev_saayam_rdbms.user_id_dr_seq');
prefix := 'SID-00-';
END IF;
padded := LPAD(seq_id::TEXT, 15, '0');
NEW.user_id := prefix ||
SUBSTRING(padded FROM 1 FOR 3) || '-' ||
SUBSTRING(padded FROM 4 FOR 3) || '-' ||
SUBSTRING(padded FROM 7 FOR 3) || '-' ||
SUBSTRING(padded FROM 10 FOR 3) || '-' ||
SUBSTRING(padded FROM 13 FOR 3);
RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER before_insert_users
BEFORE INSERT ON stockholm_dev_saayam_rdbms.users
FOR EACH ROW
EXECUTE FUNCTION stockholm_dev_saayam_rdbms.generate_sid();

Another precaution for scope: sequence exhaustion monitoring. A Postgres SEQUENCE with MAXVALUE and NO CYCLE is a hard wall. nextval() will happily hand out values until it hits MAXVALUE, and the next call after that throws an error.

That means every new INSERT into users in a region starts failing with zero warning, unless we're watching for it. And because our ranges are a fixed contract between three regions (Ireland's range literally starts where Virginia's ends), we can't just casually raise MAXVALUE on Virginia's sequence one day; it would collide with Ireland's 20B→40B block. Any fix requires re-planning all three ranges and migrating, which is not something we want to discover in mid-incident.
Since 20B is a huge range for typical signup volume, this is unlikely to be an urgent problem soon, but let's have a cron check here and there, and alert when pct_used crosses a threshold; 80% is a reasonable first trigger, maybe a second louder alert at 95%.

SELECT
schemaname,
sequencename,
last_value,
max_value,
ROUND(100.0 * last_value / max_value, 2) AS pct_used
FROM pg_sequences
WHERE schemaname IN ('virginia_dev_saayam_rdbms', 'ireland_dev_saayam_rdbms', 'stockholm_dev_saayam_rdbms');

That's it; let's have the generators checked with the test database you have. Right now, I'm proposing this directly and updating the document for the API team. If you find any issues before our weekly call, do let me know.

Have this checked and get me the outputs of the created user IDs @Suhasrv2403. I hope you can do this within a 24-hour window. Thank you

[Suhasrv2403]

generate_sid() Updated — SUBSTRING Approach

Changes Made

Updated generate_sid() across all three regions to use the SUBSTRING approach as recommended. Region identity is now encoded directly in the sequence range, padded to 15 characters, and sliced into 5 segments of 3.

Generated User ID Output

Virginia — non-EU normal (range 1 → 19,999,999,999)

SID-00-000-000-000-000-007
SID-00-000-000-000-000-008
SID-00-000-000-000-000-009

Ireland — EU normal (range 1 → 19,999,999,999) and DR Virginia (range 20B → 39,999,999,999)

SID-EU-000-000-000-000-004 ← EU normal
SID-EU-000-000-000-000-005 ← EU normal
SID-00-000-020-000-000-003 ← DR Virginia

Stockholm — DR Ireland (range 20B → 39,999,999,999) and DR Virginia (range 40B → 59,999,999,999)

SID-EU-000-020-000-000-004 ← DR Ireland
SID-EU-000-020-000-000-005 ← DR Ireland
SID-00-000-040-000-000-004 ← DR Virginia

How it works

seq_id := nextval('...');
padded := LPAD(seq_id::TEXT, 15, '0');
-- seq_id = 20000000003 → padded = '000020000000003'
-- segments: 000 | 020 | 000 | 000 | 003
-- result:   SID-00-000-020-000-000-003

The range start encodes the region — 20B produces 020 in segment 2, 40B produces 040 in segment 2. No hardcoding needed anywhere.

Tests

All 26 tests pass across all three regions:

test_virginia_generate_sid.sql .. ok (6 tests)
test_ireland_generate_sid.sql .. ok (10 tests)
test_stockholm_generate_sid.sql .. ok (10 tests)

Sequence Exhaustion Monitoring

The cron query suggested in the comment has been noted. Flagging this as a separate task — is this in scope for this PR or should it be tracked as a follow-up issue?

Hi @PallaviChitrada10 I have added the outputs of the generated IDs.