POS Service Overview

Business Case

The current Pivot architecture uses Firebase Functions for all backend logic, including POS integrations. While this works for simple CRUD operations, POS data sync presents unique challenges:

1. Volume: Thousands of transactions per day per venue
2. Historical Backfills: Need to import months of data on initial setup
3. Analytics: Complex queries across time periods, employees, categories
4. Independence: POS sync shouldn't affect main app performance

A dedicated service solves these challenges while enabling future scalability.

---

Goals

1. Decouple POS logic from Firebase Functions monolith
2. Enable long-running syncs without timeout constraints
3. Optimize for analytics with columnar storage
4. Reduce costs for write-heavy, read-light workloads
5. Establish pattern for future standalone services

---

Why BigQuery Over PostgreSQL?

This is a critical architectural decision. Here's the analysis:

Workload Characteristics

Characteristic	POS Data Pattern	Best Fit
Write volume	High (100s-1000s/day/venue)	Both
Read volume	Low (dashboard queries, reports)	BigQuery
Query pattern	Aggregations, time-series	BigQuery
Transaction needs	None (append-only)	BigQuery
Schema changes	Rare	Both
Joins with app data	Minimal	PostgreSQL edge
Historical data	Large (months/years)	BigQuery

Cost Comparison

PostgreSQL (Cloud SQL):

- db-f1-micro: ~$10/month (tiny, will need upgrade)
- db-g1-small: ~$25/month (minimum production)
- db-n1-standard-1: ~$50/month (comfortable)
- Storage: $0.17/GB/month
- Connection pooling: Additional complexity
- Backups: Additional cost

BigQuery:

- Storage: $0.02/GB/month (10x cheaper)
- Active storage: $0.02/GB/month
- Queries: $5/TB scanned (first 1TB/month free)
- Streaming inserts: $0.01/200MB
- No connection limits
- No server management

For POS workload (100 venues, 1000 txns/day each):

• ~3 million rows/month
• ~1GB new data/month
• ~10 dashboard queries/day

PostgreSQL cost: $25-50/month + management overhead BigQuery cost: ~$2-5/month (mostly storage)

Operational Comparison

Aspect	PostgreSQL	BigQuery
Connection limits	Yes (max_connections)	No
Connection pooling	Required (PgBouncer)	Not needed
Scaling	Manual (resize instance)	Automatic
Maintenance window	Required	None
Backup management	Manual or scheduled	Automatic
Cold start	N/A (always running)	N/A (serverless)
Schema migrations	Required	Flexible (STRUCT, arrays)

Query Capabilities

BigQuery advantages for POS analytics:

-- Aggregate sales by employee, day, category (instant on TB of data)
SELECT
  employee_id,
  DATE(transaction_time) as sale_date,
  category,
  SUM(amount) as total_sales,
  COUNT(*) as transaction_count
FROM `pivot-inc.pos_data.sales`
WHERE venue_id = 'xyz'
  AND transaction_time BETWEEN '2025-01-01' AND '2025-01-31'
GROUP BY 1, 2, 3
ORDER BY sale_date, total_sales DESC

-- Time-series analysis with window functions
SELECT
  employee_id,
  week,
  weekly_sales,
  LAG(weekly_sales) OVER (PARTITION BY employee_id ORDER BY week) as prev_week,
  weekly_sales - LAG(weekly_sales) OVER (...) as change
FROM (
  SELECT
    employee_id,
    DATE_TRUNC(transaction_time, WEEK) as week,
    SUM(amount) as weekly_sales
  FROM `pivot-inc.pos_data.sales`
  GROUP BY 1, 2
)

PostgreSQL would require:

• Careful indexing
• Query optimization
• Potentially materialized views
• Connection pool management

When PostgreSQL Would Be Better

PostgreSQL would be preferred if:

• ❌ Need complex transactions (we don't - append-only data)
• ❌ Need foreign keys to main app (we don't - separate data store)
• ❌ Need sub-second query latency (we don't - dashboards are fine with 1-2s)
• ❌ Already have PostgreSQL expertise (we use BigQuery in pivot-kpi)

Recommendation: BigQuery

BigQuery is the right choice because:

1. Cost: 5-10x cheaper for this workload
2. Operations: Zero management overhead
3. Scale: Handles growth from 10 to 10,000 venues without changes
4. Analytics: Built for the exact queries we need
5. Consistency: Matches pivot-kpi architecture
6. Streaming: Native support for real-time inserts

---

Success Metrics

Technical Metrics

Metric	Target	Measurement
Sync latency	<15 minutes	Time from POS transaction to BigQuery
Sync success rate	>99.5%	Successful syncs / total attempts
Query latency	<5 seconds	P95 dashboard query time
Data accuracy	>99.9%	Spot-check against POS reports
Uptime	>99.9%	Cloud Run service availability

Business Metrics

Metric	Target	Measurement
Customer adoption	>80%	% of customers using POS integration
Setup time	<30 minutes	Time to connect new POS
Support tickets	<5%	POS-related tickets / total
Data completeness	>99%	Expected vs actual records

---

Risk Assessment

Technical Risks

Risk	Impact	Probability	Mitigation
Toast API changes	High	Low	Version monitoring, integration tests
BigQuery query costs spike	Medium	Low	Query caching, cost alerts
Sync failures undetected	High	Medium	Alerting, monitoring dashboard
Token refresh race conditions	High	Medium	Single refresh source (production cron)

Business Risks

Risk	Impact	Probability	Mitigation
Toast partner rejection	High	Low	Apply early, prepare documentation
Customer confusion (new flow)	Medium	Medium	Clear documentation, support training
Data discrepancies	High	Low	Validation layer, reconciliation reports

---

Timeline

Phase 1: Foundation (Target: 2 weeks)

• Service scaffold with Cloud Run
• BigQuery dataset and initial tables
• Toast authentication flow
• Basic health endpoints

Phase 2: Core Sync (Target: 2 weeks)

• Employee roster sync
• Time clock data sync
• Sales per employee
• Tips per employee

Phase 3: Hardening (Target: 1 week)

• Cloud Scheduler integration
• Error handling and retries
• Logging and monitoring
• Alerting setup

Phase 4: Production (Target: 1 week)

• Beta testing with 2-3 customers
• Documentation and runbooks
• Full production rollout

---

Dependencies

External

• Toast Partner API access (apply if not already approved)
• BigQuery dataset in pivot-inc project

Internal

• Firebase RTDB access for company settings
• Service account with BigQuery write permissions
• Secret Manager for API credentials

---

Out of Scope (Initial Release)

• Real-time webhooks from Toast (future enhancement)
• Two-way sync (pushing schedules to POS)
• Historical data migration from existing Firebase sync
• UI for POS connection (use existing Pivot app flow)