Supabaseセットアップガイド

-- =============================================
-- JizaiLog GPS Database Schema
-- Run this in YOUR Supabase SQL Editor
-- =============================================

-- run_sql() — allows worker to auto-migrate via rpc()
CREATE OR REPLACE FUNCTION run_sql(query text) RETURNS void
LANGUAGE plpgsql SECURITY DEFINER AS $
BEGIN EXECUTE query; END;
$;

REVOKE ALL ON FUNCTION run_sql(text) FROM PUBLIC;
REVOKE ALL ON FUNCTION run_sql(text) FROM anon;
GRANT EXECUTE ON FUNCTION run_sql(text) TO service_role;

-- Schema version tracking
CREATE TABLE schema_version (
    version     INTEGER NOT NULL PRIMARY KEY,
    name        TEXT NOT NULL,
    applied_at  TEXT NOT NULL
);

INSERT INTO schema_version VALUES (1, 'baseline', NOW()::TEXT);

-- Sessions — one row per recorded walk
CREATE TABLE sessions (
    id            UUID PRIMARY KEY,
    user_id       UUID NOT NULL,
    status        TEXT NOT NULL DEFAULT 'stopped',
    started_at    TIMESTAMPTZ NOT NULL,
    ended_at      TIMESTAMPTZ,
    point_count   INTEGER DEFAULT 0,
    discard_count INTEGER DEFAULT 0,
    distance_m    REAL DEFAULT 0,
    duration_s    INTEGER DEFAULT 0,
    created_at    TIMESTAMPTZ DEFAULT now()
);

CREATE INDEX idx_sessions_user ON sessions(user_id);

-- Points — raw GPS coordinates per session
CREATE TABLE points (
    id         UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    session_id UUID NOT NULL REFERENCES sessions(id) ON DELETE CASCADE,
    ts         BIGINT NOT NULL,
    lat        DOUBLE PRECISION NOT NULL,
    lng        DOUBLE PRECISION NOT NULL,
    alt        DOUBLE PRECISION,
    accuracy   REAL,
    speed      REAL,
    heading    REAL,
    UNIQUE(session_id, ts)
);

CREATE INDEX idx_points_session_ts ON points(session_id, ts);

-- Device debug logs
CREATE TABLE device_logs (
    id          BIGSERIAL PRIMARY KEY,
    user_id     UUID NOT NULL,
    device_id   TEXT NOT NULL,
    platform    TEXT NOT NULL,
    ts          BIGINT NOT NULL,
    tag         TEXT NOT NULL,
    message     TEXT NOT NULL,
    app_version TEXT,
    created_at  TIMESTAMPTZ DEFAULT now()
);

CREATE INDEX idx_device_logs_user_ts ON device_logs(user_id, ts DESC);
CREATE INDEX idx_device_logs_device ON device_logs(device_id, ts DESC);

-- Session comments (chat)
CREATE TABLE session_comments (
    id          UUID PRIMARY KEY,
    session_id  UUID NOT NULL REFERENCES sessions(id) ON DELETE CASCADE,
    user_id     UUID NOT NULL,
    content     TEXT,
    image_path  TEXT,
    created_at  BIGINT NOT NULL
);

CREATE INDEX idx_comments_session ON session_comments(session_id, created_at);

-- Comment read tracking
CREATE TABLE comment_reads (
    session_id   UUID NOT NULL REFERENCES sessions(id) ON DELETE CASCADE,
    user_id      UUID NOT NULL,
    last_read_at BIGINT NOT NULL,
    PRIMARY KEY (session_id, user_id)
);

-- Per-session sharing
CREATE TABLE session_access (
    session_id UUID NOT NULL REFERENCES sessions(id) ON DELETE CASCADE,
    user_id    UUID NOT NULL,
    granted_by UUID NOT NULL,
    created_at TIMESTAMPTZ DEFAULT now(),
    PRIMARY KEY (session_id, user_id)
);

CREATE INDEX idx_session_access_user ON session_access(user_id);

-- Enable Row Level Security on all tables
ALTER TABLE schema_version ENABLE ROW LEVEL SECURITY;
ALTER TABLE sessions ENABLE ROW LEVEL SECURITY;
ALTER TABLE points ENABLE ROW LEVEL SECURITY;
ALTER TABLE device_logs ENABLE ROW LEVEL SECURITY;
ALTER TABLE session_comments ENABLE ROW LEVEL SECURITY;
ALTER TABLE comment_reads ENABLE ROW LEVEL SECURITY;
ALTER TABLE session_access ENABLE ROW LEVEL SECURITY;

-- No anon policies or grants needed — Worker uses service_role key
-- which bypasses RLS entirely.