Product database schema

Status: PostgreSQL schema for the product app goal sheet and the product backend. The backend owns the executable initial migration, which is applied manually in local and hosted environments. Flyway is a test-only dependency and does not run at application startup. Feature behavior and unresolved policies below remain planned. Updated: 29 October 2026.

Conventions

Use lowercase SQL and snake_case identifiers. All nine primary keys are bigint generated by default as identity primary key, mapped to Kotlin Long; foreign keys are bigint. Identity values may have gaps, including after rollbacks or conflicting inserts. Do not use IDs as request counters.

Timestamps remain UTC Unix seconds stored as bigint, mapped to Kotlin Instant for domain dates. Application projections omit creation/update audit columns. Defaults use floor(extract(epoch from current_timestamp))::bigint. Explicit updates must also maintain updated_at; a default does not update existing rows. Active flags use boolean with default true.

PostgreSQL constraints enforce types, uniqueness, ownership references and restricted parent deletion. Spring owns JDBC transactions. Operators apply baseline and forward migrations manually; Flyway is test-only and installs the schema in the disposable PostgreSQL container used by HTTP integration tests. Hosted Supabase uses the same schema.

Applying the schema

Apply the baseline after PostgreSQL is healthy:

cd apps/product-backend
docker compose --env-file local.env up -d --wait postgres
docker compose --env-file local.env exec -T postgres \
  psql -U postgres -d product_backend -v ON_ERROR_STOP=1 \
  < src/main/resources/database/migrations/V1__product_schema.sql

Change -d product_backend if POSTGRES_DB uses another name. Apply later migration files in filename order. Do not edit an applied migration; add a forward migration instead. See local development for environment keys and startup commands.

Tables

Arrows point from a dependent table to its referenced parent. app_user and redeem_code are standalone tables. profile, login_identity, login_session, and subscription depend directly on the user. subscription_tracking depends on both subscription and user. redeem_code_tracking references the code, consuming user, and granted subscription; composite subscription references enforce matching user ownership.

Working assumption: transaction_id is the server-issued API request transaction ID used to deduplicate usage recording, not a Razorpay payment ID. One row represents one counted request; the subscription’s count is the number of its rows. This interpretation avoids mixing payment transactions with API consumption and remains open for refinement.

-- Initial PostgreSQL product schema. Apply manually; Flyway is used only by tests.
create table app_user (
    id bigint generated by default as identity primary key,
    created_at bigint not null default (floor(extract(epoch from current_timestamp))::bigint),
    updated_at bigint not null default (floor(extract(epoch from current_timestamp))::bigint)
        check (updated_at >= created_at)
);

create table profile (
    id bigint generated by default as identity primary key,
    user_id bigint not null unique,
    display_name text
        check (display_name is null or length(trim(display_name)) > 0),
    created_at bigint not null default (floor(extract(epoch from current_timestamp))::bigint),
    updated_at bigint not null default (floor(extract(epoch from current_timestamp))::bigint)
        check (updated_at >= created_at),
    foreign key (user_id) references app_user(id) on delete restrict
);

create table login_identity (
    id bigint generated by default as identity primary key,
    user_id bigint not null,
    sub text not null check (length(trim(sub)) > 0),
    contact text not null check (length(trim(contact)) > 0),
    provider text not null default 'google'
        check (provider in ('google')),
    contact_type text not null
        check (contact_type in ('email', 'phone')),
    created_at bigint not null default (floor(extract(epoch from current_timestamp))::bigint),
    updated_at bigint not null default (floor(extract(epoch from current_timestamp))::bigint)
        check (updated_at >= created_at),
    unique (provider, sub),
    foreign key (user_id) references app_user(id) on delete restrict
);

create index login_identity_user_idx on login_identity(user_id);

create table login_session (
    id bigint generated by default as identity primary key,
    user_id bigint not null,
    session_id text not null unique check (length(trim(session_id)) > 0),
    expires_at bigint
        check (expires_at is null or
            (expires_at > created_at)),
    is_active boolean not null default true,
    created_at bigint not null default (floor(extract(epoch from current_timestamp))::bigint),
    updated_at bigint not null default (floor(extract(epoch from current_timestamp))::bigint)
        check (updated_at >= created_at),
    foreign key (user_id) references app_user(id) on delete restrict
);

create index login_session_user_idx
    on login_session(user_id, is_active, expires_at);

create table subscription (
    id bigint generated by default as identity primary key,
    user_id bigint not null,
    type text not null check (length(trim(type)) > 0),
    starts_at bigint not null,
    expires_at bigint not null
        check (expires_at > starts_at),
    created_at bigint not null default (floor(extract(epoch from current_timestamp))::bigint),
    updated_at bigint not null default (floor(extract(epoch from current_timestamp))::bigint)
        check (updated_at >= created_at),
    unique (id, user_id),
    foreign key (user_id) references app_user(id) on delete restrict
);

create index subscription_user_period_idx
    on subscription(user_id, starts_at, expires_at);

create table subscription_tracking (
    id bigint generated by default as identity primary key,
    user_id bigint not null,
    transaction_id text not null unique
        check (length(trim(transaction_id)) > 0),
    subscription_id bigint not null,
    created_at bigint not null default (floor(extract(epoch from current_timestamp))::bigint),
    updated_at bigint not null default (floor(extract(epoch from current_timestamp))::bigint)
        check (updated_at >= created_at),
    foreign key (user_id) references app_user(id) on delete restrict,
    foreign key (subscription_id, user_id)
        references subscription(id, user_id) on delete restrict
);

create index subscription_tracking_subscription_idx
    on subscription_tracking(subscription_id, user_id, created_at);

create index subscription_tracking_user_idx
    on subscription_tracking(user_id, created_at);

create table redeem_code (
    id bigint generated by default as identity primary key,
    code_hash text not null unique check (length(trim(code_hash)) > 0),
    subscription_type text not null check (length(trim(subscription_type)) > 0),
    duration_seconds bigint not null
        check (duration_seconds > 0),
    is_active boolean not null default true,
    expires_at bigint
        check (expires_at is null or
            (expires_at > created_at)),
    created_at bigint not null default (floor(extract(epoch from current_timestamp))::bigint),
    updated_at bigint not null default (floor(extract(epoch from current_timestamp))::bigint)
        check (updated_at >= created_at)
);

create table redeem_code_tracking (
    id bigint generated by default as identity primary key,
    redeem_code_id bigint not null,
    user_id bigint not null,
    subscription_id bigint not null unique,
    created_at bigint not null default (floor(extract(epoch from current_timestamp))::bigint),
    updated_at bigint not null default (floor(extract(epoch from current_timestamp))::bigint)
        check (updated_at >= created_at),
    unique (redeem_code_id, user_id),
    foreign key (redeem_code_id) references redeem_code(id) on delete restrict,
    foreign key (user_id) references app_user(id) on delete restrict,
    foreign key (subscription_id, user_id)
        references subscription(id, user_id) on delete restrict
);

create index redeem_code_tracking_user_idx
    on redeem_code_tracking(user_id, created_at);

The forward API key migration adds:

-- Customer API keys. Apply manually after V1; Flyway is used only by tests.
-- value stores the SHA-256 hex digest of the issued key; the plaintext is returned once at creation.
create table api_key (
    id bigint generated by default as identity primary key,
    name text not null check (length(trim(name)) > 0),
    description text
        check (description is null or length(trim(description)) > 0),
    value text not null unique check (length(trim(value)) > 0),
    user_id bigint not null,
    revoked boolean not null default false,
    created_at bigint not null default (floor(extract(epoch from current_timestamp))::bigint),
    updated_at bigint not null default (floor(extract(epoch from current_timestamp))::bigint)
        check (updated_at >= created_at),
    foreign key (user_id) references app_user(id) on delete restrict
);

create index api_key_user_idx
    on api_key(user_id, revoked);

API key rules

value holds the lowercase hex SHA-256 digest of the issued key (ak_ plus 32 random bytes, base64url). The plaintext is returned only by the create endpoint and is never stored. Revocation sets revoked = true and updated_at; rows are never deleted. Unlike other projections, ApiKey maps created_at and updated_at because the key list exposes them. Every read and update is scoped to the access token’s user ID.

Profile rules

profile keeps optional presentation data out of app_user and provider identities. The proposed initial field is nullable display_name; no birth details, avatar, or extra contact fields are assumed. A unique user_id enforces at most one profile per user and provides its lookup index. The foreign key prevents profiles without an existing user; it does not require every user to have a profile.

Create the profile when needed, allow the user to set or clear their display name, and update updated_at on changes. A missing profile or null display name must not block login or API access. Profile operations must use the authenticated account’s user ID. Email and phone remain in login_identity; profile data is not used as identity proof.

Identity rules

provider is constrained to google initially. Adding apple later requires a schema migration extending that check. contact_type accepts only email or phone; these enum checks validate the category, not the contact’s syntax, ownership, or verification status. Provider token validation and email/phone validation belong to the login service.

Use the validated (provider, sub) pair to find the account. Never merge accounts solely because their contacts match. A contact is deliberately not unique: it can change or appear across providers. Linking a new identity requires proof of control of both the signed-in account and the new provider identity.

Apple’s documented Sign in with Apple identity flow provides email, including relay email; a phone number used for an Apple account should not be assumed to be available to this app. Phone remains a supported contact category for a future independently verified flow. Apple authentication documentation.

Session rules

id identifies the database row. session_id is a unique, non-secret session reference. It is not sufficient on its own to authenticate a request: the backend signs a random UUID into the sid header of its access and refresh JWTs, and validation requires a correctly signed token whose subject matches this row’s user_id. The session row therefore stores no token verifier; the signing key and token validation enforce authenticity.

user_id associates every session with its owner. expires_at = null means no scheduled expiry; otherwise the session expires at the specified UTC Unix second. is_active = true means enabled and is_active = false means revoked. Both finite and non-expiring sessions can be revoked. When is_active = false, updated_at records when the session was deactivated. To preserve that meaning, deactivation is the final update to a session row: do not reactivate or modify inactive rows; create a new session for a new login. Expiration alone does not change is_active or updated_at.

Retain expired and inactive rows to list a user’s session history; displaying only usable sessions requires both the flag and the expiry check:

select id, session_id, created_at, expires_at
from login_session
where user_id = ?
  and is_active = true
  and (expires_at is null or expires_at > floor(extract(epoch from current_timestamp))::bigint);

Revoke an owned session without deleting its history:

update login_session
set is_active = false, updated_at = floor(extract(epoch from current_timestamp))::bigint
where id = ? and user_id = ? and is_active = true;

The user ID in session listing and revocation must come from the authenticated account. Repeating revocation leaves the original timestamp unchanged. Revoking all sessions uses the same update scoped by user_id, without the id predicate. Authentication must check revocation and expiry on subsequent requests, including for non-expiring sessions; any cache propagation delay must be explicitly defined. Revoking the current session also clears the browser credential. Device labels, last activity, and retention duration are deferred.

Subscription and counting rules

type is a plan code. Its allowed values have not been agreed, so the initial check only rejects blank values. Once plan codes are defined, use an explicit check (type in (...)) or a foreign key to a plan table; do not invent commercial tiers in the migration.

The proposed validity interval is starts_at <= request_time < expires_at. Every subscription has an expiry in this draft. Overlapping subscriptions are allowed by the SQL; define selection priority before serving requests. Cancellation, suspension, and early revocation need a separate agreed lifecycle design.

The composite foreign key prevents recording user A’s request against user B’s subscription. The globally unique, server-issued transaction_id prevents a duplicate usage event from increasing the count twice, even if it is retried with a different subscription. A conflicting retry must be checked against the original event, not silently reassigned. Clients must not be allowed to reuse arbitrary transaction IDs to bypass counting.

Count per subscription with:

select subscription_id, count(*) as request_count
from subscription_tracking
where user_id = ?
group by subscription_id;

Usage rows are append-only by application policy. updated_at remains present for consistency with the common columns. Request eligibility, outcomes that consume allowance, event timing, and late event handling are still open; created_at currently means recording time, not necessarily request time. Add occurred_at if request-time reporting is needed.

This ledger records usage; it does not by itself enforce quotas or rate limits. A read-count-then-insert sequence can overspend under concurrency. Atomic admission, shared rate state, quota snapshots, and failure behavior must be designed before enforcement is implemented.

Redeem-code rules — future capability

redeem_code defines the grant: subscription_type is the plan code, and duration_seconds is the subscription lifetime from redemption. These are proposed minimum grant fields; plan values remain open. expires_at is the deadline for consuming the code, independent of the granted subscription’s expiry. Null means no redemption deadline. Only an active, unexpired code is eligible.

Store a deterministic cryptographic verifier in code_hash, not the raw redeemable secret. Generate codes with sufficient randomness; define normalization and hashing before implementation. Do not expose verifiers or log submitted codes.

redeem_code_tracking records successful consumption only. created_at is the consumption time, and subscription_id links to the exact entitlement granted without requiring a Razorpay payment. The composite foreign key prevents a redemption from granting another user’s subscription. The subscription row snapshots the granted type and start/expiry times so later code changes do not rewrite past grants.

Proposed initial policy: a user may consume a particular code once, enforced by unique (redeem_code_id, user_id). Multiple users may consume the same code; whether codes are globally single-use, capped, or restricted to eligible users remains open. No global redemption cap is enforced by this draft. A subscription can be linked to only one successful redemption. Code disabling blocks future redemption; it does not cancel existing grants.

Validate eligibility and create the subscription and tracking record atomically in the Spring service transaction. Concurrent attempts and retries must produce one grant; on a duplicate user/code pair, return the existing result after verifying ownership. Failed redemption must leave neither an orphan subscription nor a consumption record. These cross-table rules and expiry/type checks are service requirements, not guarantees of the foreign keys alone. Keep successful tracking records immutable. Define renewal, stacking, and existing-subscriber eligibility before enabling this future capability.

Remaining links for later schema refinement

These are tracked gaps rather than extra tables with speculative contracts. Spring Boot accesses PostgreSQL directly through JDBC; hosted production persistence will use Supabase. Request tracking will be asynchronous and remains application work owned by the user; no synchronous quota reservation is introduced by this configuration change.