# Spreadsheet-to-app mapping worksheet

Use a saved copy of a small, permitted sample. Keep the original values separate from corrections. This worksheet specifies intended behavior; it is not evidence of an executed import.

## Your source and decisions

| Question | Your answer |
| --- | --- |
| Snapshot filename and date | |
| What does one source row represent? | |
| Source-row identifier and how it is assigned | |
| Business identifier for each record type | |
| Date convention and allowed formats | |
| Meaning of blank versus zero | |
| Required fields | |
| Approved status/service dictionaries | |
| Identity rule; fields that must never be used alone to merge records | |
| Repeated-item rule: unique selection or quantity? | |
| Same ID with identical data: intended behavior | |
| Same ID with different data: conflict owner and resolution | |
| Same batch loaded twice: intended behavior and test | |
| Access policy and test-copy scope | |

## Map each source column

| Source column | Target table and field | Type | Required? | Transformation | What needs human review? |
| --- | --- | --- | --- | --- | --- |
| | | | | | |
| | | | | | |
| | | | | | |

## Account for every source row

| Source-row ID | Accepted / duplicate / rejected | Resulting record ID or retained duplicate | Reason | Owner | Next action | Resolved date |
| --- | --- | --- | --- | --- | --- | --- |
| | | | | | | |

Use one primary outcome per source row. Keep additional issues in its notes rather than counting the row twice. Retain original values and link a correction back to the source row.

## Reconcile the intended result

- Source rows: ____ = accepted ____ + duplicate ____ + rejected ____.
- Distinct accepted business IDs: ____.
- Every child record points to an existing parent: expected ____; observed ____.
- No unintended duplicate relationship pairs: expected ____; observed ____.
- Multi-value entries: ____ retained + ____ repeated + ____ duplicate-row entries + ____ rejected-row entries = ____ source entries.
- Two similar names that must remain separate: ____.
- A known record and its expected parent/children: ____.

## Harbor Light fixture rules

These rules describe the invented example supplied with the guide.

1. A source row describes a maintenance request; the snapshot has stable IDs S001–S012. Request and client IDs are required and preserved as text after trimming.
2. A client is identified by client_ref. A client name or email is never the identity key. Repeated client IDs must have matching cleaned name and contact details; otherwise hold the request as a conflict.
3. Dates are date-only values. Accepted formats are YYYY-MM-DD, YYYY/MM/DD, MM/DD/YYYY, and an English three-letter month followed by day and four-digit year, such as Oct 9, 2026. Month/day order is expressly declared for this fixture. Reject impossible dates. Do not apply that locale assumption to an unknown source.
4. Contact emails in this fixture are compared without letter-case differences, after trimming. They must contain one @, a nonempty local part and a dot in the domain with no whitespace. That minimal fixture check does not establish deliverability. All addresses are invented and no email is sent.
5. New, Scheduled, In progress and Done map case-insensitively to new, scheduled, in_progress and completed. Other values need review.
6. Split services only on semicolons and trim each label. Exact approved labels map to SV-01 Gutter cleaning, SV-02 Window washing and SV-03 Pressure washing. A repeated label in a single request is one selected service, not a quantity. Blank or unknown tokens reject the whole request so none of its work is silently discarded.
7. For the same request ID, compare client identity/details, date, status and the set of service IDs after normalization. Identical payloads are duplicates; keep the first source row in this saved snapshot and log the pointer. Different payloads are unresolved conflicts; do not apply a last-row-wins rule.
8. Create clients and relationship rows only for accepted requests in this batch. A rejection produces no partial client, request or service-link records.
9. Validation precedence is required identity/name/contact fields, contact format, calendar date, status, service labels, existing-client conflict, and repeated-request comparison. Record one primary outcome per row; retain other findings in the review notes if needed.
10. The expected CSVs are a teaching fixture. They contain no access credentials, real client information, or proof that any target application supports loading them.

## Test execution, when separately authorized

| Check | Expected | Observed | Status | Evidence |
| --- | --- | --- | --- | --- |
| Initial load | Counts and relationships match the reviewed plan | | Not tested | |
| Return after reload | The same saved records remain | | Not tested | |
| Repeated batch | No extra records or links | | Not tested | |
| Conflicting ID | Conflict is visible; no silent overwrite | | Not tested | |
| Interrupted load | Behavior matches the chosen rollback/resume policy | | Not tested | |
| Access boundary | Only the permitted roles can read or change records | | Not tested | |