37 lines
1.3 KiB
MySQL
37 lines
1.3 KiB
MySQL
|
-- migrations/0001_init.sql
|
||
|
|
-- gen_random_uuid() is core since PostgreSQL 13. Kept for older images.
|
||
|
|
CREATE EXTENSION IF NOT EXISTS pgcrypto;
|
||
|
|
|
||
|
|
CREATE TABLE IF NOT EXISTS users (
|
||
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
||
|
|
username TEXT NOT NULL,
|
||
|
|
email TEXT,
|
||
|
|
password_hash TEXT NOT NULL,
|
||
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
||
|
|
reset_token_hash TEXT,
|
||
|
|
reset_token_expires TIMESTAMPTZ
|
||
|
|
);
|
||
|
|
|
||
|
|
CREATE UNIQUE INDEX IF NOT EXISTS users_username_key
|
||
|
|
ON users (lower(username));
|
||
|
|
|
||
|
|
CREATE UNIQUE INDEX IF NOT EXISTS users_email_key
|
||
|
|
ON users (lower(email)) WHERE email IS NOT NULL;
|
||
|
|
|
||
|
|
CREATE TABLE IF NOT EXISTS markers (
|
||
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
||
|
|
owner_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
|
||
|
|
name TEXT NOT NULL,
|
||
|
|
description TEXT,
|
||
|
|
lat DOUBLE PRECISION NOT NULL,
|
||
|
|
lon DOUBLE PRECISION NOT NULL,
|
||
|
|
category TEXT,
|
||
|
|
color TEXT,
|
||
|
|
is_shared BOOLEAN NOT NULL DEFAULT false,
|
||
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
||
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
|
||
|
|
);
|
||
|
|
|
||
|
|
CREATE INDEX IF NOT EXISTS markers_owner_idx ON markers (owner_id);
|
||
|
|
CREATE INDEX IF NOT EXISTS markers_shared_idx ON markers (id) WHERE is_shared;
|