一张视图加一张流水表，做物业自动催缴：4 条规则 3 道闸门的完整实现（SQL 可直接跑）

收费员每月的催缴工作本质是一张欠费表加一段定时任务应收按账期切片入库收款流水只增不改四条阶梯规则命中该催的人三道闸门挡住重复发送。跑完一轮谁该催、催到哪一档、上次什么时候催过全在库里留痕。本文的 SQL 和 Python 代码都做了脱敏可以直接建库执行。行业里常见的情形是这样的。收费员手里一张表一列房号一列欠费合计每月 5 号开始挨个打电话。老小区账期乱三年前的尾款和上个月的物业费混在一格里住户说我交过了两边对着流水翻半天。更难的是换人接手——新来的不知道上一任催过谁、催到第几回于是同一户一个月接到三个电话。问题不在人在于数据模型里压根没有账期这个维度。一、账期切片把欠了多少钱拆成哪几期没交两张表够用应收一户一账期一行收款流水只增不改。欠费不存字段由两者相减算出来。CREATETABLEfee_bill(-- 应收一户一账期一条idINTEGERPRIMARYKEY,community_idINTEGERNOTNULL,room_idINTEGERNOTNULL,periodTEXTNOTNULL,-- 账期如 2026-08amount_dueINTEGERNOTNULL,-- 应收单位分due_dateTEXTNOTNULL,-- 应缴截止日UNIQUE(room_id,period));CREATETABLEpay_record(-- 收款流水只增不改idINTEGERPRIMARYKEY,room_idINTEGERNOTNULL,amountINTEGERNOTNULL,-- 实收单位分paid_atTEXTNOTNULL);CREATEVIEWv_arrearsASSELECTb.room_id,b.period,b.due_date,b.amount_due-COALESCE(SUM(p.amount),0)ASowe_fen,CAST(julianday(now)-julianday(b.due_date)ASINTEGER)ASdays_overFROMfee_bill bLEFTJOINpay_record pONp.room_idb.room_idANDp.paid_atb.period||-01ANDp.paid_atdate(b.period||-01,1 month)GROUPBYb.idHAVINGowe_fen0;金额一律存整数分。这一条看着啰嗦但欠费金额要参与求和、冲抵、对账三个环节浮点在这三处各留一点误差几百户攒下来台账和财务永远差几毛解释成本比省下的代码高得多。视图里写死了冲抵口径某期的收款只冲该期。真实项目里住户一次交三个月很常见那要改成按账期先后依次冲抵的存储过程。先把口径固定在一个视图里比一开始就做万能冲抵可靠——口径错了后面所有催缴记录都是错的。二、规则匹配四条阶梯必须按严格度降序命中规则不写成一串 if-else做成数据表运维自己改代码不动。fromdataclassesimportdataclassfromdatetimeimportdatedataclass(frozenTrue)classRule:name:str;min_days:int;max_days:intmin_fen:int;cooldown_days:int;channel:strRULES(Rule(到期前提醒,-7,-1,0,30,sms),Rule(轻度,1,15,5000,7,sms),Rule(中度,16,45,20000,15,sms_call),Rule(重度,46,9999,50000,30,letter),)defpick_rule(days_over,owe_fen):hit[rforrinRULESifr.min_daysdays_overr.max_daysandowe_fenr.min_fen]returnmax(hit,keylambdar:r.min_days)ifhitelseNone这是整篇最容易写错的一行。规则表天然是多条同时为真欠 60 天、欠 800 元的户四条里踩中三条。若写成for r in RULES: if 匹配: return r返回的是排在前面那条到期前提醒于是越欠越久的户收到的消息越客气。max(..., keymin_days)才是对的严的一档覆盖宽的一档。还有个取舍金额门槛按户算不按单期算。按单期算一户欠 6 期、每期 3000 元永远够不上 50000 分的门槛永远进不了重度档。所以匹配前先按户汇总SELECTroom_id,SUM(owe_fen)ASowe_total,MAX(days_over)ASdays_overFROMv_arrearsGROUPBYroom_id;三、三道闸门同一户别在一天里收到三条短信规则算的是该催谁闸门管的是现在能不能催。两者分开因为规则是业务定的闸门是投诉率教出来的。闸门一靠数据库约束不靠代码判断有没有发过CREATETABLEdunning_log(idINTEGERPRIMARYKEY,room_idINTEGERNOTNULL,periodTEXTNOTNULL,rule_nameTEXTNOTNULL,channelTEXTNOTNULL,sent_atTEXTNOTNULL,statusTEXTNOTNULLDEFAULTqueued-- queued/sent/failed);CREATEUNIQUEINDEXux_dunning_dedupONdunning_log(room_id,period,rule_name);定时任务被重跑、被并发触发、被人手动补一遍插入都撞约束撞了就跳过。这比先查有没有记录再插入稳因为两次查询之间存在时间窗。闸门二是冷却按户算不带账期。这条是踩过的坑。defgate_cooldown(conn,room_id,rule,today):lastconn.execute(SELECT MAX(sent_at) FROM dunning_log WHERE room_id? AND statussent,(room_id,)).fetchone()[0]iflastand(today-date.fromisoformat(last[:10])).daysrule.cooldown_days:returnFalse,冷却期内returnTrue,若冷却条件里多加一个AND period ?看着更精准实际会炸一户欠 6 个账期六个账期各算各的冷却全都判定可发同一天出去 6 条。住户视角就是你们是不是有病。冷却的主语是人不是账期。闸门三是月度上限加重度转人工单户自然月触达不超过 3 次重度档不进发送队列写进人工待办由人决定上门还是走函件。系统能判断欠了 90 天判断不了这户是独居老人还是恶意拖欠最后一步交给人投诉就压住了。三件事串起来就是一个循环defrun_daily(conn,today):forroom_id,owe,daysinconn.execute(SELECT room_id, SUM(owe_fen), MAX(days_over) FROM v_arrears GROUP BY room_id):rulepick_rule(days,owe)ifnotruleornotgate_month_cap(conn,room_id,today):continueifgate_cooldown(conn,room_id,rule,today)[0]:yieldroom_id,rule# letter 档改投人工待办表被跳过的户别直接丢掉把跳过原因存成当天运行明细。收费员问这户为什么没催答案在这一列里不用翻日志。结果与数据这套设计在本地 SQLite 上用假数据跑通了完整链路上面的代码块可以直接建库执行。下表列的是结构差异和自证方式不写任何无法复现的业务指标。环节翻表人工做法这套设计怎么自己验证欠费口径一列合计账期混在一起视图按账期冲抵算出插 3 期数据跑v_arrears看分期该催谁人眼看颜色、凭记忆规则表匹配取最严档造欠 60 天、欠 800 元的户看是否命中重度会不会重复催靠收费员记性去重索引 户级冷却同一批数据连跑两次run_daily第二次应为空换人接手交接靠口头dunning_log全留痕查statussent含档位与时间越权发送无约束重度档转人工造 90 天欠费看它进 manual 不进 auto真正省下的不是打电话的时间是这户到底催过没有的扯皮时间。这套 IM 系统的完整实现已开源https://gitee.com/281064811/jiaoyou-uniapp