API Reference

Schema

All tables live in one database and are created on demand — the CREATE TABLE IF NOT EXISTS block at the top of api.php runs on every request. New tables appear automatically; column changes to an existing table need a manual ALTER against the live database.

Meeting Cost Timer

meetings

id         INT AUTO_INCREMENT PRIMARY KEY
title      VARCHAR(120)  NOT NULL
cost       DECIMAL(12,2) NOT NULL
seconds    INT           NOT NULL
week_key   DATE          NOT NULL
created_at DATETIME      DEFAULT CURRENT_TIMESTAMP

Track Timer

track_sessions

id         INT AUTO_INCREMENT PRIMARY KEY
name       VARCHAR(120)  NOT NULL
duration   BIGINT        NOT NULL
athletes   LONGTEXT      NOT NULL
created_at DATETIME      DEFAULT CURRENT_TIMESTAMP

Toolshare

tb_users

id            INT AUTO_INCREMENT PRIMARY KEY
username      VARCHAR(60)  NOT NULL UNIQUE
password_hash VARCHAR(255) NOT NULL
display_name  VARCHAR(80)  NOT NULL
email         VARCHAR(120) NOT NULL
token         VARCHAR(64)  DEFAULT NULL
created_at    DATETIME     DEFAULT CURRENT_TIMESTAMP

tb_tools

id          INT AUTO_INCREMENT PRIMARY KEY
owner_id    INT           NOT NULL
name        VARCHAR(120)  NOT NULL
brand       VARCHAR(80)   DEFAULT NULL
category    VARCHAR(60)   DEFAULT NULL
notes       TEXT          DEFAULT NULL
photo_url   VARCHAR(255)  DEFAULT NULL
status      ENUM('available','borrowed') DEFAULT 'available'
created_at  DATETIME      DEFAULT CURRENT_TIMESTAMP
FOREIGN KEY (owner_id) REFERENCES tb_users(id) ON DELETE CASCADE

tb_requests

id           INT AUTO_INCREMENT PRIMARY KEY
tool_id      INT           NOT NULL
requester_id INT           NOT NULL
owner_id     INT           NOT NULL
status       ENUM('pending','approved','declined','returned') DEFAULT 'pending'
message      TEXT          DEFAULT NULL
created_at   DATETIME      DEFAULT CURRENT_TIMESTAMP
updated_at   DATETIME      DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
FOREIGN KEY (tool_id)      REFERENCES tb_tools(id)   ON DELETE CASCADE
FOREIGN KEY (requester_id) REFERENCES tb_users(id)   ON DELETE CASCADE
FOREIGN KEY (owner_id)     REFERENCES tb_users(id)   ON DELETE CASCADE

tb_sessions

token      VARCHAR(64) PRIMARY KEY
user_id    INT         NOT NULL
created_at DATETIME    DEFAULT CURRENT_TIMESTAMP
INDEX idx_user (user_id)
FOREIGN KEY (user_id) REFERENCES tb_users(id) ON DELETE CASCADE

tb_friendships

id         INT AUTO_INCREMENT PRIMARY KEY
user_a     INT NOT NULL
user_b     INT NOT NULL
created_at DATETIME DEFAULT CURRENT_TIMESTAMP
UNIQUE KEY unique_pair (user_a, user_b)
FOREIGN KEY (user_a) REFERENCES tb_users(id) ON DELETE CASCADE
FOREIGN KEY (user_b) REFERENCES tb_users(id) ON DELETE CASCADE

Flight Tracker

ft_users

id            INT AUTO_INCREMENT PRIMARY KEY
username      VARCHAR(60)  NOT NULL UNIQUE
password_hash VARCHAR(255) NOT NULL
display_name  VARCHAR(80)  NOT NULL
token         VARCHAR(64)  DEFAULT NULL
created_at    DATETIME     DEFAULT CURRENT_TIMESTAMP

ft_flights

id             INT AUTO_INCREMENT PRIMARY KEY
user_id        INT          NOT NULL
from_code      VARCHAR(4)   NOT NULL
to_code        VARCHAR(4)   NOT NULL
from_city      VARCHAR(100) NOT NULL
to_city        VARCHAR(100) NOT NULL
flight_date    DATE         NOT NULL
airline        VARCHAR(80)  DEFAULT NULL
seat_class     VARCHAR(30)  DEFAULT NULL
flight_number  VARCHAR(20)  DEFAULT NULL
created_at     DATETIME     DEFAULT CURRENT_TIMESTAMP
FOREIGN KEY (user_id) REFERENCES ft_users(id) ON DELETE CASCADE

Glucose

bg_readings

id         INT AUTO_INCREMENT PRIMARY KEY
reading_at DATETIME     NOT NULL
mgdl       SMALLINT     NOT NULL
trend      VARCHAR(20)  DEFAULT NULL
created_at DATETIME     DEFAULT CURRENT_TIMESTAMP
UNIQUE KEY uniq_reading_at (reading_at)

bg_events

id         INT AUTO_INCREMENT PRIMARY KEY
start_at   DATETIME     NOT NULL
end_at     DATETIME     DEFAULT NULL
tag        VARCHAR(32)  NOT NULL
note       VARCHAR(500) DEFAULT NULL
created_at DATETIME     DEFAULT CURRENT_TIMESTAMP
updated_at DATETIME     DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
INDEX idx_start (start_at)

Confidence Pool

cp_weeks

id                INT AUTO_INCREMENT PRIMARY KEY
season            INT          NOT NULL
week              INT          NOT NULL
push_rule         ENUM('award','void') DEFAULT 'void'
scores_fetched_at DATETIME     DEFAULT NULL
created_at        DATETIME     DEFAULT CURRENT_TIMESTAMP
UNIQUE KEY uk_season_week (season, week)

cp_games

id         INT AUTO_INCREMENT PRIMARY KEY
week_id    INT          NOT NULL
sort_order INT          NOT NULL
away_team  VARCHAR(8)   NOT NULL
home_team  VARCHAR(8)   NOT NULL
favorite   ENUM('home','away') NOT NULL
spread     DECIMAL(4,1) NOT NULL
espn_id    VARCHAR(24)  DEFAULT NULL
away_score INT          DEFAULT 0
home_score INT          DEFAULT 0
state      ENUM('pre','in','post') DEFAULT 'pre'
detail     VARCHAR(60)  DEFAULT NULL
kickoff    DATETIME     DEFAULT NULL
UNIQUE KEY uk_week_order (week_id, sort_order)
FOREIGN KEY (week_id) REFERENCES cp_weeks(id) ON DELETE CASCADE

cp_entries

id          INT AUTO_INCREMENT PRIMARY KEY
week_id     INT          NOT NULL
player_name VARCHAR(60)  NOT NULL
created_at  DATETIME     DEFAULT CURRENT_TIMESTAMP
updated_at  DATETIME     DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
UNIQUE KEY uk_week_player (week_id, player_name)
FOREIGN KEY (week_id) REFERENCES cp_weeks(id) ON DELETE CASCADE

cp_picks

id         INT AUTO_INCREMENT PRIMARY KEY
entry_id   INT NOT NULL
game_id    INT NOT NULL
pick       ENUM('home','away') NOT NULL
confidence INT NOT NULL
UNIQUE KEY uk_entry_game (entry_id, game_id)
UNIQUE KEY uk_entry_conf (entry_id, confidence)
FOREIGN KEY (entry_id) REFERENCES cp_entries(id) ON DELETE CASCADE
FOREIGN KEY (game_id)  REFERENCES cp_games(id)   ON DELETE CASCADE

cp_players

id          INT AUTO_INCREMENT PRIMARY KEY
phone       VARCHAR(20)  NOT NULL UNIQUE
player_name VARCHAR(60)  NOT NULL
opted_out   TINYINT(1)   DEFAULT 0
created_at  DATETIME     DEFAULT CURRENT_TIMESTAMP
updated_at  DATETIME     DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP

cp_pending

id          INT AUTO_INCREMENT PRIMARY KEY
token       VARCHAR(40)  NOT NULL UNIQUE
week_id     INT          NOT NULL
player_name VARCHAR(60)  NOT NULL
phone       VARCHAR(20)  DEFAULT NULL
rows_json   LONGTEXT     NOT NULL
note        VARCHAR(255) DEFAULT NULL
claimed_at  DATETIME     DEFAULT NULL
created_at  DATETIME     DEFAULT CURRENT_TIMESTAMP
INDEX idx_week (week_id)
FOREIGN KEY (week_id) REFERENCES cp_weeks(id) ON DELETE CASCADE

WildcatsXC

xc_meets

id         INT AUTO_INCREMENT PRIMARY KEY
name       VARCHAR(160) NOT NULL
meet_date  DATE         NOT NULL
location   VARCHAR(160) DEFAULT NULL
created_at DATETIME     DEFAULT CURRENT_TIMESTAMP
INDEX idx_date (meet_date)

xc_athletes

id         INT AUTO_INCREMENT PRIMARY KEY
name       VARCHAR(80)  NOT NULL
school     VARCHAR(120) NOT NULL
match_key  VARCHAR(210) NOT NULL
created_at DATETIME     DEFAULT CURRENT_TIMESTAMP
UNIQUE KEY uniq_athlete (match_key)

xc_results

id         INT AUTO_INCREMENT PRIMARY KEY
meet_id    INT          NOT NULL
athlete_id INT          NOT NULL
race       VARCHAR(80)  NOT NULL
distance_m SMALLINT     DEFAULT NULL
place      SMALLINT     DEFAULT NULL
grade      TINYINT      DEFAULT NULL
time_ms    INT          NOT NULL
UNIQUE KEY uniq_run (meet_id, athlete_id)
INDEX idx_athlete (athlete_id)
FOREIGN KEY (meet_id)    REFERENCES xc_meets(id)    ON DELETE CASCADE
FOREIGN KEY (athlete_id) REFERENCES xc_athletes(id) ON DELETE CASCADE

xc_pin_attempts

id         INT AUTO_INCREMENT PRIMARY KEY
ip         VARCHAR(45) NOT NULL
created_at DATETIME    DEFAULT CURRENT_TIMESTAMP
KEY idx_ip (ip, created_at)

Daily Tasks

dt_scores

id            INT AUTO_INCREMENT PRIMARY KEY
name          VARCHAR(60)  NOT NULL
task_count    INT          NOT NULL
total_seconds INT          NOT NULL
week_key      DATE         NOT NULL
created_at    DATETIME     DEFAULT CURRENT_TIMESTAMP
INDEX idx_week (week_key, task_count, total_seconds)

dt_tasks

user_id     INT          NOT NULL
date_key    DATE         NOT NULL
tasks_json  LONGTEXT     NOT NULL
next_id     INT          NOT NULL DEFAULT 1
updated_at  DATETIME     DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
PRIMARY KEY (user_id, date_key)
FOREIGN KEY (user_id) REFERENCES tb_users(id) ON DELETE CASCADE

Face Breaker

fb_scores

id         INT AUTO_INCREMENT PRIMARY KEY
name       VARCHAR(32)  NOT NULL
score      INT          NOT NULL
week_start DATE         NOT NULL
created_at TIMESTAMP    DEFAULT CURRENT_TIMESTAMP
INDEX idx_week_score (week_start, score)

Reaction Test

reaction_scores

id         INT AUTO_INCREMENT PRIMARY KEY
name       VARCHAR(60)  NOT NULL
avg_ms     INT          NOT NULL
week_key   DATE         NOT NULL
created_at DATETIME     DEFAULT CURRENT_TIMESTAMP
INDEX idx_week (week_key, avg_ms)

Update Notifications

subscribers

id            INT AUTO_INCREMENT PRIMARY KEY
contact_type  ENUM('email','phone') NOT NULL
contact_value VARCHAR(120) NOT NULL
unsub_token   VARCHAR(64)  NOT NULL
created_at    DATETIME     DEFAULT CURRENT_TIMESTAMP
UNIQUE KEY uniq_contact (contact_type, contact_value)

Analytics

an_hits

id        BIGINT AUTO_INCREMENT PRIMARY KEY
ts        DATETIME     NOT NULL
page      VARCHAR(60)  NOT NULL
vid       CHAR(16)     NOT NULL
referrer  VARCHAR(100) DEFAULT NULL
device    VARCHAR(8)   DEFAULT NULL
browser   VARCHAR(16)  DEFAULT NULL
os        VARCHAR(16)  DEFAULT NULL
installed TINYINT(1)   NOT NULL DEFAULT 0
theme     VARCHAR(5)   DEFAULT NULL
lang      VARCHAR(15)  DEFAULT NULL
city      VARCHAR(80)  DEFAULT NULL
region    VARCHAR(80)  DEFAULT NULL
country   CHAR(2)      DEFAULT NULL
lat       DECIMAL(5,2) DEFAULT NULL
lon       DECIMAL(5,2) DEFAULT NULL
KEY idx_ts (ts)
KEY idx_vid (vid, ts)

an_daily

day      DATE        NOT NULL
page     VARCHAR(60) NOT NULL
views    INT         NOT NULL
visitors INT         NOT NULL
PRIMARY KEY (day, page)

an_salt

day  DATE     NOT NULL PRIMARY KEY
salt CHAR(64) NOT NULL

an_api

day      DATE        NOT NULL
method   VARCHAR(7)  NOT NULL
action   VARCHAR(40) NOT NULL
calls    INT         NOT NULL DEFAULT 0
errors   INT         NOT NULL DEFAULT 0
total_ms BIGINT      NOT NULL DEFAULT 0
max_ms   INT         NOT NULL DEFAULT 0
PRIMARY KEY (day, method, action)

Other

subscribe_attempts

id            INT AUTO_INCREMENT PRIMARY KEY
ip            VARCHAR(45)  NOT NULL
contact_value VARCHAR(120) NOT NULL
texted        TINYINT(1)   NOT NULL DEFAULT 0
created_at    DATETIME     DEFAULT CURRENT_TIMESTAMP
KEY idx_ip (ip, created_at)
KEY idx_contact (contact_value, created_at)