107 lines
3.1 KiB
SQL
107 lines
3.1 KiB
SQL
CREATE EXTENSION IF NOT EXISTS postgis;
|
|
|
|
CREATE TABLE athletes (
|
|
id BIGSERIAL PRIMARY KEY,
|
|
intervals_athlete_id TEXT NOT NULL UNIQUE,
|
|
display_name TEXT NOT NULL,
|
|
access_token TEXT NOT NULL,
|
|
scopes TEXT NOT NULL DEFAULT '',
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
|
|
);
|
|
|
|
CREATE TABLE oauth_states (
|
|
state TEXT PRIMARY KEY,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT now()
|
|
);
|
|
|
|
CREATE TABLE sessions (
|
|
token_hash BYTEA PRIMARY KEY,
|
|
athlete_id BIGINT NOT NULL
|
|
REFERENCES athletes(id)
|
|
ON DELETE CASCADE,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
last_seen_at TIMESTAMPTZ NOT NULL DEFAULT now()
|
|
);
|
|
|
|
CREATE INDEX sessions_athlete_idx ON sessions (athlete_id);
|
|
|
|
CREATE TABLE activities (
|
|
id BIGSERIAL PRIMARY KEY,
|
|
athlete_id BIGINT NOT NULL
|
|
REFERENCES athletes(id)
|
|
ON DELETE CASCADE,
|
|
intervals_activity_id TEXT NOT NULL,
|
|
start_time TIMESTAMPTZ,
|
|
activity_json JSONB,
|
|
processed_at TIMESTAMPTZ,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
UNIQUE (athlete_id, intervals_activity_id)
|
|
);
|
|
|
|
CREATE INDEX activities_start_time_idx ON activities(start_time);
|
|
CREATE INDEX activities_athlete_idx ON activities(athlete_id);
|
|
|
|
CREATE TABLE county_boundaries (
|
|
id BIGSERIAL PRIMARY KEY,
|
|
name TEXT NOT NULL,
|
|
geometry geometry(MultiPolygon, 4326) NOT NULL
|
|
);
|
|
|
|
CREATE INDEX county_boundaries_geometry_idx
|
|
ON county_boundaries USING GIST (geometry);
|
|
|
|
CREATE TABLE county_crossings (
|
|
id BIGSERIAL PRIMARY KEY,
|
|
activity_id BIGINT NOT NULL
|
|
REFERENCES activities(id)
|
|
ON DELETE CASCADE,
|
|
track_index BIGINT NOT NULL,
|
|
crossing_time TIMESTAMPTZ(3) NOT NULL,
|
|
location geometry(Point, 4326) NOT NULL,
|
|
from_county_id BIGINT NOT NULL
|
|
REFERENCES county_boundaries(id),
|
|
to_county_id BIGINT NOT NULL
|
|
REFERENCES county_boundaries(id),
|
|
intervals_interval JSONB,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT now()
|
|
);
|
|
|
|
CREATE INDEX county_crossings_time_idx
|
|
ON county_crossings(crossing_time);
|
|
|
|
CREATE INDEX county_crossings_location_idx
|
|
ON county_crossings USING GIST (location);
|
|
|
|
CREATE INDEX county_crossings_direction_time_idx
|
|
ON county_crossings(from_county_id, to_county_id, crossing_time);
|
|
|
|
CREATE INDEX county_crossings_activity_idx
|
|
ON county_crossings(activity_id);
|
|
|
|
CREATE TABLE leaderboard_groups (
|
|
id BIGSERIAL PRIMARY KEY,
|
|
owner_athlete_id BIGINT NOT NULL
|
|
REFERENCES athletes(id)
|
|
ON DELETE CASCADE,
|
|
name TEXT NOT NULL,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
UNIQUE (owner_athlete_id, name)
|
|
);
|
|
|
|
CREATE INDEX leaderboard_groups_owner_idx
|
|
ON leaderboard_groups(owner_athlete_id);
|
|
|
|
CREATE TABLE leaderboard_group_members (
|
|
group_id BIGINT NOT NULL
|
|
REFERENCES leaderboard_groups(id)
|
|
ON DELETE CASCADE,
|
|
athlete_id BIGINT NOT NULL
|
|
REFERENCES athletes(id)
|
|
ON DELETE CASCADE,
|
|
PRIMARY KEY (group_id, athlete_id)
|
|
);
|
|
|
|
CREATE INDEX leaderboard_group_members_athlete_idx
|
|
ON leaderboard_group_members(athlete_id);
|