312 lines
9.9 KiB
Markdown
312 lines
9.9 KiB
Markdown
|
---
|
||
|
|
title: prop-acc · billing · 场景 - activitylog 审计追溯
|
||
|
|
aliases:
|
||
|
|
- activitylog 审计
|
||
|
|
- 操作日志查询
|
||
|
|
- audit-activitylog-trace
|
||
|
|
- 场景-activitylog 追溯
|
||
|
|
tags:
|
||
|
|
- 场景
|
||
|
|
- prop-acc
|
||
|
|
- 账单
|
||
|
|
- 审计
|
||
|
|
- 合规
|
||
|
|
audience:
|
||
|
|
- 业务人员
|
||
|
|
- 财务
|
||
|
|
- 审计师
|
||
|
|
- 法务
|
||
|
|
status: 已发布
|
||
|
|
sub_feature: billing
|
||
|
|
last_review: 2026-05-26
|
||
|
|
code_version: 2026-05-22
|
||
|
|
---
|
||
|
|
|
||
|
|
# 场景:activitylog 审计追溯
|
||
|
|
|
||
|
|
billing 模块**首次启用** `spatie/laravel-activitylog`(prop-acc 其他模块仅用 meta JSON)。所有关键操作(作废 / 批删 / 挂起 / 恢复 / 收款 / 创建)记 activitylog,审计 / 内审 / 法务可**精准追溯**:谁 / 什么时候 / 在哪 / 改了什么。
|
||
|
|
|
||
|
|
## 典型情境
|
||
|
|
|
||
|
|
> [!example] 真实情境
|
||
|
|
> 5 月 20 日,内审师审计 5 月嘉禾花园账单数据,发现:
|
||
|
|
>
|
||
|
|
> - 5 月 15 日有一次**批量删除 92 张账单**(`bulk_deleted`)
|
||
|
|
> - 同日有 5 条 **bill voided**(账单作废)
|
||
|
|
> - 还有几张账单的 status 从 Suspended → Unpaid(恢复)
|
||
|
|
>
|
||
|
|
> 审计师要查清:
|
||
|
|
> - 谁操作的?
|
||
|
|
> - 操作的原因?
|
||
|
|
> - 影响了哪些具体账单?
|
||
|
|
> - 是否合规?
|
||
|
|
|
||
|
|
## 业务人员 / 审计师视角
|
||
|
|
|
||
|
|
### 第 1 步:确定查询维度
|
||
|
|
|
||
|
|
- **谁操作**:`causer_id`(操作员)
|
||
|
|
- **什么时候**:`created_at` 范围
|
||
|
|
- **操作类型**:`event`(created / voided / bulk_deleted / suspended / resumed / collected / split)
|
||
|
|
- **针对哪个对象**:`subject_type` + `subject_id`(单条操作)/ properties.affected_bill_nos(批量)
|
||
|
|
- **操作详情**:`properties` JSON 字段
|
||
|
|
|
||
|
|
### 第 2 步:运行 SQL 查询
|
||
|
|
|
||
|
|
#### 查询 1:某员工某月所有操作
|
||
|
|
|
||
|
|
```sql
|
||
|
|
SELECT
|
||
|
|
id,
|
||
|
|
event,
|
||
|
|
subject_type,
|
||
|
|
subject_id,
|
||
|
|
properties,
|
||
|
|
created_at
|
||
|
|
FROM activity_log
|
||
|
|
WHERE causer_id = ? -- 王主管 ID
|
||
|
|
AND created_at BETWEEN '2026-05-01' AND '2026-05-31 23:59:59'
|
||
|
|
ORDER BY created_at DESC;
|
||
|
|
```
|
||
|
|
|
||
|
|
返回:
|
||
|
|
|
||
|
|
| id | event | subject | properties (节选) | created_at |
|
||
|
|
|---|---|---|---|---|
|
||
|
|
| 1023 | bulk_deleted | (null) | mode=DeleteAndVoid, reason="...", deleted=92, voided=5 | 2026-05-15 14:32 |
|
||
|
|
| 1022 | voided | Bill #500 | reason="业务调整", from_status=Partial | 2026-05-15 14:31 |
|
||
|
|
| 1021 | collected | Bill #321 | amount=800, channel=微信 | 2026-05-10 11:23 |
|
||
|
|
| ... | ... | ... | ... | ... |
|
||
|
|
|
||
|
|
#### 查询 2:某 Bill 的所有操作历史
|
||
|
|
|
||
|
|
```sql
|
||
|
|
SELECT
|
||
|
|
event,
|
||
|
|
causer_id,
|
||
|
|
properties,
|
||
|
|
created_at
|
||
|
|
FROM activity_log
|
||
|
|
WHERE subject_type = 'App\\Models\\Bill'
|
||
|
|
AND subject_id = ? -- 具体 Bill ID
|
||
|
|
ORDER BY created_at ASC;
|
||
|
|
```
|
||
|
|
|
||
|
|
返回该 Bill 的**全生命周期**:created → collected → voided 等。
|
||
|
|
|
||
|
|
#### 查询 3:批量删除的详情
|
||
|
|
|
||
|
|
```sql
|
||
|
|
SELECT
|
||
|
|
causer_id,
|
||
|
|
properties->>'$.mode' AS mode,
|
||
|
|
properties->>'$.reason' AS reason,
|
||
|
|
properties->>'$.total_selected' AS selected,
|
||
|
|
properties->>'$.deleted_count' AS deleted,
|
||
|
|
properties->>'$.voided_count' AS voided,
|
||
|
|
properties->>'$.blocked_count' AS blocked,
|
||
|
|
JSON_LENGTH(properties->'$.affected_bill_nos') AS affected_count,
|
||
|
|
created_at
|
||
|
|
FROM activity_log
|
||
|
|
WHERE event = 'bulk_deleted'
|
||
|
|
AND created_at BETWEEN ? AND ?
|
||
|
|
ORDER BY created_at DESC;
|
||
|
|
```
|
||
|
|
|
||
|
|
可看出**每次批删**的统计 + 原因。
|
||
|
|
|
||
|
|
#### 查询 4:某 bill_no 是否被批删 / 作废过
|
||
|
|
|
||
|
|
```sql
|
||
|
|
SELECT *
|
||
|
|
FROM activity_log
|
||
|
|
WHERE event = 'bulk_deleted'
|
||
|
|
AND JSON_CONTAINS(
|
||
|
|
properties->'$.affected_bill_nos',
|
||
|
|
JSON_QUOTE('B-202605-501-001 [DELETED]')
|
||
|
|
);
|
||
|
|
```
|
||
|
|
|
||
|
|
或:
|
||
|
|
|
||
|
|
```sql
|
||
|
|
-- 灵活模糊匹配
|
||
|
|
SELECT *
|
||
|
|
FROM activity_log
|
||
|
|
WHERE event = 'bulk_deleted'
|
||
|
|
AND properties->'$.affected_bill_nos' LIKE '%B-202605-501-001%';
|
||
|
|
```
|
||
|
|
|
||
|
|
可追溯到**已物理删的 Bill** 是何时被谁删的。
|
||
|
|
|
||
|
|
### 第 3 步:解读 properties
|
||
|
|
|
||
|
|
不同 event 的 properties 结构不同:
|
||
|
|
|
||
|
|
| event | properties 主要字段 |
|
||
|
|
|---|---|
|
||
|
|
| `created`(账单创建)| `bill_no, amount, fee_type_id, resident_id` |
|
||
|
|
| `collected`(收款)| `amount, channel, receipt_id` |
|
||
|
|
| `voided`(单作废)| `reason, from_status, to_status, bill_no, amount, paid_amount` |
|
||
|
|
| `suspended`(挂起)| `reason, from_status, to_status, bill_no` |
|
||
|
|
| `resumed`(恢复)| `reason, from_status, to_status, bill_no` |
|
||
|
|
| `split`(拆账单)| `target_resident, amount_split, original_bill_id, new_bill_id` |
|
||
|
|
| `bulk_deleted`(批删)| `mode, reason, total_selected, deleted_count, voided_count, blocked_count, affected_bill_nos[]` |
|
||
|
|
|
||
|
|
详见 [[smart-bulk-delete-design]]"activitylog 设计"段。
|
||
|
|
|
||
|
|
### 第 4 步:出审计报告
|
||
|
|
|
||
|
|
```markdown
|
||
|
|
# 2026 年 5 月 嘉禾花园账单操作审计报告
|
||
|
|
|
||
|
|
## 审计范围
|
||
|
|
- 时段:2026-05-01 至 2026-05-31
|
||
|
|
- 模块:billing
|
||
|
|
- 关注操作:bulk_deleted, voided
|
||
|
|
|
||
|
|
## 高敏操作统计
|
||
|
|
- 批量删除(bulk_deleted):2 次
|
||
|
|
- 5/15 王主管:92 删 + 5 作废,原因"5 月 1 日 Replace 策略误用清理"
|
||
|
|
- 5/28 李经理:30 删,原因"测试数据清理"
|
||
|
|
- 单条作废(voided):8 次
|
||
|
|
- 大多与上述批删事件关联
|
||
|
|
- 1 次独立:5/22 王主管作废 Bill #321(原因:陈先生纠纷调解结果)
|
||
|
|
|
||
|
|
## 异常发现
|
||
|
|
- 无未授权操作(所有 bulk_deleted 操作员均有 bill.bulkDelete 权限)
|
||
|
|
- 无超规模操作(单次最多 92 张,合规)
|
||
|
|
- 所有操作都填了 reason(合规)
|
||
|
|
|
||
|
|
## 合规结论
|
||
|
|
- ✅ 所有高敏操作均有 audit trail
|
||
|
|
- ✅ 操作员权限符合岗位
|
||
|
|
- ✅ 原因填写规范
|
||
|
|
|
||
|
|
## 建议
|
||
|
|
- 长期保留 activitylog(至少 7 年,与会计档案同周期)
|
||
|
|
- 季度审计抽查
|
||
|
|
```
|
||
|
|
|
||
|
|
## 业户视角
|
||
|
|
|
||
|
|
业户**通常不直接接触** activitylog。但**法律纠纷时**:
|
||
|
|
|
||
|
|
- 业户对某账单的操作有疑问 → 业户可申请查看 activitylog
|
||
|
|
- 物业有义务**留存 + 展示**操作历史(透明化、可追溯)
|
||
|
|
- 业户/法院通过 activitylog 评估物业操作合规性
|
||
|
|
|
||
|
|
## 法务 / 监管视角
|
||
|
|
|
||
|
|
| 用途 | 怎么用 |
|
||
|
|
|---|---|
|
||
|
|
| **司法纠纷举证** | 业户起诉物业不当操作 → 物业拿 activitylog 证明操作合规 |
|
||
|
|
| **政府监管检查** | 检查批量删除 / 作废操作是否合理 |
|
||
|
|
| **行业自律审计** | 行业协会定期抽查 |
|
||
|
|
| **内部审计** | 财务总监 / 审计部门定期审 |
|
||
|
|
|
||
|
|
## 与 prop-acc 其他模块的对比
|
||
|
|
|
||
|
|
| 模块 | 审计方案 |
|
||
|
|
|---|---|
|
||
|
|
| **billing(本)** | **activitylog + meta** |
|
||
|
|
| deposit | meta JSON(`force_closed_*` / `freeze_reason` 等)|
|
||
|
|
| prepaid | meta JSON(`freeze_reason` / `unfreeze_reason`)|
|
||
|
|
| meter | meta JSON(`decommission_reason`)|
|
||
|
|
| adhoc | meta JSON 或 `voided_at` 字段 |
|
||
|
|
|
||
|
|
billing 的 activitylog 是 prop-acc **首个启用 spatie 审计日志的模块**(issue.md Q6 标志性改进)。其他模块**可以借鉴**(未来若启用,有 billing 实施经验)。
|
||
|
|
|
||
|
|
## activitylog 表结构(spatie 标准)
|
||
|
|
|
||
|
|
```sql
|
||
|
|
CREATE TABLE activity_log (
|
||
|
|
id BIGINT PRIMARY KEY,
|
||
|
|
log_name VARCHAR(255),
|
||
|
|
description TEXT, -- 简单描述
|
||
|
|
subject_type VARCHAR(255), -- 对象类(Bill)
|
||
|
|
subject_id BIGINT, -- 对象 ID
|
||
|
|
causer_type VARCHAR(255), -- 操作员类(User)
|
||
|
|
causer_id BIGINT, -- 操作员 ID
|
||
|
|
properties JSON, -- 详情(reason / amount / etc.)
|
||
|
|
event VARCHAR(255), -- 自定义事件名
|
||
|
|
batch_uuid VARCHAR(36), -- 可关联多条 log
|
||
|
|
created_at TIMESTAMP,
|
||
|
|
updated_at TIMESTAMP,
|
||
|
|
INDEX idx_subject(subject_type, subject_id),
|
||
|
|
INDEX idx_causer(causer_type, causer_id),
|
||
|
|
INDEX idx_event(event),
|
||
|
|
INDEX idx_created_at(created_at)
|
||
|
|
);
|
||
|
|
```
|
||
|
|
|
||
|
|
## 数据保留与归档
|
||
|
|
|
||
|
|
> [!warning] activitylog 表会快速增长
|
||
|
|
> 每次高敏操作 1 条 + 收款 / 创建 / 编辑等可能也加 → 数据增长快。
|
||
|
|
>
|
||
|
|
> 建议:
|
||
|
|
>
|
||
|
|
> - **生产环境**:保留 1-3 年热数据(查询性能优)
|
||
|
|
> - **冷数据归档**:超 1 年的迁到 archive 表 / 对象存储
|
||
|
|
> - **永久保留**:某些事件(bulk_deleted / voided)**法定 7+ 年**
|
||
|
|
>
|
||
|
|
> 当前未实施归档,数据量大后需要运维介入。
|
||
|
|
|
||
|
|
## 常见问题
|
||
|
|
|
||
|
|
> [!question] activitylog 与 meta JSON 的关系?
|
||
|
|
> 互补:
|
||
|
|
>
|
||
|
|
> | 方面 | activitylog | meta JSON |
|
||
|
|
> |---|---|---|
|
||
|
|
> | 保留多久 | 全平台共表(易归档)| 与对象同存(无法独立归档)|
|
||
|
|
> | 查询性能 | 按时间 / 类型查快 | 在对象上 random access 快 |
|
||
|
|
> | 关联多个对象 | ✅(properties 数组) | ❌(只在自己 meta)|
|
||
|
|
> | 跨模块查询 | ✅ | ❌(各模块字段不同)|
|
||
|
|
> | 操作上下文 | ✅(causer_id 直接)| ❌(若没存)|
|
||
|
|
>
|
||
|
|
> 推荐:meta 存"对象当前状态的辅助"(如 voided_at);activitylog 存"事件链"(谁干了什么)。两者不冲突。
|
||
|
|
|
||
|
|
> [!question] 业务人员如何看 activitylog?
|
||
|
|
> 当前**没有 UI**(spatie 包提供数据存储,UI 需自建)。审计师 / 业务方查询:
|
||
|
|
>
|
||
|
|
> - 直接 SQL(本场景示范)
|
||
|
|
> - 让运维查 / 出导出
|
||
|
|
> - 未来加 `ActivityLogResource` Filament UI(优先级看需求)
|
||
|
|
|
||
|
|
> [!question] activitylog 能改 / 删吗?
|
||
|
|
> 系统层面**理论上可改**(就是普通表)。**合规上不应改**(篡改审计 = 大罪)。
|
||
|
|
>
|
||
|
|
> 高级实施:用**append-only**表(数据库层面 disallow UPDATE/DELETE)+ 定期写校验和(若数据被改可发现)。当前未实施。
|
||
|
|
|
||
|
|
> [!question] 跨模块审计能合并查吗?
|
||
|
|
> 可以(activitylog 是全平台共表)。但其他模块当前**没启用 activitylog**(用 meta),所以跨模块审计**目前只看 bill 模块**。未来若其他模块启用,可统一查。
|
||
|
|
|
||
|
|
> [!question] activitylog 与系统日志(Laravel log)的差异?
|
||
|
|
> | 维度 | activitylog | Laravel log |
|
||
|
|
> |---|---|---|
|
||
|
|
> | 内容 | 业务事件(带 subject / causer / properties) | 技术日志(error / debug / info)|
|
||
|
|
> | 持久化 | 数据库 | 文件 / 集中日志服务 |
|
||
|
|
> | 业务查询 | ✅ 结构化,SQL 查 | ❌ 全文搜索 |
|
||
|
|
> | 合规价值 | **高** | 低(辅助排错)|
|
||
|
|
>
|
||
|
|
> 两者并存,各管各的。activitylog 关注**业务操作**,Laravel log 关注**系统行为**。
|
||
|
|
|
||
|
|
## 异常分支
|
||
|
|
|
||
|
|
- 发现疑似篡改 → 物业内部审计 + 法务介入
|
||
|
|
- 数据量太大查询慢 → 归档老数据
|
||
|
|
- 业户申请查 activitylog → 出报告(由审计师 / 法务出)
|
||
|
|
- 长期审计需求 → 加 `ActivityLogResource` UI(未来扩展)
|
||
|
|
|
||
|
|
## 相关文档
|
||
|
|
|
||
|
|
- [[smart-bulk-delete-design]](核心 activitylog 设计)
|
||
|
|
- [[delete-vs-void-dual-track]]
|
||
|
|
- [[bulk-delete-batch-mistake]]
|
||
|
|
- [[void-paid-bill]]
|
||
|
|
- [[suspend-bill]]
|
||
|
|
- [[resume-bill]]
|
||
|
|
- [[audit-monthly-billing-vs-collection]]
|