mini-prog/backend/migrations/0001_init.sql

BEGIN;

CREATE EXTENSION IF NOT EXISTS pgcrypto;

CREATE OR REPLACE FUNCTION set_updated_at()
RETURNS TRIGGER AS $$
BEGIN
  NEW.updated_at = NOW();
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TABLE tenants (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  tenant_code TEXT NOT NULL UNIQUE,
  name TEXT NOT NULL,
  status TEXT NOT NULL DEFAULT 'active' CHECK (status IN ('draft', 'active', 'disabled', 'archived')),
  theme_jsonb JSONB NOT NULL DEFAULT '{}'::jsonb,
  settings_jsonb JSONB NOT NULL DEFAULT '{}'::jsonb,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE TRIGGER tenants_set_updated_at
BEFORE UPDATE ON tenants
FOR EACH ROW EXECUTE FUNCTION set_updated_at();

CREATE TABLE entry_channels (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  tenant_id UUID NOT NULL REFERENCES tenants(id) ON DELETE CASCADE,
  channel_code TEXT NOT NULL,
  channel_type TEXT NOT NULL CHECK (channel_type IN ('app', 'wechat_mini', 'wechat_oa', 'h5', 'qr')),
  platform_app_id TEXT,
  display_name TEXT NOT NULL,
  status TEXT NOT NULL DEFAULT 'active' CHECK (status IN ('draft', 'active', 'disabled', 'archived')),
  is_default BOOLEAN NOT NULL DEFAULT FALSE,
  config_jsonb JSONB NOT NULL DEFAULT '{}'::jsonb,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  UNIQUE (tenant_id, channel_code)
);

CREATE INDEX entry_channels_tenant_id_idx ON entry_channels(tenant_id);
CREATE INDEX entry_channels_platform_app_id_idx ON entry_channels(platform_app_id);

CREATE TRIGGER entry_channels_set_updated_at
BEFORE UPDATE ON entry_channels
FOR EACH ROW EXECUTE FUNCTION set_updated_at();

CREATE TABLE users (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_public_id TEXT NOT NULL UNIQUE,
  default_tenant_id UUID REFERENCES tenants(id) ON DELETE SET NULL,
  status TEXT NOT NULL DEFAULT 'active' CHECK (status IN ('active', 'disabled', 'deleted')),
  nickname TEXT,
  avatar_url TEXT,
  last_login_at TIMESTAMPTZ,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX users_default_tenant_id_idx ON users(default_tenant_id);

CREATE TRIGGER users_set_updated_at
BEFORE UPDATE ON users
FOR EACH ROW EXECUTE FUNCTION set_updated_at();

CREATE TABLE login_identities (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  identity_type TEXT NOT NULL CHECK (identity_type IN ('mobile', 'wechat_mini_openid', 'wechat_oa_openid', 'wechat_unionid')),
  provider TEXT NOT NULL,
  provider_subject TEXT NOT NULL,
  country_code TEXT,
  mobile TEXT,
  status TEXT NOT NULL DEFAULT 'active' CHECK (status IN ('active', 'disabled', 'deleted')),
  profile_jsonb JSONB NOT NULL DEFAULT '{}'::jsonb,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  UNIQUE (provider, provider_subject)
);

CREATE INDEX login_identities_user_id_idx ON login_identities(user_id);
CREATE INDEX login_identities_mobile_idx ON login_identities(country_code, mobile);

CREATE TRIGGER login_identities_set_updated_at
BEFORE UPDATE ON login_identities
FOR EACH ROW EXECUTE FUNCTION set_updated_at();

CREATE TABLE auth_sms_codes (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  scene TEXT NOT NULL,
  country_code TEXT NOT NULL,
  mobile TEXT NOT NULL,
  client_type TEXT NOT NULL CHECK (client_type IN ('app', 'wechat')),
  device_key TEXT NOT NULL,
  code_hash TEXT NOT NULL,
  provider_payload_jsonb JSONB NOT NULL DEFAULT '{}'::jsonb,
  expires_at TIMESTAMPTZ NOT NULL,
  cooldown_until TIMESTAMPTZ NOT NULL,
  consumed_at TIMESTAMPTZ,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX auth_sms_codes_lookup_idx
ON auth_sms_codes(country_code, mobile, client_type, scene, created_at DESC);

CREATE TABLE auth_refresh_tokens (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  client_type TEXT NOT NULL CHECK (client_type IN ('app', 'wechat')),
  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 auth_refresh_tokens(id) ON DELETE SET NULL
);

CREATE INDEX auth_refresh_tokens_user_id_idx ON auth_refresh_tokens(user_id);
CREATE INDEX auth_refresh_tokens_expires_at_idx ON auth_refresh_tokens(expires_at);

COMMIT;