IaC / SwiftFlow

Artifact 1bb3cc6e23b0bee3db1e97493ddc083d8b909a252932ae408f8898d5a061ae67:

• File backend/src/user_repo.gleam — part of check-in [1821522f51] at 2026-10-23 05:51:23 on branch trunk — RBAC 权限与多仓数据隔离

  - 新增用户、角色、权限点、用户角色、用户授权仓库、登录会话、审计日志表（008_rbac） - 预设五个角色（超级管理员、仓库主管、制单/调度员、打包复核员、库内作业员），按角色限制登录终端 - 账号密码登录，PBKDF2-SHA256 加盐存储，令牌只存哈希，会话按配置过期，连续输错锁定 - 首个超级管理员从 SWIFTFLOW_ADMIN_USERNAME / SWIFTFLOW_ADMIN_PASSWORD 创建，仅在用户表为空时生效 - 除健康检查与登录外所有接口需登录并校验权限点 - 订单、采购单、库位、库存、批次码按授权仓库过滤；收货、上架、出库只能在当前作业仓库进行 - 用户管理、角色查询、审计日志接口；同步、库位、上架、收货、出库写操作记录审计 (user: pluto size: 14195)

import database.{type Conn, type DbError, IntParam, TextParam}
import gleam/dynamic/decode
import gleam/list
import gleam/option.{type Option, None, Some}
import gleam/result
import gleam/string
import ql361api/json_codec

pub type User {
  User(
    id: String,
    username: String,
    display_name: String,
    enabled: Bool,
    roles: List(String),
    warehouse_ids: List(Int),
    created_at: String,
    updated_at: String,
  )
}

pub type Credential {
  Credential(
    id: String,
    username: String,
    display_name: String,
    password_hash: String,
    enabled: Bool,
    failed_attempts: Int,
    locked_until: String,
  )
}

pub type SessionRow {
  SessionRow(
    user_id: String,
    terminal: String,
    warehouse_id: Int,
    expires_at: String,
  )
}

pub type AuditEntry {
  AuditEntry(
    id: String,
    user_id: String,
    username: String,
    action: String,
    target: String,
    warehouse_id: Int,
    detail: String,
    created_at: String,
  )
}

pub type RoleRow {
  RoleRow(
    code: String,
    name: String,
    terminals: List(String),
    permissions: List(String),
  )
}

pub fn count_users(conn: Conn) -> Result(Int, DbError) {
  count(conn, "SELECT COUNT(*) FROM users", [])
}

pub fn username_taken(conn: Conn, username: String) -> Result(Bool, DbError) {
  use n <- result.try(count(
    conn,
    "SELECT COUNT(*) FROM users WHERE username = ?",
    [TextParam(username)],
  ))
  Ok(n > 0)
}

pub fn find_credential(
  conn: Conn,
  username: String,
) -> Result(Option(Credential), DbError) {
  use rows <- result.try(database.query_on(
    conn,
    "SELECT id, username, display_name, password_hash, enabled,
            failed_attempts, locked_until
     FROM users WHERE username = ?",
    [TextParam(username)],
    {
      use id <- decode.field(0, decode.string)
      use username <- decode.field(1, decode.string)
      use display_name <- decode.field(2, decode.string)
      use password_hash <- decode.field(3, decode.string)
      use enabled <- decode.field(4, json_codec.int())
      use failed_attempts <- decode.field(5, json_codec.int())
      use locked_until <- decode.field(6, decode.string)
      decode.success(Credential(
        id: id,
        username: username,
        display_name: display_name,
        password_hash: password_hash,
        enabled: enabled != 0,
        failed_attempts: failed_attempts,
        locked_until: locked_until,
      ))
    },
  ))
  Ok(first(rows))
}

pub fn password_hash_of(conn: Conn, user_id: String) -> Result(Option(String), DbError) {
  use rows <- result.try(database.query_on(
    conn,
    "SELECT password_hash FROM users WHERE id = ?",
    [TextParam(user_id)],
    {
      use hash <- decode.field(0, decode.string)
      decode.success(hash)
    },
  ))
  Ok(first(rows))
}

pub fn record_login_failure(
  conn: Conn,
  user_id: String,
  attempts: Int,
  locked_until: String,
  now: String,
) -> Result(Nil, DbError) {
  database.exec_on(
    conn,
    "UPDATE users SET failed_attempts = ?, locked_until = ?, updated_at = ? WHERE id = ?",
    [IntParam(attempts), TextParam(locked_until), TextParam(now), TextParam(user_id)],
  )
}

pub fn reset_login_failures(conn: Conn, user_id: String) -> Result(Nil, DbError) {
  database.exec_on(
    conn,
    "UPDATE users SET failed_attempts = 0, locked_until = '' WHERE id = ?",
    [TextParam(user_id)],
  )
}

pub fn insert_user(
  conn: Conn,
  id: String,
  username: String,
  display_name: String,
  password_hash: String,
  now: String,
) -> Result(Nil, DbError) {
  database.exec_on(
    conn,
    "INSERT INTO users (id, username, display_name, password_hash, enabled,
                        failed_attempts, locked_until, created_at, updated_at)
     VALUES (?, ?, ?, ?, 1, 0, '', ?, ?)",
    [
      TextParam(id),
      TextParam(username),
      TextParam(display_name),
      TextParam(password_hash),
      TextParam(now),
      TextParam(now),
    ],
  )
}

pub fn update_profile(
  conn: Conn,
  user_id: String,
  display_name: String,
  enabled: Bool,
  now: String,
) -> Result(Nil, DbError) {
  database.exec_on(
    conn,
    "UPDATE users SET display_name = ?, enabled = ?, updated_at = ? WHERE id = ?",
    [
      TextParam(display_name),
      IntParam(case enabled {
        True -> 1
        False -> 0
      }),
      TextParam(now),
      TextParam(user_id),
    ],
  )
}

pub fn set_password(
  conn: Conn,
  user_id: String,
  password_hash: String,
  now: String,
) -> Result(Nil, DbError) {
  database.exec_on(
    conn,
    "UPDATE users SET password_hash = ?, failed_attempts = 0, locked_until = '',
                      updated_at = ?
     WHERE id = ?",
    [TextParam(password_hash), TextParam(now), TextParam(user_id)],
  )
}

pub fn set_roles(conn: Conn, user_id: String, roles: List(String)) -> Result(Nil, DbError) {
  use _ <- result.try(database.exec_on(
    conn,
    "DELETE FROM user_roles WHERE user_id = ?",
    [TextParam(user_id)],
  ))
  list.try_fold(roles, Nil, fn(_, role) {
    database.exec_on(
      conn,
      "INSERT INTO user_roles (user_id, role_code) VALUES (?, ?)",
      [TextParam(user_id), TextParam(role)],
    )
  })
}

pub fn set_warehouses(
  conn: Conn,
  user_id: String,
  warehouse_ids: List(Int),
) -> Result(Nil, DbError) {
  use _ <- result.try(database.exec_on(
    conn,
    "DELETE FROM user_warehouses WHERE user_id = ?",
    [TextParam(user_id)],
  ))
  list.try_fold(warehouse_ids, Nil, fn(_, warehouse_id) {
    database.exec_on(
      conn,
      "INSERT INTO user_warehouses (user_id, warehouse_id) VALUES (?, ?)",
      [TextParam(user_id), IntParam(warehouse_id)],
    )
  })
}

pub fn get_user(conn: Conn, user_id: String) -> Result(Option(User), DbError) {
  use rows <- result.try(database.query_on(
    conn,
    "SELECT id, username, display_name, enabled, created_at, updated_at
     FROM users WHERE id = ?",
    [TextParam(user_id)],
    user_header_decoder(),
  ))
  case rows {
    [] -> Ok(None)
    [header, ..] -> {
      use user <- result.try(fill_user(conn, header))
      Ok(Some(user))
    }
  }
}

pub fn list_users(conn: Conn) -> Result(List(User), DbError) {
  use rows <- result.try(database.query_on(
    conn,
    "SELECT id, username, display_name, enabled, created_at, updated_at
     FROM users ORDER BY username",
    [],
    user_header_decoder(),
  ))
  list.try_map(rows, fn(header) { fill_user(conn, header) })
}

pub fn user_roles(conn: Conn, user_id: String) -> Result(List(String), DbError) {
  database.query_on(
    conn,
    "SELECT role_code FROM user_roles WHERE user_id = ? ORDER BY role_code",
    [TextParam(user_id)],
    {
      use code <- decode.field(0, decode.string)
      decode.success(code)
    },
  )
}

pub fn user_warehouses(conn: Conn, user_id: String) -> Result(List(Int), DbError) {
  database.query_on(
    conn,
    "SELECT warehouse_id FROM user_warehouses WHERE user_id = ? ORDER BY warehouse_id",
    [TextParam(user_id)],
    {
      use id <- decode.field(0, json_codec.int())
      decode.success(id)
    },
  )
}

pub fn user_permissions(conn: Conn, user_id: String) -> Result(List(String), DbError) {
  database.query_on(
    conn,
    "SELECT DISTINCT rp.permission_code
     FROM user_roles ur
     JOIN role_permissions rp ON rp.role_code = ur.role_code
     WHERE ur.user_id = ?
     ORDER BY rp.permission_code",
    [TextParam(user_id)],
    {
      use code <- decode.field(0, decode.string)
      decode.success(code)
    },
  )
}

pub fn list_roles(conn: Conn) -> Result(List(RoleRow), DbError) {
  use roles <- result.try(database.query_on(
    conn,
    "SELECT code, name, terminals FROM roles ORDER BY code",
    [],
    {
      use code <- decode.field(0, decode.string)
      use name <- decode.field(1, decode.string)
      use terminals <- decode.field(2, decode.string)
      decode.success(#(code, name, terminals))
    },
  ))
  list.try_map(roles, fn(role) {
    let #(code, name, terminals) = role
    use permissions <- result.try(database.query_on(
      conn,
      "SELECT permission_code FROM role_permissions WHERE role_code = ?
       ORDER BY permission_code",
      [TextParam(code)],
      {
        use permission <- decode.field(0, decode.string)
        decode.success(permission)
      },
    ))
    Ok(RoleRow(
      code: code,
      name: name,
      terminals: split_list(terminals),
      permissions: permissions,
    ))
  })
}

pub fn insert_session(
  conn: Conn,
  token_hash: String,
  user_id: String,
  terminal: String,
  warehouse_id: Int,
  now: String,
  expires_at: String,
) -> Result(Nil, DbError) {
  database.exec_on(
    conn,
    "INSERT INTO user_sessions (token_hash, user_id, terminal, warehouse_id,
                                created_at, expires_at, last_seen_at)
     VALUES (?, ?, ?, ?, ?, ?, ?)",
    [
      TextParam(token_hash),
      TextParam(user_id),
      TextParam(terminal),
      IntParam(warehouse_id),
      TextParam(now),
      TextParam(expires_at),
      TextParam(now),
    ],
  )
}

pub fn find_session(
  conn: Conn,
  token_hash: String,
  now: String,
) -> Result(Option(SessionRow), DbError) {
  use rows <- result.try(database.query_on(
    conn,
    "SELECT s.user_id, s.terminal, s.warehouse_id, s.expires_at
     FROM user_sessions s
     JOIN users u ON u.id = s.user_id
     WHERE s.token_hash = ? AND s.expires_at > ? AND u.enabled = 1",
    [TextParam(token_hash), TextParam(now)],
    {
      use user_id <- decode.field(0, decode.string)
      use terminal <- decode.field(1, decode.string)
      use warehouse_id <- decode.field(2, json_codec.int())
      use expires_at <- decode.field(3, decode.string)
      decode.success(SessionRow(
        user_id: user_id,
        terminal: terminal,
        warehouse_id: warehouse_id,
        expires_at: expires_at,
      ))
    },
  ))
  Ok(first(rows))
}

pub fn touch_session(conn: Conn, token_hash: String, now: String) -> Result(Nil, DbError) {
  database.exec_on(
    conn,
    "UPDATE user_sessions SET last_seen_at = ? WHERE token_hash = ?",
    [TextParam(now), TextParam(token_hash)],
  )
}

pub fn set_session_warehouse(
  conn: Conn,
  token_hash: String,
  warehouse_id: Int,
) -> Result(Nil, DbError) {
  database.exec_on(
    conn,
    "UPDATE user_sessions SET warehouse_id = ? WHERE token_hash = ?",
    [IntParam(warehouse_id), TextParam(token_hash)],
  )
}

pub fn delete_session(conn: Conn, token_hash: String) -> Result(Nil, DbError) {
  database.exec_on(
    conn,
    "DELETE FROM user_sessions WHERE token_hash = ?",
    [TextParam(token_hash)],
  )
}

/// 撤销某用户的会话，`keep` 为要保留的当前会话（传空串则全部撤销）。
pub fn delete_user_sessions(
  conn: Conn,
  user_id: String,
  keep: String,
) -> Result(Nil, DbError) {
  database.exec_on(
    conn,
    "DELETE FROM user_sessions WHERE user_id = ? AND token_hash <> ?",
    [TextParam(user_id), TextParam(keep)],
  )
}

pub fn delete_expired_sessions(conn: Conn, now: String) -> Result(Nil, DbError) {
  database.exec_on(
    conn,
    "DELETE FROM user_sessions WHERE expires_at <= ?",
    [TextParam(now)],
  )
}

pub fn insert_audit(conn: Conn, entry: AuditEntry) -> Result(Nil, DbError) {
  database.exec_on(
    conn,
    "INSERT INTO audit_logs (id, user_id, username, action, target, warehouse_id,
                             detail, created_at)
     VALUES (?, ?, ?, ?, ?, ?, ?, ?)",
    [
      TextParam(entry.id),
      TextParam(entry.user_id),
      TextParam(entry.username),
      TextParam(entry.action),
      TextParam(entry.target),
      IntParam(entry.warehouse_id),
      TextParam(entry.detail),
      TextParam(entry.created_at),
    ],
  )
}

pub fn list_audit(
  conn: Conn,
  limit: Int,
  offset: Int,
) -> Result(#(Int, List(AuditEntry)), DbError) {
  use total <- result.try(count(conn, "SELECT COUNT(*) FROM audit_logs", []))
  use rows <- result.try(database.query_on(
    conn,
    "SELECT id, user_id, username, action, target, warehouse_id, detail, created_at
     FROM audit_logs
     ORDER BY created_at DESC, id DESC
     LIMIT ? OFFSET ?",
    [IntParam(limit), IntParam(offset)],
    {
      use id <- decode.field(0, decode.string)
      use user_id <- decode.field(1, decode.string)
      use username <- decode.field(2, decode.string)
      use action <- decode.field(3, decode.string)
      use target <- decode.field(4, decode.string)
      use warehouse_id <- decode.field(5, json_codec.int())
      use detail <- decode.field(6, decode.string)
      use created_at <- decode.field(7, decode.string)
      decode.success(AuditEntry(
        id: id,
        user_id: user_id,
        username: username,
        action: action,
        target: target,
        warehouse_id: warehouse_id,
        detail: detail,
        created_at: created_at,
      ))
    },
  ))
  Ok(#(total, rows))
}

fn fill_user(conn: Conn, header: User) -> Result(User, DbError) {
  use roles <- result.try(user_roles(conn, header.id))
  use warehouses <- result.try(user_warehouses(conn, header.id))
  Ok(User(..header, roles: roles, warehouse_ids: warehouses))
}

fn user_header_decoder() -> decode.Decoder(User) {
  use id <- decode.field(0, decode.string)
  use username <- decode.field(1, decode.string)
  use display_name <- decode.field(2, decode.string)
  use enabled <- decode.field(3, json_codec.int())
  use created_at <- decode.field(4, decode.string)
  use updated_at <- decode.field(5, decode.string)
  decode.success(User(
    id: id,
    username: username,
    display_name: display_name,
    enabled: enabled != 0,
    roles: [],
    warehouse_ids: [],
    created_at: created_at,
    updated_at: updated_at,
  ))
}

fn count(
  conn: Conn,
  sql: String,
  params: List(database.Param),
) -> Result(Int, DbError) {
  use rows <- result.try(database.query_on(conn, sql, params, {
    use n <- decode.field(0, json_codec.int())
    decode.success(n)
  }))
  case rows {
    [n, ..] -> Ok(n)
    [] -> Ok(0)
  }
}

fn first(rows: List(a)) -> Option(a) {
  case rows {
    [row, ..] -> Some(row)
    [] -> None
  }
}

fn split_list(raw: String) -> List(String) {
  string.split(raw, ",")
  |> list.map(string.trim)
  |> list.filter(fn(item) { item != "" })
}