# Data Model and Integration Boundaries

## 1. Database Artifacts

Database files:

- `database/schema.sql`
- `database/seed.sql`
- `migrations/m250613_000001_initial_schema.sql`
- `migrations/20260615_000001_add_user_profile_fields.sql`
- `migrations/20260615_000002_add_invoice_payment_fields.sql`
- `migrations/20260615_000003_add_maintenance_agreements.sql`
- `migrations/20260615_000004_add_crews_and_job_crew_assignment.sql`

Claude should inspect schema definitions directly and build a table-level testing plan.

Important table areas expected from current code/docs:

- Tenants and users
- Jobs and job workflow data
- Clients/customers
- Quotes and quote line items
- Invoices, invoice line items, and payment fields
- Team members/users
- Maintenance agreements and generated recurring jobs
- Crews and job crew assignments
- Audit logs
- SaaS subscription plans and tenant subscriptions
- Platform/admin billing foundations
- Import/export tracking foundations
- LLM connection and usage foundations
- API/offline/customer portal foundations

## 2. Persistence Boundaries

Repository classes represent persistence boundaries:

- `ClientRepository` and `DemoClientSessionRepository`
- `JobRepository` and `DemoJobSessionRepository`
- `QuoteRepository` and `DemoQuoteSessionRepository`
- `InvoiceRepository` and `DemoInvoiceSessionRepository`
- `TeamMemberRepository` and `DemoTeamMemberSessionRepository`
- `MaintenanceAgreementRepository` and demo equivalent
- `CrewRepository` and demo equivalent
- `DatabaseAuditLogRepository` and `DemoAuditLogRepository`
- Platform/demo repositories under `src/Saas/Repository`

Testing implications:

- Every repository should have tenant scope tests.
- Every update/delete should assert tenant ownership.
- Demo/session repositories should not be treated as production persistence.
- Database-backed tests should include two tenants and prove cross-tenant reads/writes are blocked.

## 3. Demo Data

Primary demo data file:

- `src/Demo/Service/OperationsDemoData.php`

Current demo data includes:

- 20 jobs with statuses `completed`, `scheduled`, `in_progress`, and `pending`
- 8 clients
- 6 quotes
- 4 invoices
- dashboard stats
- job status counts
- quote status counts
- weekly calendar labels
- unscheduled jobs derived from pending jobs
- 3 team members

Testing implications:

- Dashboard counts should be deterministic against this fixture.
- Job list/search/filter tests can use demo jobs.
- Quote/invoice edit tests should use draft/unpaid seeded records.
- AI assistant tests can use seeded jobs such as `Kitchen Faucet Repair` and `Rooftop AC Unit Repair`.

## 4. Import Data Contracts

Import services:

- `ImportSchemaRegistry`
- `ImportValidationService`
- `ImportPipelineService`
- `ImportExportAdminService`

Data types and mandatory fields:

| Import type | Required fields |
| --- | --- |
| customers | `name` |
| jobs | `title`, `client_name`, `status` |
| quotes | `quote_number`, `client_name`, `status`, `subtotal`, `total` |
| invoices | `invoice_number`, `client_name`, `status`, `total`, `due_date` |
| team | `name`, `email`, `role` |

Import lifecycle:

1. Select import type.
2. Upload/paste CSV text or file.
3. Preview/validate headers and rows.
4. Store preview in session.
5. Commit only if preview exists and is valid.
6. Track created ids.
7. Undo last import.
8. Audit import run where audit service is available.

Important negative cases:

- Unknown import type.
- Missing required headers.
- Header aliases.
- Bad email/status/date/amount.
- Empty CSV.
- Commit without preview.
- Commit invalid preview.
- Undo with wrong import id.
- CSV formula strings.

## 5. Export Data Contracts

Export supports:

- all
- clients
- jobs
- quotes
- invoices

Rules:

- Export requires `export_csv`.
- In non-demo live mode, Basic plan is blocked from export.
- Pro trial allows live export.
- CSV output prefixes unsafe cells beginning with formula/control characters.
- Export run should be auditable where audit service is available.

Test requirements:

- Verify headers for each dataset.
- Verify rows include correct merged data.
- Verify formula injection.
- Verify unauthorized roles denied.
- Verify plan gating.

## 6. Accounting Integration Boundary

Providers represented:

- Xero
- Intuit QuickBooks Online
- Sage Accounting
- Zoho Books
- MYOB
- FreeAgent
- Reckon
- SMEPlus
- FreshBooks
- Wave Accounting

Current state:

- Provider registry and settings UI exist.
- Sandbox-style validation exists.
- Platform admin accounting connection exists for site/platform admin.
- Live OAuth callbacks, token refresh, webhooks, and provider sync workers are not confirmed as implemented.

Testing plan should include:

- Provider registry completeness.
- Unsupported provider denial.
- Sync mode validation.
- Sandbox connection persistence in demo/session repository.
- Secrets not rendered.
- Platform admin-only access for platform accounting.
- Mark live provider sync as "Requires owner confirmation" unless code proves it.

## 7. Calendar Integration Boundary

Providers:

- Google Calendar
- Outlook/Microsoft calendar

Current state:

- Settings UI exists.
- Provider registry exists.
- Sync strategy documents inbound/outbound/conflict behavior.
- Live OAuth and webhook callbacks are not confirmed as implemented.

Test focus:

- Provider display.
- Role permission visibility.
- Two-way sync strategy service cases.
- Conflict when both provider and FieldOps changed.
- No conflict when only one side changed.
- Live OAuth marked as foundation unless implemented.

## 8. LLM and AI Boundary

LLM settings:

- Provider metadata exists for OpenAI, Azure OpenAI, Anthropic, Google Gemini, and custom compatible HTTPS endpoints.
- API key validation exists.
- Custom endpoint must be HTTPS.
- API keys are not stored in current implementation.

AI Assistant:

- Current assistant is local deterministic.
- Endpoint: `POST /ai-assistant/generate`
- Modes: quote, schedule, general.
- Uses local job context.
- Does not call external LLMs.
- Does not mutate data.
- Redacts obvious API keys, tokens, passwords, secrets, and long card-like numbers.

Testing requirements:

- Treat live LLM calls as missing/foundation.
- Verify no external network call is made by current assistant.
- Verify generated content is review-only.
- Verify record mutation does not happen.
- Verify prompt/detail content is redacted.

## 9. Billing, Pricing, and Subscription Boundary

Core services:

- `TradePlanCatalog`
- `TenantSubscriptionService`
- `PricingConfigurationService`
- `PlatformBillingService`
- `PlatformAdminService`

Known product rules:

- Basic plan is GBP 0.
- Pro plan is GBP 30/month.
- Pro includes 30-day free trial.
- Pricing settings support multiple currencies.
- Global discount can be active or expired.
- Tenant subscription access state gates Pro-only features.
- Platform admin can override next billing amount with reason.
- Platform billing ledger shows contract value, billed so far, remaining value, and next invoice.
- Stripe/live payments are not integrated yet.

Test requirements:

- Price display and currency formatting.
- Trial period start/extend/end.
- Grace period.
- Payment failed/active flags.
- Override validation and resuming automatic billing.
- Multi-currency discount behavior.
- No payment provider charge attempted.

## 10. Customer Portal Boundary

Portal service:

- Signed token access.
- Quote approval/decline.
- Invoice view with payment placeholder.
- Job status view.

Test requirements:

- Token signature verification.
- Expiry verification.
- Tamper resistance.
- Quote approve/decline state change.
- No login required but no unrelated data exposure.
- Payment link placeholder must not be represented as live payment.

## 11. Mobile/Offline Boundary

Current state:

- PWA manifest exists.
- Service worker and offline fallback exist.
- Offline technician workspace exists.
- Browser localStorage queue exists.
- API sync route exists.
- Capacitor wrapper config and docs exist.
- App-store production status is not approved.

Test requirements:

- Offline save.
- Sync now.
- Assigned job only.
- Tenant/user scoped local storage key.
- Mobile viewport usability.
- Service worker fallback.
- Capacitor status page visible to platform admin.