feat: secondary field rules

[ethnjs]

Secondary Field Rules — Let a Rule Affect a Different Field

Problem

A ParseRule today can only transform the value before it lands in its mapped field. There is no way for a single header to also write to a different field. A common real case: an availability question on a confirmation form should simultaneously set status = confirmed or status = declined based on the cell value. Today this requires a separate column or manual TD override.

---

Solution

Each ParseRule gains an optional secondary field target. When toggled on for a rule, that rule writes its output to a different field instead of the primary mapped field. The toggle is per-rule and independent — two rules on the same mapping row can target completely different secondary fields.

---

Data Model Changes

ParseRule — new nullable secondary field fields

class ParseRule(BaseModel):
    # ... existing fields (condition, match, case_sensitive, action, value, is_alias) ...
 
    # Secondary field target — nullable, all absent from JSON when null.
    # When secondary_field is non-null, this rule writes to the secondary field
    # instead of the primary mapped field.
    secondary_field:       str | None = None
    secondary_field_type:  str | None = None  # "single" | "list" | "group" | "ignore"
    secondary_value_type:  str | None = None  # "text" | "number" | "boolean" | "date" | "time_range"
    secondary_extra_key:   str | None = None  # required if secondary_field == "extra_data"
    secondary_group_key:   str | None = None  # required if secondary_field_type == "group"

ColumnMapping is unchanged — no secondary field concept at the mapping level.

Validators on ParseRule

• If secondary_field is non-null, it must be a valid field name from the appropriate KNOWN_FIELDS for the sheet type.
• If secondary_field == "extra_data", secondary_extra_key is required — error if missing.
• If secondary_field_type == "group", secondary_group_key is required — error if missing.
• If secondary_field is null, all other secondary_* fields must also be null — error otherwise.
• secondary_field_type and secondary_value_type follow the same valid value sets as field_type and value_type on ColumnMapping.

---

Rule Execution in Sync

Rules execute sequentially in order as today. Each rule independently determines where its output goes:

• rule.secondary_field is null → apply to the primary field value (current behavior, unchanged).
• rule.secondary_field is non-null → apply to the secondary field value for that specific target field.

Each unique secondary field target gets its own independent value accumulator, initialized empty. Primary and secondary accumulators do not affect each other.

At the end of rule processing:

• Primary field value → written to the mapped field as today.
• Each secondary field accumulator (if non-empty, i.e. at least one secondary rule fired and matched) → written to membership_fields[secondary_field] using the rule's secondary_field_type / secondary_value_type coercion, respecting secondary_extra_key and secondary_group_key.
• If a secondary rule's condition did not match, that rule does not fire and nothing is written to its secondary field target.
• For group secondary field type: merges into the existing dict value for that field, same as normal group aggregation.

Example

Header: "Will you be available?" → field: availability, field_type: group, value_type: time_range
 
Rule 1: condition: contains, match: "Yes" → primary (accumulates into availability)
Rule 2: condition: contains, match: "Yes" → secondary_field: status,
                                            secondary_field_type: single,
                                            secondary_value_type: text,
                                            action: set, value: "confirmed"
Rule 3: condition: contains, match: "No"  → secondary_field: status,
                                            secondary_field_type: single,
                                            secondary_value_type: text,
                                            action: set, value: "declined"

If cell = "Yes, Thursday 5/21": Rule 1 fires → availability slot added. Rule 2 fires → status accumulator = "confirmed". Rule 3 does not fire. Final write: availability updated, status = "confirmed".

---

Enum Validation in /validate-mappings

Fields with enum constraints

Add a registry of fields with known enum values:

FIELD_ENUM_VALUES: dict[str, set[str]] = {
    "status":   {"interested", "confirmed", "declined", "assigned", "removed"},
    "division": {"A", "B", "C"},   # temp until tournament-level division config
    "type":     {"standard", "trial"},
}

For any rule (primary or secondary) where action is set or replace and the target field is in FIELD_ENUM_VALUES — check that rule.value is one of the allowed values. If not → error.

Overwrite detection

Direct mapping + secondary rule targeting same field:
If header A maps directly to field X (field = X), and header B has a rule with secondary_field = X — warning (ignorable via ?force=true). Last write wins at sync time if forced.

Exception — no warning if field X is group type AND the group_key on the direct mapping differs from secondary_group_key on the rule (different keys don't overwrite).

Exception becomes error if field X is group type AND group_key == secondary_group_key — same key is a guaranteed overwrite.

Two secondary rules on different mapping rows targeting same non-group field:
→ warning.

Two secondary rules both targeting extra_data with the same secondary_extra_key:
→ error.

---

Frontend — Mapping Table UI

Per-rule secondary field toggle

Each rule row in the rule editor gains a toggle at the far right — a small button or icon labeled "→ field" or "2nd".

When toggled on for a rule:

• The rule row expands vertically. A second indented line appears below the existing rule controls containing:
  • Field dropdown (scoped to sheet type's known fields)
  • Field type dropdown (Single / List / Group / Ignore)
  • Value type dropdown (Text / Number / Yes/No / Date / Time Range) — disabled when field type is Ignore
  • Extra key input — shown when secondary field == extra_data
  • Group key input — shown when secondary field type == group
• These controls are specific to this rule only — other rules on the same mapping row are unaffected.

When toggled off:

• Clears all secondary_* fields on that rule.
• Collapses the secondary field controls.

Visual distinction

Rules with a non-null secondary_field render with a subtle visual indicator — e.g. a small "→ [field label]" tag or a colored left border — so TDs can see at a glance which rules affect other fields without opening the full controls.

Validation feedback

Secondary field rule errors and warnings appear in the existing error/warning tooltip on the mapping row accordion, prefixed with the rule number: e.g. Rule 2: "confirmedd" is not a valid value for status.

---

Files Expected to Change

• backend/app/schemas/sheet_config.py — add secondary_* nullable fields to ParseRule, add FIELD_ENUM_VALUES registry, validators
• backend/app/services/sync_service.py — per-rule secondary field dispatch, independent secondary accumulators per target field, secondary field writes after rule loop
• backend/app/services/sheets_validation.py — secondary_* cross-field validators, enum value check for primary and secondary targets, overwrite detection
• frontend/lib/api.ts — add secondary_* nullable fields to ParseRule
• frontend/components/ui/SheetConfigMappingTable.tsx — per-rule secondary field toggle, indented secondary field controls per rule row, visual indicator
• backend/tests/services/test_sheets_validation.py — enum validation, overwrite detection cases, missing secondary key errors, null consistency errors
• backend/tests/services/test_sync_service.py — secondary field write, primary unaffected, condition miss leaves secondary untouched, group merge, multiple rules with different secondary targets on same mapping

---

Completion Criteria

1. A rule with secondary_field="status", action="set", value="confirmed" writes status = "confirmed" to the membership when its condition matches.
2. The primary field value is unaffected by rules that have secondary_field set.
3. If a rule's condition does not match, nothing is written to its secondary field target.
4. Two rules on the same mapping row can each independently declare different secondary_field values — both fire independently.
5. /validate-mappings errors if secondary_field is null but any other secondary_* field is non-null.
6. /validate-mappings errors if secondary_field == "extra_data" with no secondary_extra_key.
7. /validate-mappings errors if a rule's value is not in the allowed enum set for its target field (primary or secondary).
8. /validate-mappings warns on direct mapping + secondary rule targeting the same non-group field.
9. /validate-mappings errors on direct mapping + secondary rule targeting the same group field with the same group key.
10. Each rule row in the accordion has an independent secondary field toggle.
11. Toggling a rule's secondary field toggle on reveals indented secondary field controls scoped to that rule only.
12. Rules with a secondary field show a visual indicator (e.g. "→ Status") in the collapsed rule list.
13. Existing rules with all secondary_* fields null behave exactly as before — no regression.