51 lines
1.9 KiB
SQL
51 lines
1.9 KiB
SQL
-- Space tables for public demo spaces with face search
|
|
|
|
CREATE TABLE IF NOT EXISTS spaces (
|
|
id TEXT NOT NULL PRIMARY KEY,
|
|
slug TEXT NOT NULL UNIQUE,
|
|
name TEXT NOT NULL,
|
|
description TEXT,
|
|
is_public BOOLEAN NOT NULL DEFAULT FALSE,
|
|
created_at TEXT NOT NULL,
|
|
updated_at TEXT NOT NULL
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_spaces_slug ON spaces(slug);
|
|
CREATE INDEX IF NOT EXISTS idx_spaces_is_public ON spaces(is_public);
|
|
|
|
CREATE TABLE IF NOT EXISTS photos (
|
|
id TEXT NOT NULL PRIMARY KEY,
|
|
space_id TEXT NOT NULL REFERENCES spaces(id) ON DELETE CASCADE,
|
|
url TEXT NOT NULL,
|
|
thumbnail_url TEXT,
|
|
face_count INTEGER NOT NULL DEFAULT 0,
|
|
metadata TEXT,
|
|
created_at TEXT NOT NULL
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_photos_space_id ON photos(space_id);
|
|
CREATE INDEX IF NOT EXISTS idx_photos_created_at ON photos(created_at);
|
|
|
|
CREATE TABLE IF NOT EXISTS face_embeddings (
|
|
id TEXT NOT NULL PRIMARY KEY,
|
|
photo_id TEXT NOT NULL REFERENCES photos(id) ON DELETE CASCADE,
|
|
space_id TEXT NOT NULL REFERENCES spaces(id) ON DELETE CASCADE,
|
|
embedding BLOB NOT NULL, -- JSON array of float32
|
|
bounding_box TEXT NOT NULL, -- JSON bounding box
|
|
created_at TEXT NOT NULL
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_face_embeddings_space_id ON face_embeddings(space_id);
|
|
CREATE INDEX IF NOT EXISTS idx_face_embeddings_photo_id ON face_embeddings(photo_id);
|
|
|
|
CREATE TABLE IF NOT EXISTS rate_limits (
|
|
id TEXT NOT NULL PRIMARY KEY,
|
|
ip_address TEXT NOT NULL,
|
|
space_id TEXT NOT NULL REFERENCES spaces(id) ON DELETE CASCADE,
|
|
request_count INTEGER NOT NULL DEFAULT 1,
|
|
window_start TEXT NOT NULL,
|
|
created_at TEXT NOT NULL
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_rate_limits_ip_space ON rate_limits(ip_address, space_id);
|
|
CREATE INDEX IF NOT EXISTS idx_rate_limits_window_start ON rate_limits(window_start); |