Database Schema

Complete database schema for Tracera

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);

Column	Type	Description
id	UUID	Primary key, auto-generated
email	TEXT	Unique email address
name	TEXT	Display name from OAuth provider
avatar_url	TEXT	Profile picture URL
role	TEXT	User role: user or admin
created_at	TIMESTAMPTZ	Account creation timestamp
updated_at	TIMESTAMPTZ	Last 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);

Column	Type	Description
provider	TEXT	Provider name: google, github, steam, magic_link
provider_id	TEXT	External 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 │
     └──────────┘           └───────────────┘