Database structure, designed before a single screen.
Good software starts with a good data model. Scroll to watch one take shape for a field-service app, then the functions and API on top. Every action is validated server-side.
- Data model
- Relations
- Tenant isolation
- Indexes and migrations
- Functions and API
Data model. We map your business into tables first: what a customer, a job and a booking are, and which facts each one owns.
Relations. Primary and foreign keys tie the tables together, and constraints stop bad data at the door: no booking without a job, no orphaned invoice.
Tenant isolation. Every row carries its workspace. On Supabase, row-level security policies in Postgres keep each workspace to its own rows.
Indexes and migrations. Indexes keep the lookups you run most fast as the data grows. Every schema change is a versioned migration, reviewed and replayable.
Functions and API. Server functions validate every input before they write, and the same logic is exposed as a clean, versioned REST API.
Whether it's small internal software or large SaaS production, we can hold the architecture and infrastructure.
A database diagram for a field-service app on demo data. Six tables, workspaces, users, customers, jobs, invoices and bookings, are linked by primary and foreign keys. Every table carries a workspace_id, and row-level security hides other workspaces' rows. Indexes sit on customer_id, email and starts_at, and four versioned migrations build the schema. A server function, Create booking, validates input, checks access, writes in one transaction and sends a confirmation, and the REST API exposes bookings, customers and invoices under version 1.
The decisions underneath
The database, the functions on top of it and the API that exposes them. We plan each from the first scope, and build in the ones your product calls for.
Database
We build on Supabase, Convex, Neon or a managed Postgres service in your own cloud, such as Amazon RDS or Azure Database for PostgreSQL, whichever fits the product.
- Data models
- We start from your business, not a template: the things you track, who owns them and how they relate, drawn up as tables before a screen is built.
- Relations and constraints
- Primary and foreign keys link every record to what it belongs to, and constraints in the database itself refuse bad data, like a booking for a job that does not exist.
- Tenant isolation
- Every row carries its workspace. On Supabase and Postgres, row-level security policies enforce the split inside the database itself, not just in the screens. On Convex, which has no row-level security of its own, the same check runs in every query and mutation.
- Migrations and versioning
- On Postgres, every schema change is a versioned migration kept with the code: reviewed, tested on a preview database, then applied the same way everywhere. On Convex, the schema is defined in code and versioned the same way. Either way, the structure never drifts between environments.
- Indexes
- We index the lookups your product runs most, like today's bookings or a customer's jobs, so screens stay fast as the data grows.
- Backups and recovery
- Automated backups and point-in-time recovery on Supabase, so the database can be restored to a moment before something went wrong.
- Encryption
- On our default Vercel and Supabase stack, data is encrypted in transit (TLS 1.2+) and at rest (AES-256).
Functions
Every action that changes data runs on the server, never trusted to the screen.
- Server functions
- Each one validates its input, checks the user's role and workspace, then writes, in one transaction where it matters, so a change is saved in full or not at all.
- Edge functions
- Small functions that run close to your users for webhooks, confirmations and integrations, such as Supabase Edge Functions on Deno.
- Background jobs
- Imports, reminders and syncs run on a schedule or a queue, away from the screen, and are written to retry safely without doing the same work twice.
- AI in the product
- We integrate the right model for each job: frontier LLMs from Anthropic, OpenAI, Google, Meta and Mistral, or local small language models where privacy or cost calls for it.
APIs
A clean REST API, or whatever API structure your other tools need.
- Clean REST endpoints
- Named the way your business talks, customers, jobs and bookings, with standard methods, clear errors and documentation another developer can pick up quickly.
- Versioned
- Endpoints live under a version such as v1, so we can improve the API without breaking anything already plugged into it.
- Same rules as the app
- The API calls the same server functions as the app, with the same validation, login and roles. Nothing can do through the API what it could not do on screen.
Your software, as a clean API.
We set up REST APIs for software so it can talk back and forth with everything, in clean, diagnosable formats.
- 201 Created
POST /v1/customers48 ms - 422 Unprocessable
POST /v1/bookings9 msstarts_at must be in the future - 201 Created
POST /v1/bookings61 ms - 200 OK
booking.created112 ms - 201 Created
POST /invoices230 ms - 200 OK
POST /v1/webhooks/payments37 ms - 404 Not Found
GET /v1/bookings/bkg_9z1x6 msno booking with id bkg_9z1x - 200 OK
GET /v1/bookings?date=today34 ms
A demo, on demo data, of software talking to six other tools through its REST API. A CRM creates a customer. A booking form sends a time in the past and is refused with the reason, starts_at must be in the future, then succeeds when sent again. The software posts a booking event to team chat and raises an invoice in accounting software, a payment platform reports a deposit, an AI assistant asks for a booking that does not exist and gets a 404 naming the id, then reads today's bookings with the same login and roles as the app. Each call is logged with its status, timing and request id.
Let's build it.
Tell us what you need and we'll show you how we'd build it.
Or email jack.west@sparkintegrations.com.au