Plan: Google Sheet to backend import pipeline (design only)

[codethor0]

Goal

Design how approved Google Sheet rows (from Form intake) map to future PostgreSQL records. No implementation in this issue.

Current state

• Form responses land in project Google Sheet with manual columns: review_status, reviewer, notes
• Backend report API expects in-app reports tied to existing job postings
• Offline URL helper exists: backend/app/services/job_url_validation.py

Deliverables

1. Field mapping document (Sheet columns -> company, posting, report fields)
2. Duplicate detection strategy using normalized job URLs
3. Import eligibility rules tied to review_status
4. Idempotency and audit requirements
5. PII redaction rules before public use
6. Explicit v1 non-goals (no scraping, no auto-verify)

Constraints

• No schema or API changes until design is approved
• No new dependencies without approval
• See docs/post-launch-roadmap.md Track 1

Related

• Issue Design review: future URL validation API integration #5 (URL validation at intake)
• Issue Define: Form/Sheet moderation workflow and future product UI #7 (moderation workflow)

[codethor0]

Batch 10D complete at commit 3b76e34.

Design document: docs/sheet-import-design.md

Deliverables from this issue's planning scope are documented:

• Field mapping (Form/Sheet columns to company, job posting, report)
• Duplicate detection via normalized job URLs
• Import eligibility tied to import_ready=yes and moderation SOP
• Idempotency and audit logging expectations
• PII rules (optional contact email not imported)
• Explicit v1 non-goals (no scraping, no auto-verify, dry-run default)

Issue remains open. CLI import implementation is deferred to Batch 12A pending maintainer approval of the design checklist in the doc.

Cross-links updated in post-launch-roadmap, implementation-status, moderation-sop, and google-form-intake-spec.

[codethor0]

Batch 12C audit remediation checkpoint (2026-07-01).

Status update:

• Batch 10D design is complete (docs/sheet-import-design.md).
• Batch 12A dry-run CLI is complete (scripts/sheet_import_dry_run.py, scripts/verify_sheet_columns.py; no database writes).
• Batch 12B apply-mode design is complete (docs/sheet-import-apply-design.md).
• --apply implementation is not implemented and remains pending.
• Implementation is blocked until section 18 approval gates in docs/sheet-import-apply-design.md are explicitly approved.
• Real sanitized Google Sheet CSV export verification via scripts/verify_sheet_columns.py is still required before apply-mode implementation.
• Batch 12C does not implement --apply.

Issue remains open for apply-mode implementation after maintainer gate approval.