Max Mayfield
5ee869b9d8
- Login: email + password lookup, scrypt verify, JWT token - Signup: create tenant + owner user in transaction, slug generation - API key: dd0c_ prefix, SHA-256 hash (not bcrypt — faster for API key lookups), prefix index - Scrypt over bcrypt: zero native deps, Node.js built-in crypto - Auth routes skip JWT middleware (login/signup are public) - 002_auth.sql: users + api_keys tables with RLS, copied to all products - Synced auth middleware to P3/P4/P5/P6
36 lines
1.4 KiB
SQL
36 lines
1.4 KiB
SQL
-- dd0c shared auth tables — append to each product's migration
|
|
-- Run after 001_init.sql
|
|
|
|
-- Users
|
|
CREATE TABLE IF NOT EXISTS users (
|
|
id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
|
|
tenant_id UUID NOT NULL REFERENCES tenants(id) ON DELETE CASCADE,
|
|
email TEXT NOT NULL UNIQUE,
|
|
password_hash TEXT NOT NULL,
|
|
role TEXT NOT NULL DEFAULT 'viewer' CHECK (role IN ('owner', 'admin', 'member', 'viewer')),
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT now()
|
|
);
|
|
CREATE INDEX IF NOT EXISTS idx_users_email ON users(email);
|
|
CREATE INDEX IF NOT EXISTS idx_users_tenant ON users(tenant_id);
|
|
|
|
-- API Keys
|
|
CREATE TABLE IF NOT EXISTS api_keys (
|
|
id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
|
|
tenant_id UUID NOT NULL REFERENCES tenants(id) ON DELETE CASCADE,
|
|
user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
|
|
key_prefix TEXT NOT NULL,
|
|
key_hash TEXT NOT NULL,
|
|
revoked BOOLEAN NOT NULL DEFAULT false,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT now()
|
|
);
|
|
CREATE INDEX IF NOT EXISTS idx_api_keys_prefix ON api_keys(key_prefix) WHERE revoked = false;
|
|
|
|
-- RLS
|
|
ALTER TABLE users ENABLE ROW LEVEL SECURITY;
|
|
ALTER TABLE api_keys ENABLE ROW LEVEL SECURITY;
|
|
|
|
CREATE POLICY tenant_iso_users ON users
|
|
USING (tenant_id::text = current_setting('app.tenant_id', true));
|
|
CREATE POLICY tenant_iso_api_keys ON api_keys
|
|
USING (tenant_id::text = current_setting('app.tenant_id', true));
|