`migrations/004_app_users.sql`

40 lines
-- App users + sessions for the admin/user portals.
-- Separate from `professionals` (which is the bar-licensed-attorney directory)
-- and `organizations` (firms). An app_user MAY claim a professional/firm.

CREATE TABLE IF NOT EXISTS app_users (
  id              BIGSERIAL PRIMARY KEY,
  email           TEXT UNIQUE NOT NULL,
  password_hash   TEXT NOT NULL,
  full_name       TEXT,
  role            TEXT NOT NULL DEFAULT 'user' CHECK (role IN ('user', 'admin')),
  plan            TEXT NOT NULL DEFAULT 'free' CHECK (plan IN ('free', 'premium', 'pro')),
  status          TEXT NOT NULL DEFAULT 'active' CHECK (status IN ('active', 'suspended', 'deleted')),
  organization_id BIGINT REFERENCES organizations(id) ON DELETE SET NULL,
  professional_id BIGINT REFERENCES professionals(id) ON DELETE SET NULL,
  bar_number      TEXT,
  created_at      TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at      TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  last_login_at   TIMESTAMPTZ
);

CREATE INDEX IF NOT EXISTS idx_app_users_email  ON app_users(LOWER(email));
CREATE INDEX IF NOT EXISTS idx_app_users_role   ON app_users(role);
CREATE INDEX IF NOT EXISTS idx_app_users_status ON app_users(status);

CREATE TABLE IF NOT EXISTS app_sessions (
  id          TEXT PRIMARY KEY,
  user_id     BIGINT NOT NULL REFERENCES app_users(id) ON DELETE CASCADE,
  expires_at  TIMESTAMPTZ NOT NULL,
  created_at  TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  ip          TEXT,
  user_agent  TEXT
);

CREATE INDEX IF NOT EXISTS idx_app_sessions_user    ON app_sessions(user_id);
CREATE INDEX IF NOT EXISTS idx_app_sessions_expires ON app_sessions(expires_at);

CREATE OR REPLACE TRIGGER trg_app_users_updated
  BEFORE UPDATE ON app_users
  FOR EACH ROW EXECUTE FUNCTION set_updated_at();