Database Schema

Database schema for a PostgreSQL database.

1. Users

Deepseek:

CREATE TABLE users (
    user_id       UUID PRIMARY KEY,
    username      VARCHAR(50) UNIQUE,
    email         VARCHAR(100) UNIQUE,
    password_hash VARCHAR(255), -- Only for local authentication (optional)
    created_at    TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    last_login    TIMESTAMP WITH TIME ZONE,
    is_active     BOOLEAN DEFAULT TRUE
);

ChatGPT

CREATE TABLE users (
  id             SERIAL       PRIMARY KEY,
  email          VARCHAR(255) UNIQUE NOT NULL,
  display_name   VARCHAR(100),
  created_at     TIMESTAMPTZ  NOT NULL DEFAULT NOW()
);

2. User Identities

Deepseek:
(I had to remind Deepseek of authentication requirements to get it to create this table)

-- Separate table for OAuth identities
CREATE TABLE user_oauth_identities (
    identity_id       UUID PRIMARY KEY,
    user_id           UUID REFERENCES users(user_id) ON DELETE CASCADE,
    provider          VARCHAR(20) NOT NULL CHECK (provider IN ('line', 'google')),
    provider_user_id  VARCHAR(100) NOT NULL, -- LINE or Google's unique ID
    email             VARCHAR(100), -- From provider
    name              VARCHAR(100), -- From provider
    profile_image     TEXT, -- URL to avatar
    access_token      TEXT, -- Encrypted
    refresh_token     TEXT, -- Encrypted
    token_expiry      TIMESTAMP WITH TIME ZONE,
    UNIQUE(provider, provider_user_id) -- One identity per provider
);

ChatGPT

-- Link to external auth providers (LINE, Google, etc.)
CREATE TABLE auth_identities (
  id                  SERIAL       PRIMARY KEY,
  user_id             INTEGER      NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  provider            VARCHAR(50)  NOT NULL,        -- e.g. 'line', 'google'
  provider_user_id    VARCHAR(255) NOT NULL,        -- external user ID
  created_at          TIMESTAMPTZ  NOT NULL DEFAULT NOW(),
  UNIQUE (provider, provider_user_id)
);

2. Homes (Locations)

Deekseek:

CREATE TABLE homes (
    home_id           UUID PRIMARY KEY,
    name              VARCHAR(100) NOT NULL,
    address           TEXT,
    timezone          VARCHAR(50) DEFAULT 'UTC',
    created_at        TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    created_by        UUID REFERENCES users(user_id)
);

ChatGPT: No table suggested

3. Data Sources

Deepseek:

CREATE TABLE data_sources (
    source_id      UUID PRIMARY KEY,
    name           VARCHAR(100) NOT NULL,
    description    TEXT,
    source_type    VARCHAR(50) NOT NULL,  -- 'electric', 'water', 'gas', etc.
    unit           VARCHAR(20) NOT NULL,         -- 'kWh', 'gallons', 'm³', etc.
    home_id        UUID REFERENCES homes(home_id) ON DELETE SET NULL,  -- Optional association
    is_public      BOOLEAN DEFAULT FALSE,
    created_at     TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    created_by     UUID REFERENCES users(user_id)
);

ChatGPT:

-- A “meter” or other data‐source
CREATE TABLE data_sources (
  id             SERIAL       PRIMARY KEY,
  owner_id       INTEGER      NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  name           VARCHAR(100) NOT NULL,
  description    TEXT,
  place_name     VARCHAR(200),
  address        TEXT,
  meter_number   VARCHAR(100),
  thumbnail_url  TEXT,
  created_at     TIMESTAMPTZ  NOT NULL DEFAULT NOW()
);

4. Readings or Data Entries

Deepseek:

CREATE TABLE readings (
    reading_id     UUID         PRIMARY KEY,
    source_id      UUID         REFERENCES data_sources(source_id) ON DELETE CASCADE,
    value          DOUBLE PRECISION NOT NULL,
    timestamp      TIMESTAMP    WITH TIME ZONE NOT NULL,
    recorded_at    TIMESTAMP    WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    recorded_by    UUID         REFERENCES users(user_id),
    notes          TEXT,
    quality_flag   VARCHAR(20)  CHECK (quality_flag IN ('good', 'questionable', 'bad', 'estimated')),
    metadata       JSONB        -- For additional context like weather conditions
);

-- Index for efficient time-series queries
CREATE INDEX idx_readings_source_timestamp ON readings(source_id, timestamp DESC);

ChatGPT:

-- Time-stamped readings for each source
CREATE TABLE data_entries (
  id             SERIAL       PRIMARY KEY,
  source_id      INTEGER      NOT NULL REFERENCES data_sources(id) ON DELETE CASCADE,
  recorded_at    TIMESTAMPTZ  NOT NULL,             -- when the reading was taken
  value          NUMERIC      NOT NULL,             -- integer or decimal
  created_by     INTEGER      NOT NULL REFERENCES users(id),
  created_at     TIMESTAMPTZ  NOT NULL DEFAULT NOW(),
  updated_at     TIMESTAMPTZ
);
CREATE INDEX ON data_entries(source_id, recorded_at);