Skip to main content

​
Database Schema

Tracera uses TimescaleDB (PostgreSQL extension) for all persistent data storage. The schema is organized around three domains: authentication, items/prices, and portfolio.

​
Users

CREATE TABLE users (
    id          UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    email       TEXT UNIQUE NOT NULL,
    name        TEXT NOT NULL DEFAULT '',
    avatar_url  TEXT NOT NULL DEFAULT '',
    role        TEXT NOT NULL DEFAULT 'user',  -- 'user' or 'admin'
    created_at  TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at  TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX idx_users_email ON users (email);
ColumnTypeDescription
idUUIDPrimary key, auto-generated
emailTEXTUnique email address
nameTEXTDisplay name from OAuth provider
avatar_urlTEXTProfile picture URL
roleTEXTUser role: user or admin
created_atTIMESTAMPTZAccount creation timestamp
updated_atTIMESTAMPTZLast update timestamp (auto-updated via trigger)

​
Auth Providers

CREATE TABLE auth_providers (
    id            UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id       UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    provider      TEXT NOT NULL,
    provider_id   TEXT NOT NULL DEFAULT '',
    created_at    TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    UNIQUE(provider, provider_id),
    UNIQUE(user_id, provider)
);

CREATE INDEX idx_auth_providers_user_id ON auth_providers (user_id);
CREATE INDEX idx_auth_providers_lookup ON auth_providers (provider, provider_id);
ColumnTypeDescription
providerTEXTProvider name: google, github, steam, magic_link
provider_idTEXTExternal ID from the OAuth provider
Constraints:
  • UNIQUE(provider, provider_id) — one account per provider identity
  • UNIQUE(user_id, provider) — one link per provider per user

​
Items (Planned)

CREATE TABLE items (
    id               SERIAL PRIMARY KEY,
    market_hash_name TEXT UNIQUE NOT NULL,
    weapon_type      TEXT NOT NULL DEFAULT '',
    skin_name        TEXT NOT NULL DEFAULT '',
    rarity           TEXT NOT NULL DEFAULT '',
    stattrak         BOOLEAN NOT NULL DEFAULT FALSE,
    image_url        TEXT NOT NULL DEFAULT '',
    created_at       TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
Stores CS2 item metadata. Items are identified by their Steam market_hash_name (e.g., “AK-47 | Redline (Field-Tested)”).

​
Price History (Planned)

CREATE TABLE price_history (
    time      TIMESTAMPTZ NOT NULL,
    item_id   INT NOT NULL REFERENCES items(id),
    source    TEXT NOT NULL,
    price     BIGINT NOT NULL,   -- stored in cents
    volume    INT NOT NULL DEFAULT 0,
    listings  INT NOT NULL DEFAULT 0
);

SELECT create_hypertable('price_history', 'time', if_not_exists => TRUE);
This is a TimescaleDB hypertable — automatically partitioned by time for efficient querying of time-series data.
Prices are stored in minor units (cents) as BIGINT. Always divide by 100 for display.

​
Portfolio Holdings (Planned)

CREATE TABLE user_portfolio_holdings (
    id               UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id          UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    steam_asset_id   TEXT NOT NULL,
    item_id          INT NOT NULL REFERENCES items(id),
    market_hash_name TEXT NOT NULL,
    quantity         INT NOT NULL DEFAULT 1,
    imported_at      TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    created_at       TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at       TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    UNIQUE(user_id, steam_asset_id)
);

CREATE TABLE user_portfolio_imports (
    user_id          UUID PRIMARY KEY REFERENCES users(id) ON DELETE CASCADE,
    last_imported_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    imported_assets  INT NOT NULL DEFAULT 0,
    updated_at       TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

​
Entity Relationship

┌──────────┐    1:N    ┌────────────────┐
│  users   │──────────▶│ auth_providers │
└──────────┘           └────────────────┘
     │
     │ 1:N
     ▼
┌───────────────────────┐
│ user_portfolio_holdings│
└───────────┬───────────┘
            │ N:1
            ▼
     ┌──────────┐    1:N    ┌───────────────┐
     │  items   │──────────▶│ price_history │
     └──────────┘           └───────────────┘