# Customer Profile Dashboard - Database Connection Guide

A plain-English breakdown of every piece of data on this page that would need to talk to a database, organized by what you'd see on screen.

---

## 1. Customer Record

The core customer — one row in the database that everything else hangs off of.

| Field | What It Is | When It Changes |
|-------|-----------|-----------------|
| Name | "Austin Baker" | User edits in Profile modal |
| Phone numbers | One or more phone numbers | Add, edit, or remove in Profile |
| Email addresses | One or more emails | Add, edit, or remove in Profile |
| Active/Inactive | Whether the account is live | Toggle switch in header |
| Inactive reason | Why they were deactivated | Set when toggling off |
| Contact preference | Text, Email, or Phone | Changed in Profile |
| Preferred time | AM, PM, Eve checkboxes | Changed in Profile |
| Customer source | How they found you (Referral, Google, etc.) | Changed in Profile |
| Pet type | No Pets, Dog, Cat, Dog & Cat | Changed in Profile |
| Dangerous dog | Yes/No flag | Checkbox in Profile |
| VIP status | Yes/No (auto-set when rating >= 4.5) | Auto-calculated from ratings |
| Rating scores | 5 scores: Friendly, Easy, Pay, Stay, Trust (1-5 each) | Set in Profile > Attributes tab |

**Database action:** Read on page load. Write whenever the user changes anything in the Profile modal or toggles the active switch.

---

## 2. Locations

Each customer can have multiple properties. Each location is its own record linked to the customer.

| Field | What It Is | When It Changes |
|-------|-----------|-----------------|
| Address | Full street address | Set when location is created |
| Acreage | Property size in acres | Editable on the map overlay |
| Square footage | Treatable area | Editable on the map overlay |
| Active/Inactive | Whether this location is being serviced | Toggle in Profile |
| Inactive reason | Why location was deactivated | Set when deactivating |
| Google image URL | Street View or uploaded photo | Changes when user sets a main photo or resets |

**Database action:** Read all locations for this customer on page load. Write when a new location is added, or when acreage/sqft/photo/status changes.

---

## 3. Services (Per Location)

Each location can have multiple pest control services. This is where the money lives.

| Field | What It Is | When It Changes |
|-------|-----------|-----------------|
| Service type | Tick Control, Lanternfly, Mosquito | Set when service is added |
| Price per spray | Dollar amount per application | Changed in the pricing configurator |
| Number of sprays | How many sprays in the season (1-7) | Changed in the pricing configurator |
| March add-on | Yes/No - early season spray | Toggled in configurator |
| November add-on | Yes/No - late season spray | Toggled in configurator |
| Payment method | "On Receipt" or "Prepay" | Selected in configurator |
| Archived | Soft-delete flag when service is removed | Set when user removes a service |
| Pesticide info | Product name, active ingredient, EPA #, signal word | Comes from service type catalog |

**Database action:** Read all services for each location on page load. Write when the user changes price, spray count, add-ons, payment method, or adds/removes a service.

---

## 4. Service History

The record of what happened each season — scheduled sprays, completed sprays, payments.

| Field | What It Is | When It Changes |
|-------|-----------|-----------------|
| Date | When the service was/is scheduled | Set when spray is scheduled |
| Invoice ID | Invoice number tied to this spray | Set when invoice is created |
| Payment date | When payment was received | Set when payment is collected |
| Payment method | How they paid (Visa **4768, Check, etc.) | Set when payment is collected |
| Status | Scheduled, Due, Paid, Complete | Changes as work progresses |
| Year | 2025, 2026, etc. | Set at creation |

**Database action:** Read history for each service on page load. Write when sprays are scheduled, invoiced, or paid.

---

## 5. Spray Log

Detailed field records for each spray application — this is what goes on the pesticide report.

| Field | What It Is |
|-------|-----------|
| Date | When the spray was performed |
| Operator 1 & 2 | Names of technicians |
| License numbers | Applicator license #s |
| Pest type | What was being treated |
| Product | Pesticide used |
| Active ingredient | Chemical name |
| EPA registration # | Federal registration |
| Signal word | Caution, Warning, Danger |
| Square footage | Area treated |
| Gallons used | Amount of product |
| Rate | Application rate |
| Weather | Conditions during spray |
| Truck | Which vehicle was used |

**Database action:** Read-only on this page (spray logs would be created by the field team in a separate workflow). The dashboard displays them and can export to PDF.

---

## 6. Activity Log / Timeline

The running history of everything that happens on this account — notes, system events, status changes.

| Field | What It Is | When It Changes |
|-------|-----------|-----------------|
| Timestamp | When it happened | Auto-set at creation |
| Heading | What happened ("User Note", "Service Added", etc.) | Auto-set based on action |
| Description | Details or user-written note text | Set at creation, editable for user notes |
| Author | Who did it (Darren, System, etc.) | Auto-set |
| Type/Icon | Visual indicator (note, call, system, etc.) | Auto-set based on action |
| Editable | Whether the user can change the text | True for user notes, false for system entries |

**Database action:** Read all entries for the current location on page load. Write when:
- User adds a note manually
- User edits an existing note
- System auto-generates entries (service added/removed, location activated/deactivated, etc.)

---

## 7. Location Instructions

Pinned instructions that the field team sees before spraying a property.

| Field | What It Is | When It Changes |
|-------|-----------|-----------------|
| Title | Short label ("VIP Customer", "Beehives", etc.) | Set at creation |
| Description | Detailed instruction text | Set at creation |
| Type | info, caution, danger, success | Auto-set based on source |

These come from two places:
- **Checkboxes** in Profile > Attributes tab (VIP, Danger Dog, Veg Garden, Beehives, etc.) — checking a box auto-creates the instruction, unchecking removes it
- **Manual entry** — user types a custom instruction

**Database action:** Read all instructions for the current location on page load. Write when checkboxes change or manual instructions are added/deleted.

---

## 8. Documents

Files uploaded to the customer's account (contracts, photos, PDFs).

| Field | What It Is | When It Changes |
|-------|-----------|-----------------|
| Filename | Original file name | Set at upload |
| Upload date | When it was uploaded | Auto-set |
| File data | The actual file (would be stored in cloud storage) | Set at upload |
| File type | PDF, PNG, JPG, DOC, etc. | Auto-detected |

**Database action:** Read document list on modal open. Write when files are uploaded or deleted. The actual file bytes would go to cloud storage (S3, etc.), with the database storing the reference/URL.

---

## 9. Photo Gallery

Property photos tied to each location.

| Field | What It Is | When It Changes |
|-------|-----------|-----------------|
| Image URL/data | The photo itself | Set at upload |
| Is main photo | Whether this shows on the map | Changed via "Set as Main" button |

**Database action:** Read all photos for the current location on modal open. Write when photos are uploaded, deleted, or set as the main display photo.

---

## 10. Invoices

Bills sent to the customer.

| Field | What It Is | When It Changes |
|-------|-----------|-----------------|
| Invoice number | Auto-generated ID (e.g., INV-2026-104) | Set at creation |
| Date | Invoice date | Set by user |
| Due terms | Upon Receipt, Net 7, Net 14, Net 30 | Selected by user |
| Line items | Description, quantity, price, taxable flag | Added/edited by user |
| Subtotal | Sum of line items | Auto-calculated |
| Tax | CT tax at 6.35% on taxable items | Auto-calculated |
| Total | Subtotal + tax | Auto-calculated |
| Message | Personal note to include | Written by user |
| Send method | Email or Text | Selected by user |
| Send to | Which email/phone to send to | Selected by user |

**Database action:** Write the full invoice when "Send Invoice" is clicked. This is a transactional write — it creates the invoice record, the line items, and triggers the delivery (email or SMS via an external API).

---

## 11. Payments

Money collected from the customer.

| Field | What It Is | When It Changes |
|-------|-----------|-----------------|
| Payment method | Card on file, check, ACH, Venmo, etc. | Selected at collection |
| Reference number | Check #, transaction ID, etc. | Entered at collection |
| Amount | How much was paid | From invoice total |
| Invoice ID | Which invoice this pays | Linked at collection |

**Database action:** Write when "Charge" is clicked. This would also need to talk to a payment processor (Stripe, Square, etc.) for card charges, then update the invoice status to Paid and create a history entry.

---

## 12. Account Statement

A read-only view that pulls from invoices and payments to show the full financial picture.

| What It Shows |
|--------------|
| All invoices with dates and amounts |
| All payments with dates and amounts |
| Running balance |
| Aging buckets (Current, 30 days, 60 days, 90+ days) |

**Database action:** Read-only. This is assembled from the invoice and payment tables — no separate storage needed.

---

## 13. Communications

Message threads between staff and the customer.

| Field | What It Is | When It Changes |
|-------|-----------|-----------------|
| Conversation | Which employee is chatting | Created when thread starts |
| Messages | Individual messages in the thread | Added when someone sends a reply |
| Timestamp | When each message was sent | Auto-set |
| Direction | Inbound vs outbound | Auto-set |
| SMS sends | Text messages sent to customer phone | Created via "Send SMS" |
| Call logs | Record of phone calls made | Created via "Call" button |

**Database action:** Read all conversations for this customer on modal open. Write when a new message is sent. SMS and calls would also trigger external APIs (Twilio, etc.) and log the communication.

---

## 14. Employees / Users

The people who use the system.

| Field | What It Is |
|-------|-----------|
| Name | Employee name (Darren, Jeff, Jacob, Carlos, Sue) |
| Avatar | Profile photo |
| Role/permissions | What they can do (not yet implemented) |
| Current user | Who is logged in |

**Database action:** Read on page load. This would come from an authentication/user management system. Currently hardcoded.

---

## 15. Service Type Catalog

The master list of services your company offers — this is reference data, not per-customer.

| Field | What It Is |
|-------|-----------|
| ID | tick, lanternfly, mosquito, flea |
| Name | Display name |
| Color | Brand color for UI |
| Default pesticide | Product name |
| Active ingredient | Chemical name |
| EPA number | Registration number |
| Signal word | Caution/Warning/Danger |
| Prepay rule | e.g., "Pay 6 get 7th free" |

**Database action:** Read-only on this page. Would be managed in an admin/settings area.

---

## 16. Preferences & Settings

Small pieces of state that persist.

| What | Where It's Stored Now | Where It Should Go |
|------|----------------------|-------------------|
| Light/dark mode | Browser localStorage | Could stay in localStorage (cosmetic only) |
| Customer source | In-memory | Database (customer record) |
| Theme preference | localStorage key `tc-theme` | localStorage is fine |

---

## Summary: What Talks to the Database

### On Page Load (READ)
1. Customer record (name, phones, emails, status, preferences, ratings)
2. All locations for this customer
3. All services for each location
4. Service history for each service
5. Spray logs for each service
6. Timeline/notes for each location
7. Instructions for each location
8. Employee/user list
9. Service type catalog

### When User Takes Action (WRITE)
1. **Profile changes** - name, phone, email, pets, source, preferences, ratings
2. **Location changes** - new location, acreage/sqft, activate/deactivate
3. **Service changes** - add/remove service, price, sprays, add-ons, payment method
4. **Notes** - add or edit timeline entries
5. **Instructions** - add or remove via checkboxes or manual entry
6. **Documents** - upload or delete files
7. **Photos** - upload, delete, or set as main
8. **Invoices** - create and send (also triggers email/SMS API)
9. **Payments** - collect payment (also triggers payment processor API)
10. **Communications** - send messages (also triggers SMS/email API)
11. **Account status** - activate/deactivate with reason

### External APIs Needed
- **Google Maps** - Street View, Satellite, Geocoding (already integrated)
- **Payment processor** - Stripe, Square, etc. for card charges
- **SMS provider** - Twilio or similar for text messages
- **Email service** - SendGrid or similar for invoice/statement delivery
- **Cloud storage** - S3 or similar for document and photo files