Week 3: Database Foundation — Nov 9 – 15, 2024
TL;DR: databaseservices for customers, properties, and jobs are live. The data layer is parameterized, tenant-isolated, and ready for the application layer.
Highlights This Week
- Built PostgresService — the central database abstraction layer
- Implemented customer, property, and job table schemas
- Added parameterized query patterns to prevent SQL injection
The PostgresService Pattern
Rather than an ORM, we chose a thin service layer over pg (node-postgres). Every query is parameterized ($1, $2) and every query includes WHERE tenant_id = $1. This pattern gives us full SQL control while maintaining injection safety and tenant isolation.
Schema Design
The schema reflects the field service domain: Customers own Properties, which have Jobs scheduled against them. Each entity has audit columns (created_at, updated_at, created_by) and soft-delete support (deleted_at).
What’s Next
Express routes to expose these services as REST endpoints.