mini-prog/backend/migrations/0013_ops_console.sql

BEGIN;

ALTER TABLE auth_sms_codes
  DROP CONSTRAINT IF EXISTS auth_sms_codes_client_type_check;

ALTER TABLE auth_sms_codes
  ADD CONSTRAINT auth_sms_codes_client_type_check
  CHECK (client_type IN ('app', 'wechat', 'ops'));

CREATE TABLE ops_roles (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  role_code TEXT NOT NULL UNIQUE,
  display_name TEXT NOT NULL,
  role_rank INT NOT NULL,
  permissions_jsonb JSONB NOT NULL DEFAULT '{}'::jsonb,
  status TEXT NOT NULL DEFAULT 'active' CHECK (status IN ('active', 'disabled', 'archived')),
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE TRIGGER ops_roles_set_updated_at
BEFORE UPDATE ON ops_roles
FOR EACH ROW EXECUTE FUNCTION set_updated_at();

CREATE TABLE ops_users (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  ops_user_public_id TEXT NOT NULL UNIQUE,
  country_code TEXT NOT NULL,
  mobile TEXT NOT NULL,
  display_name TEXT NOT NULL,
  status TEXT NOT NULL DEFAULT 'active' CHECK (status IN ('active', 'disabled', 'deleted')),
  last_login_at TIMESTAMPTZ,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  UNIQUE (country_code, mobile)
);

CREATE INDEX ops_users_mobile_idx ON ops_users(country_code, mobile);

CREATE TRIGGER ops_users_set_updated_at
BEFORE UPDATE ON ops_users
FOR EACH ROW EXECUTE FUNCTION set_updated_at();

CREATE TABLE ops_user_roles (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  ops_user_id UUID NOT NULL REFERENCES ops_users(id) ON DELETE CASCADE,
  ops_role_id UUID NOT NULL REFERENCES ops_roles(id) ON DELETE CASCADE,
  status TEXT NOT NULL DEFAULT 'active' CHECK (status IN ('active', 'disabled')),
  assigned_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  assigned_by_ops_user_id UUID REFERENCES ops_users(id) ON DELETE SET NULL,
  UNIQUE (ops_user_id, ops_role_id)
);

CREATE INDEX ops_user_roles_user_idx ON ops_user_roles(ops_user_id);
CREATE INDEX ops_user_roles_role_idx ON ops_user_roles(ops_role_id);

CREATE TABLE ops_refresh_tokens (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  ops_user_id UUID NOT NULL REFERENCES ops_users(id) ON DELETE CASCADE,
  device_key TEXT,
  token_hash TEXT NOT NULL UNIQUE,
  issued_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  expires_at TIMESTAMPTZ NOT NULL,
  revoked_at TIMESTAMPTZ,
  replaced_by_token_id UUID REFERENCES ops_refresh_tokens(id) ON DELETE SET NULL
);

CREATE INDEX ops_refresh_tokens_user_idx ON ops_refresh_tokens(ops_user_id);
CREATE INDEX ops_refresh_tokens_expires_idx ON ops_refresh_tokens(expires_at);

INSERT INTO ops_roles (role_code, display_name, role_rank, permissions_jsonb)
VALUES
  ('owner', '所有者', 100, '{"scope":"all"}'::jsonb),
  ('admin', '管理员', 80, '{"scope":"ops_admin"}'::jsonb),
  ('operator', '运维专员', 50, '{"scope":"ops_operator"}'::jsonb),
  ('viewer', '只读观察员', 10, '{"scope":"ops_viewer"}'::jsonb)
ON CONFLICT (role_code) DO NOTHING;

COMMIT;