Blame for backend/postgres-init/00-auth-schema.sql

CREATE SCHEMA IF NOT EXISTS auth;

CREATE TABLE IF NOT EXISTS auth.users (
  id text PRIMARY KEY,
  email text NOT NULL,
  password_hash text,
  is_operator boolean NOT NULL DEFAULT false,
  disabled boolean NOT NULL DEFAULT false,
  created_at timestamptz NOT NULL DEFAULT now(),
  updated_at timestamptz NOT NULL DEFAULT now()
);

CREATE UNIQUE INDEX IF NOT EXISTS auth_users_email_lower_idx
  ON auth.users (lower(email));

CREATE TABLE IF NOT EXISTS auth.sessions (
  token_hash text PRIMARY KEY,
  user_id text NOT NULL REFERENCES auth.users(id) ON DELETE CASCADE,
  created_at timestamptz NOT NULL DEFAULT now(),
  expires_at timestamptz NOT NULL,
  user_agent text,
  ip text
);

CREATE INDEX IF NOT EXISTS auth_sessions_user_id_idx
  ON auth.sessions (user_id);

CREATE INDEX IF NOT EXISTS auth_sessions_expires_at_idx
  ON auth.sessions (expires_at);

-- 000_one_shot_schema.sql references auth.uid() in RLS policies.
-- The backend bypasses RLS with its Postgres connection, so a no-op function is fine.
CREATE OR REPLACE FUNCTION auth.uid()
RETURNS text
LANGUAGE sql
STABLE
AS $$
  SELECT NULL::text;
$$;