Skip to main content

teudb - The User Database

teudb (TradeEntry User DB) is a separate Postgres database from tedb, on the same server. It holds identity, categories, plans, entitlements, verification state and per-user settings.

Why a separate database, not a schema​

The market-data pipelines routinely do destructive bulk operations on tedb - deleteadateabove() rollbacks, staging-table truncations after a Parquet pack, and full restores from backup_tedb.sh. Any of those, run with a wrong date or against a wrong backup, would take user accounts with them if identities lived in the same database.

Physical separation makes that impossible. The cost is one extra async engine and no cross-database joins - and there is no join we need, because usr_master.usrid is never referenced from tedb, and no market table is ever referenced from teudb.

Never join across the two databases

teudb and tedb are different physical databases. There is no query that can join usr_master to scr_master. If you find yourself wanting to, the answer is to carry the value across in application code.

Naming conventions​

Table prefix: usr_, consistent with the repository's existing domain prefixes:

PrefixOwnerExample
scr_Instrument registriesscr_master, scr_global_master
st_Strategy trackerst_group, st_master
te_Generic time-series factste_asset, te_price
usr_User identity and access (this database)usr_master, usr_plan

Column conventions follow the repo: short surrogate keys (usrid, catid, planid, featid, subid) in the spirit of masid; validity windows named sdate/edate; timestamps named created_at/updated_at with server_default=func.now(), matching the AA/models.py precedent.

Entity relationships​

The six categories​

catidcatcodeMeaningMobile verification
0ANONPseudo-row: an unauthenticated visitor. Never assigned to a real user.n/a
1ADMINFull control including user managementRequired in practice
2DEVELOPERAll pages and pipelines, no user managementRequired in practice
3POWERProduct evaluatorsMandatory
4PAIDSubscribers; features determined by planMandatory
5FREEIdentified users - email and mobile on fileNot required
Why ANON is a real row

Modelling "open to all" as catid = 0 means entitlement resolution has exactly one code path. An anonymous request resolves against the ANON row and receives its grants, rather than being a special case scattered through route handlers and nav components.

It also means the public/private boundary is data, not code - changing what a visitor can see is an INSERT into usr_cat_feature, reviewable in a migration, not a hunt through Python and JSX.

POWER ranks above PAID because the brief describes power users as internal evaluators - they should see at least everything a paying customer sees.

Schema​

usr_category​

CREATE TABLE usr_category (
    catid           SMALLINT     PRIMARY KEY,
    catcode         TEXT         NOT NULL UNIQUE,
    catname         TEXT         NOT NULL,
    rank            SMALLINT     NOT NULL UNIQUE,   -- 0=ANON .. 50=ADMIN
    needs_email     BOOLEAN      NOT NULL DEFAULT true,
    needs_mobile    BOOLEAN      NOT NULL DEFAULT false,
    is_paid         BOOLEAN      NOT NULL DEFAULT false,
    created_at      TIMESTAMPTZ  NOT NULL DEFAULT now(),
    CONSTRAINT ck_usr_category_code
        CHECK (catcode IN ('ANON','FREE','PAID','POWER','DEVELOPER','ADMIN'))
);

Reference data - seeded by migration, never edited at runtime. rank gives require_min_rank('DEVELOPER') semantics without an inheritance table.

usr_master​

CREATE TABLE usr_master (
    usrid              BIGSERIAL   PRIMARY KEY,
    email              CITEXT      NOT NULL UNIQUE,
    mobile_cc          TEXT        NULL,          -- '+91'
    mobile_no          TEXT        NULL,          -- digits only
    pwd_hash           TEXT        NOT NULL,      -- argon2id
    pwd_changed_at     TIMESTAMPTZ NOT NULL DEFAULT now(),
    fullname           TEXT        NULL,
    catcode            TEXT        NOT NULL DEFAULT 'FREE'
                                   REFERENCES usr_category(catcode),
    entver             INTEGER     NOT NULL DEFAULT 1,
    email_verified_at  TIMESTAMPTZ NULL,
    mobile_verified_at TIMESTAMPTZ NULL,
    status             TEXT        NOT NULL DEFAULT 'pending',
    last_login_at      TIMESTAMPTZ NULL,
    failed_logins      SMALLINT    NOT NULL DEFAULT 0,
    locked_until       TIMESTAMPTZ NULL,
    created_at         TIMESTAMPTZ NOT NULL DEFAULT now(),
    updated_at         TIMESTAMPTZ NOT NULL DEFAULT now(),

    CONSTRAINT ck_usr_master_status
        CHECK (status IN ('pending','active','suspended','deleted')),
    CONSTRAINT ck_usr_master_never_anon
        CHECK (catcode <> 'ANON'),
    -- An active account has at minimum a verified email
    CONSTRAINT ck_usr_master_active_needs_email
        CHECK (status <> 'active' OR email_verified_at IS NOT NULL),
    -- THE differential-verification rule, enforced in the database
    CONSTRAINT ck_usr_master_mobile_required
        CHECK (catcode IN ('FREE') OR mobile_verified_at IS NOT NULL),
    CONSTRAINT ck_usr_master_mobile_pair
        CHECK ((mobile_cc IS NULL) = (mobile_no IS NULL))
);

CREATE UNIQUE INDEX ux_usr_master_mobile
    ON usr_master (mobile_cc, mobile_no) WHERE mobile_no IS NOT NULL;
CREATE INDEX ix_usr_master_catcode ON usr_master (catcode);
CREATE INDEX ix_usr_master_status  ON usr_master (status);
ck_usr_master_mobile_required is the requirement, in the schema

This constraint is the literal encoding of the project leader's rule: email verification is enough for first-time users; mobile verification is mandatory for power and paid users.

An UPDATE usr_master SET catcode='PAID' on a row with mobile_verified_at IS NULL raises a constraint violation. The rule cannot be bypassed by a service-layer bug, a bad admin script, or a direct psql session.

The promotion service must catch IntegrityError and return HTTP 409 with an actionable message, never a 500.

ux_usr_master_mobile needs the same treatment: POST /api/auth/request-otp (app/auth/service.py:request_mobile_otp) writes the submitted number straight onto usr_master before sending the OTP, so a number already claimed by another account raises the same IntegrityError the promotion path does. It's caught the same way - ux_usr_master_mobile in str(e.orig) maps to HTTP 409 ("This mobile number is already registered to another account"), never a bare 500. Two accounts can't share a mobile number, including an ADMIN's - a number registered to the one ADMIN row has to be cleared from it before another account can claim it.

Why catcode TEXT rather than catid INT: so the CHECK constraint above can be written in readable, literal form. The small denormalisation cost buys an enforced invariant.

CITEXT requires CREATE EXTENSION IF NOT EXISTS citext; as the first statement of the initial migration. Verify availability first:

SELECT * FROM pg_available_extensions WHERE name = 'citext';

If unavailable, substitute TEXT, store lower(email), and add CREATE UNIQUE INDEX ux_usr_master_email ON usr_master (lower(email)). CITEXT is preferred - it eliminates the whole class of "signed up with Mixed.Case, cannot log in" bugs.

entver is the entitlement version. It is embedded in every issued JWT and bumped on any category or subscription change. See Instant revocation below.

usr_plan​

CREATE TABLE usr_plan (
    planid        SERIAL        PRIMARY KEY,
    plancode      TEXT          NOT NULL UNIQUE,   -- 'BASIC','PRO','DESK'
    planname      TEXT          NOT NULL,
    catcode       TEXT          NOT NULL REFERENCES usr_category(catcode),
    price_inr     NUMERIC(10,2) NOT NULL DEFAULT 0,
    billing_cycle TEXT          NOT NULL DEFAULT 'monthly',
    duration_days INTEGER       NOT NULL DEFAULT 30,
    is_active     BOOLEAN       NOT NULL DEFAULT true,
    sort_order    SMALLINT      NOT NULL DEFAULT 0,
    description   TEXT          NULL,
    created_at    TIMESTAMPTZ   NOT NULL DEFAULT now(),
    CONSTRAINT ck_usr_plan_cycle
        CHECK (billing_cycle IN ('monthly','quarterly','annual','lifetime','trial'))
);

Seeded plans: FREE_TIER (FREE, zero), POWER_EVAL (POWER, zero, 30-day trial), PAID_BASIC, PAID_PRO, PAID_DESK. The catcode column links plan to tier, so buying PAID_PRO implies catcode='PAID'.

usr_feature - the module catalog​

CREATE TABLE usr_feature (
    featid      SERIAL      PRIMARY KEY,
    featcode    TEXT        NOT NULL UNIQUE,    -- 'aa','option_chain', ...
    featname    TEXT        NOT NULL,
    kind        TEXT        NOT NULL DEFAULT 'page',
    route       TEXT        NULL,               -- '/option-chain'
    api_prefix  TEXT        NULL,               -- '/api/option-chain'
    icon        TEXT        NULL,               -- lucide icon name
    sort_order  SMALLINT    NOT NULL DEFAULT 0,
    is_active   BOOLEAN     NOT NULL DEFAULT true,
    created_at  TIMESTAMPTZ NOT NULL DEFAULT now(),
    CONSTRAINT ck_usr_feature_kind CHECK (kind IN ('page','api','action'))
);

This table is the single source of truth for the navigation bar. Carrying route and icon here is what lets Header.jsx stop hardcoding its navLinks array and render from entitlements instead.

Seed rows, one per existing module:

featcodeNamerouteapi_prefix
homeHome/-
aaAsset Allocation/asset-allocation/api/aa
dashboardDashboard/dashboard/api
analyticsAnalytics/analytics/api
option_chainOption Chain/option-chain/api/option-chain
strategy_builderStrategy Builder/strategy-builder/api/strategy-builder
bhav_downloaderBhav Downloader/bhav-downloader/api/bhav-downloader
mcx_downloaderMCX Downloader-/api/mcx-downloader
eodieod_uploadEODIEOD Upload/eodieod-upload/api/eodieod-upload
contract_noteContract Note/contract-note/api/contract-note
ieod_spotfutIEOD Spot-Fut/ieod-spot-fut/api/ieod-spotfut
holiday_masterHoliday Master/holiday-master/api/holidays
live_wsLive Market Feed-/ws/market-data
admin_usersUser Administration/admin/users/api/admin

usr_cat_feature - baseline grants by category​

CREATE TABLE usr_cat_feature (
    catcode    TEXT        NOT NULL REFERENCES usr_category(catcode) ON DELETE CASCADE,
    featid     INTEGER     NOT NULL REFERENCES usr_feature(featid) ON DELETE CASCADE,
    can_read   BOOLEAN     NOT NULL DEFAULT true,
    can_write  BOOLEAN     NOT NULL DEFAULT false,
    created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
    PRIMARY KEY (catcode, featid)
);
This table IS Enhancement 1

The project leader's requirement - "Only Asset allocation page would be visible for all, rest of all pages should come under admin and developers" - is expressed entirely as seed rows here:

-- The ONLY two rows for ANON
INSERT INTO usr_cat_feature (catcode, featid, can_read)
SELECT 'ANON', featid, true FROM usr_feature WHERE featcode IN ('aa','home');

Give this seed block dedicated review attention. It is the public/private boundary of the entire product.

usr_plan_feature - per-plan additions​

CREATE TABLE usr_plan_feature (
    planid        INTEGER     NOT NULL REFERENCES usr_plan(planid) ON DELETE CASCADE,
    featid        INTEGER     NOT NULL REFERENCES usr_feature(featid) ON DELETE CASCADE,
    can_read      BOOLEAN     NOT NULL DEFAULT true,
    can_write     BOOLEAN     NOT NULL DEFAULT false,
    quota_per_day INTEGER     NULL,   -- NULL = unlimited
    created_at    TIMESTAMPTZ NOT NULL DEFAULT now(),
    PRIMARY KEY (planid, featid)
);

Entitlement resolution rule​

Write this once, in app/auth/entitlements.py, and nowhere else

Effective entitlements are the union of:

  1. usr_cat_feature rows for the user's catcode, plus
  2. usr_plan_feature rows for every currently-active subscription.

can_write is OR-ed. quota_per_day takes the most generous value across active plans, with NULL (unlimited) beating any number.

There is no deny-list. Grants are additive only. This keeps the resolver a single query with no precedence puzzles - which is exactly why it can be trusted.

An "active" subscription is status='active' AND sdate <= CURRENT_DATE AND (edate IS NULL OR edate >= CURRENT_DATE). Expiry is evaluated lazily in that predicate, so v2.0 needs no cron sweeper.

usr_subscription​

CREATE TABLE usr_subscription (
    subid       BIGSERIAL     PRIMARY KEY,
    usrid       BIGINT        NOT NULL REFERENCES usr_master(usrid) ON DELETE CASCADE,
    planid      INTEGER       NOT NULL REFERENCES usr_plan(planid),
    sdate       DATE          NOT NULL,
    edate       DATE          NULL,          -- NULL = open-ended
    status      TEXT          NOT NULL DEFAULT 'active',
    amount_inr  NUMERIC(10,2) NULL,
    payment_ref TEXT          NULL,          -- opaque; no gateway in v2.0
    granted_by  BIGINT        NULL REFERENCES usr_master(usrid),
    note        TEXT          NULL,
    created_at  TIMESTAMPTZ   NOT NULL DEFAULT now(),
    updated_at  TIMESTAMPTZ   NOT NULL DEFAULT now(),
    CONSTRAINT ck_usr_sub_status
        CHECK (status IN ('active','expired','cancelled','pending')),
    CONSTRAINT ck_usr_sub_dates
        CHECK (edate IS NULL OR edate >= sdate)
);
CREATE INDEX ix_usr_sub_usrid_status ON usr_subscription (usrid, status);
CREATE INDEX ix_usr_sub_edate ON usr_subscription (edate) WHERE status = 'active';

usr_verification - email links and mobile OTPs​

CREATE TABLE usr_verification (
    verid        BIGSERIAL   PRIMARY KEY,
    usrid        BIGINT      NOT NULL REFERENCES usr_master(usrid) ON DELETE CASCADE,
    channel      TEXT        NOT NULL,
    purpose      TEXT        NOT NULL,
    token_hash   TEXT        NOT NULL UNIQUE,   -- sha256; NEVER the raw value
    destination  TEXT        NOT NULL,          -- snapshot of email/mobile sent to
    attempts     SMALLINT    NOT NULL DEFAULT 0,
    max_attempts SMALLINT    NOT NULL DEFAULT 5,
    expires_at   TIMESTAMPTZ NOT NULL,
    consumed_at  TIMESTAMPTZ NULL,
    created_at   TIMESTAMPTZ NOT NULL DEFAULT now(),
    created_ip   INET        NULL,
    CONSTRAINT ck_usr_verification_channel
        CHECK (channel IN ('email','mobile')),
    CONSTRAINT ck_usr_verification_purpose
        CHECK (purpose IN ('signup','password_reset','change_email',
                           'change_mobile','tier_upgrade')),
    CONSTRAINT ck_usr_verification_attempts CHECK (attempts <= max_attempts)
);
CREATE INDEX ix_usr_verification_open ON usr_verification (usrid, channel, purpose)
    WHERE consumed_at IS NULL;
Never store the raw token or OTP

token_hash holds sha256(value). Verification hashes the submitted value and compares. A database dump must not let anyone complete a verification or reset a password.

TTLs: signup email link 24h, password_reset 1h, mobile OTP 10 minutes. Resend throttle: at most 3 unexpired unconsumed rows per user per purpose, otherwise HTTP 429.

usr_session - refresh tokens​

CREATE TABLE usr_session (
    sesid         BIGSERIAL   PRIMARY KEY,
    usrid         BIGINT      NOT NULL REFERENCES usr_master(usrid) ON DELETE CASCADE,
    token_hash    TEXT        NOT NULL UNIQUE,   -- sha256 of the refresh token
    family_id     UUID        NOT NULL,          -- rotation family
    parent_sesid  BIGINT      NULL REFERENCES usr_session(sesid),
    issued_at     TIMESTAMPTZ NOT NULL DEFAULT now(),
    expires_at    TIMESTAMPTZ NOT NULL,
    revoked_at    TIMESTAMPTZ NULL,
    revoke_reason TEXT        NULL,
    last_used_at  TIMESTAMPTZ NULL,
    user_agent    TEXT        NULL,
    ip_addr       INET        NULL
);
CREATE INDEX ix_usr_session_live   ON usr_session (usrid) WHERE revoked_at IS NULL;
CREATE INDEX ix_usr_session_family ON usr_session (family_id);

Rotation with reuse detection. On /refresh, the presented token is revoked and a child issued in the same family_id. If a token that is already revoked is presented, the entire family is revoked and the user must log in again - the signature of a stolen token being replayed.

Interaction with the frontend refresh interceptor

Reuse detection and a naive axios interceptor fight each other. The Dashboard fires several parallel requests; if all of them independently call /refresh on a 401, the second rotation presents an already-revoked token and the family is killed - the user is logged out for no reason.

The frontend must use a single-flight guard so concurrent 401s await one shared refresh promise. This is called out as an explicit acceptance criterion on Patch 13.

usr_setting - per-user UI preferences​

CREATE TABLE usr_setting (
    usrid         BIGINT      PRIMARY KEY REFERENCES usr_master(usrid) ON DELETE CASCADE,
    theme         TEXT        NOT NULL DEFAULT 'midnight',
    density       TEXT        NOT NULL DEFAULT 'comfortable',
    default_page  TEXT        NULL,
    timezone      TEXT        NOT NULL DEFAULT 'Asia/Kolkata',
    number_format TEXT        NOT NULL DEFAULT 'en-IN',
    extra         JSONB       NOT NULL DEFAULT '{}'::jsonb,
    updated_at    TIMESTAMPTZ NOT NULL DEFAULT now(),
    CONSTRAINT ck_usr_setting_theme
        CHECK (theme IN ('midnight','deep-ocean','carbon-amber','daylight')),
    CONSTRAINT ck_usr_setting_density
        CHECK (density IN ('compact','comfortable'))
);

This is the table the project leader asked for - "maintain separate Database for user level setting". theme is a real column with a CHECK because it is queried and reported on and must match the four data-theme values exactly; extra JSONB is the escape hatch so adding a preference never needs a migration.

usr_audit​

CREATE TABLE usr_audit (
    audid       BIGSERIAL   PRIMARY KEY,
    usrid       BIGINT      NULL REFERENCES usr_master(usrid) ON DELETE SET NULL,
    actor_usrid BIGINT      NULL REFERENCES usr_master(usrid) ON DELETE SET NULL,
    action      TEXT        NOT NULL,
    object_type TEXT        NULL,
    object_id   TEXT        NULL,
    detail      JSONB       NOT NULL DEFAULT '{}'::jsonb,
    ip_addr     INET        NULL,
    user_agent  TEXT        NULL,
    created_at  TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE INDEX ix_usr_audit_usrid_time  ON usr_audit (usrid, created_at DESC);
CREATE INDEX ix_usr_audit_action_time ON usr_audit (action, created_at DESC);

usrid is the subject; actor_usrid is who performed it - they differ for admin actions. Actions include login.ok, login.fail, register, verify.email, verify.mobile, category.change, plan.grant, token.reuse_detected, admin.suspend, admin.bootstrap.

Never log secrets into detail

No passwords, no raw tokens, no OTPs. The audit log is the one table most likely to be exported for support purposes.

Instant revocation​

An entitlement change must take effect immediately, but re-resolving features from the database on every single request costs a join per request.

The entver column solves both:

  1. On login, resolved feature codes are embedded in the JWT along with entver.
  2. Every request does one cheap primary-key lookup: SELECT entver, status FROM usr_master WHERE usrid = $1.
  3. If entver does not match the token's claim, or status <> 'active', return 401 - the client refreshes and gets current entitlements.
  4. Any mutation that changes access bumps entver.
Bump entver in exactly one place

Every path that changes a category, grants or revokes a subscription, or suspends a user must bump it. Implement a single _bump_entver(session, usrid) helper in app/auth/service.py and call it from every such mutation - and unit-test that each admin mutation actually bumps.

A forgotten bump means a suspended user keeps working for up to 15 minutes.

Tier promotion flow​

DEVELOPER and ADMIN are never self-service. POST /api/admin/users/{usrid}/category (app/api/admin_routes.py, ADMIN-gated) grants FREE/PAID/POWER/DEVELOPER to an existing user, and every grant writes a usr_audit row with actor_usrid set. It can never grant ADMIN itself — see below.

Creating the first admin​

There is exactly one ADMIN, enforced at two levels:

  • App level — admin_set_category (app/auth/service.py) restricts POST /api/admin/users/{usrid}/category to FREE/PAID/POWER/DEVELOPER; ADMIN is not a grantable target through the API, ever.
  • DB level — ux_usr_master_single_admin, a partial unique index (CREATE UNIQUE INDEX ... ON usr_master (catcode) WHERE catcode = 'ADMIN', migration 806cdcd16a87) rejects a second ADMIN row even from a direct psql session or a future script.

Because of that, the only row that is ever ADMIN is the one created by the bootstrap CLI, run once, before any other ADMIN exists:

cd backend
python -m scripts.create_admin

(Never python scripts\create_admin.py — see root CLAUDE.md HARD RULE 1.)

The script prompts interactively for email, mobile country code + number, an optional full name, and a password (min 12 characters, confirmed twice). It then creates the row with catcode='ADMIN', email_verified_at/mobile_verified_at/status='active' already set — skipping the normal register → verify-email → add-mobile → verify-otp chain, because there is no existing ADMIN yet to grant the category through the API. It refuses to run if a usr_master row with that email already exists, and if usr_category has no ADMIN row yet, run alembic -c alembic_user.ini upgrade head first.

This script cannot promote an existing user to ADMIN — it only creates a fresh row, and the single-admin index above would reject the insert once an ADMIN already exists. Promoting an existing account this way requires a manual UPDATE usr_master SET catcode='ADMIN' ... (which also fails the unique index if an ADMIN already exists) followed by a usr_audit row recording the change — see git log for action='admin.bootstrap' for a worked example.

Admin API reference​

app/api/admin_routes.py, ADMIN-gated at include_router time (see User System Design "Route gating policy"):

EndpointPurpose
GET /api/admin/users?search=&limit=&offset=Lists usr_master rows (app/auth/service.py:list_users) — search matches email or fullname as a case-insensitive substring, limit caps at 100. Powers the frontend's User Management table.
POST /api/admin/users/{usrid}/category{"target_catcode": "FREE"|"PAID"|"POWER"|"DEVELOPER"} — see "Creating the first admin" above for why ADMIN is never a valid value here.
POST /api/admin/users/{usrid}/status{"target_status": "active"|"suspended"|"deleted"} (app/auth/service.py:admin_set_status) — the "admin action" transitions in the account-lifecycle diagram above, plus deleted as the "remove a user" case. Never accepts pending (system-assigned only) and refuses to touch the ADMIN row, same as the category endpoint.

admin_set_status is a soft delete - target_status='deleted' sets usr_master.status, it never issues a row DELETE. It bumps entver (kills the access token within one request) and calls logout_all_sessions (kills the refresh token too), so removal is immediate, not "expires in 15 minutes." Reversible by the same endpoint with target_status='active' - which can itself 409 if the account's email_verified_at was never set (ck_usr_master_active_needs_email), since a pending row that gets removed before ever verifying its email can't skip straight to active.

The frontend surface is frontend/src/components/AdminUserManagement.jsx, rendered inside Profile.jsx only when catcode === 'ADMIN' — it replaces the self-service tier-upgrade card for the admin viewer (an admin doesn't self-upgrade; they manage everyone else instead). Each row shows a Remove button (with a confirm prompt) for an active/pending/suspended user, or a Reactivate button for a suspended/deleted one.

GET /api/auth/me (public surface, any authenticated user) also returns email, fullname, and mobile_verified alongside the original usrid/catcode/feats - one extra usr_master row lookup, since none of those three are in the JWT claims. Profile.jsx uses mobile_verified to show a "Mobile: Verified" badge or an "Add & Verify" link to /verify-mobile - previously nothing on the Profile page surfaced this, so a user with an unverified mobile had no way to discover the flow existed short of hitting the self-service upgrade's 409.

Connection pooling​

teudb gets its own engine with a smaller pool (pool_size=5, max_overflow=10) than the market-data engine (10/20). Auth queries are short and frequent; market queries are long and few.

Check max_connections before rollout

Two pools now share the server: up to 45 connections, plus the nseeod/nseieod psycopg2 pipelines and any psql sessions. On a resource-constrained VPS, confirm headroom:

SHOW max_connections;

Lower the teudb pool if it is tight.