39 lines
1.4 KiB
SQL
39 lines
1.4 KiB
SQL
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
|
|
|
|
CREATE TABLE users (
|
|
id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
|
|
email VARCHAR(255) UNIQUE NOT NULL,
|
|
password_hash VARCHAR(255),
|
|
google_id VARCHAR(255) UNIQUE,
|
|
credits BIGINT NOT NULL DEFAULT 30,
|
|
tier VARCHAR(50) NOT NULL DEFAULT 'free',
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
|
|
);
|
|
|
|
CREATE TABLE api_keys (
|
|
id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
|
|
user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
|
|
key_hash VARCHAR(255) UNIQUE NOT NULL,
|
|
name VARCHAR(255) NOT NULL DEFAULT 'Default',
|
|
last_used_at TIMESTAMPTZ,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
|
|
);
|
|
|
|
CREATE TABLE usage_logs (
|
|
id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
|
|
user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
|
|
api_key_id UUID NOT NULL REFERENCES api_keys(id) ON DELETE CASCADE,
|
|
endpoint VARCHAR(100) NOT NULL,
|
|
url TEXT NOT NULL,
|
|
status VARCHAR(50) NOT NULL,
|
|
credits_used BIGINT NOT NULL DEFAULT 1,
|
|
duration_ms BIGINT NOT NULL DEFAULT 0,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
|
|
);
|
|
|
|
CREATE INDEX idx_api_keys_user_id ON api_keys(user_id);
|
|
CREATE INDEX idx_api_keys_key_hash ON api_keys(key_hash);
|
|
CREATE INDEX idx_usage_logs_user_id ON usage_logs(user_id);
|
|
CREATE INDEX idx_usage_logs_created_at ON usage_logs(created_at);
|