BesChannels Legacy Data Cleaning

Operating Contract

Guide the user through a project-specific cleaning run. Do not behave as a blind universal cleaner. This package follows the shared CLI contract described in cli-aligned-contract.md; it is a standalone cleaning Skill and does not include the CLI runtime or customer credentials.

1. Preserve the source file and hash it before work.
2. Inspect the actual workbook or CSV with scripts/inspect_workbook.py when supported.
3. Classify evidence using data-classification.md.
4. Return the confirmation card below with actual headers, source-column types, target fields, actions, and wait for confirmation on high-risk decisions.
5. Generate or adapt a project-specific cleaning script after confirmation.
6. Validate the outputs with scripts/validate_cleaned_output.py and the contract in cleaning-contract.md.
7. For BesChannels-specific fields and import failures, read beschannels-import-contract.md plus only the field references needed.

Do not modify the source, delete rows by default, guess business mappings, or claim an import succeeded from a local validation alone.

Confirmation Card

Reply in Chinese before cleaning:

本次识别到的文件：
表/编码/行列数：
目标系统与对象：
首要身份字段：
来源列 -> 字段类型 -> 处理动作 -> 目标字段：
计划清洗的字段：
不会改动的字段：
拟采用的规则：
需要确认：
预计输出：

Ask only decisions that change the result:

• Confirm the target is BesChannels and name the destination object.
• Confirm the first identity field for this import. Suggest mobile phone only as a default; allow the business object to choose another field.
• Use the target field display name in the import header. Never use an internal field name as a customer-facing import header.
• Confirm whether deduplication is enabled and the exact key.
• Confirm whether 手机号2 is processed.
• Confirm how duplicate headers map; never collapse them by display name alone.
• Confirm any field mapping, option mapping, row deletion, or merge that lacks direct evidence.
• Confirm whether each date-like source column is Date or DateTime; do not infer this from the column title alone.

If these decisions are missing, finish the inspection and plan, mark the run blocked, and do not clean.

Workflow

1. Inspect

Run with UTF-8 enabled on Windows:

$env:PYTHONUTF8 = '1'
python scripts/inspect_workbook.py "<INPUT>"

Record source SHA256, sheets, encoding, row/column counts, exact headers, duplicate headers, empty columns, and likely risk fields.

2. Lock The Run

For work expected to exceed 30 minutes or require probes/import feedback, create a run checkpoint with the source manifest, confirmed decisions, planned outputs, current step, and resume command. Never store customer values, credentials, or hidden reasoning in the checkpoint.

3. Clean On A Copy

• Preserve every row unless the user explicitly approves removal or merge.
• Keep unrelated columns byte- or value-equivalent where the writer permits.
• Prefer clearing only the unsafe field over deleting a record or making a weak guess.
• Clean before deduplication; deduplicate only on confirmed, non-empty keys.
• Merge tag/history fields only when the user confirms the delimiter and merge semantics.
• Record every changed field with row identifier, original value, cleaned value, action, reason, rule ID, and confidence.

For date-like columns:

• Keep Date and DateTime as separate field types. A date-only value must not be silently given a time, and a timestamp must not be truncated without confirmation.
• Preserve the original value in the audit. Mixed formats, Excel serial numbers, impossible calendar values, timezone-bearing values, and ambiguous day/month order go to review until the customer or current importer contract confirms a conversion.
• A valid local parse is not proof that the target importer accepts the serialized value. Probe the exact target object, field type, tenant, and importer version before expanding a date conversion.
• When the current contract confirms a DateTime serialization, record the exact format in the plan and validation result (for example, yyyy-MM-dd HH:mm:ss); do not generalize it to another object.

When the file resembles the verified four-key lead migration shape, offer but do not enable this template without confirmation: cleaned 手机号 + 邮箱 + 单位 + 微信openid/openid, keep the first row, uniquely merge confirmed history fields, retain the first non-empty value elsewhere, and send conflicting non-empty values to review.

4. Probe Dynamic Contracts

Treat schema, option labels, field types, tenant behavior, and importer versions as dynamic facts. Use a 2-50 row probe that isolates one cause before expanding a rule. Preserve the failure and success receipts.

5. Validate And Package

python scripts/validate_cleaned_output.py "<OUTPUT>" --source "<INPUT>"
python scripts/validate_cleaned_output.py "<OUTPUT.csv>" --encoding gb18030

Require zero unexplained row changes, zero unintended header changes, valid output encoding/line endings, and a field-level audit. Generate the standard artifacts described in cleaning-contract.md.

6. Report Truthfully

Distinguish these outcomes:

• planned: inspection and confirmation card are complete; cleaning has not run.
• blocked: a required decision, source, schema, option, or receipt is missing.
• completed: local artifacts and validation passed.
• import_verified: the target system accepted the intended rows and the receipt/readback was verified.

For multi-object or receipt-driven work, also report the finer-grained state when applicable: prepared, locally_validated, imported, readback_verified, partial, or review. A local validation result does not imply an external import or readback.

Never promote completed to import_verified from a script exit code or model statement.

Progressive References

• Read cleaning-contract.md for outputs, audit fields, summary schema, validation, and failure behavior.
• Read data-classification.md before mixing customer evidence, confirmed rules, live system facts, or AI suggestions.
• Read province-city-rules.md for province/city/address work.
• Read email-mobile-rules.md for email, mobile, landline, and 手机号2 work.
• Read beschannels-import-contract.md for CSV encoding, URL, Address, CheckBox, probes, import receipts, and known cross-case evidence.

Stop Conditions

Stop without expanding a change when the source hash drifts, headers or row counts change unexpectedly, the tenant/object/schema cannot be verified, option labels are ambiguous, duplicate-header mapping is unconfirmed, a probe tests more than one cause, or two consecutive tool/import failures have no new evidence.

Preserve the last valid checkpoint and return the exact missing decision or failed validation.