Why we never let the frontend touch the database directly

The rule

Our rule is short: a browser, phone or bot never holds a database connection and never sends SQL. Every read and write goes through a REST endpoint, and every endpoint that touches data calls a named stored procedure. There are no ad-hoc queries in controllers, and no ORM generating SQL nobody reviewed.

This sounds conservative, and it is. It is also the single decision that has saved us the most time on long-running client systems — operations portals, dairy collection platforms, multi-tenant SaaS — where the same data is read by a React dashboard, a Flutter field app and a WhatsApp flow at the same time.

Why not let the frontend query directly?

Backend-as-a-service tools make it tempting to point a frontend straight at the database with row-level security rules. For a prototype, that is fine. For a system a business runs on, it spreads business rules across every client that touches the data.

• Permissions end up duplicated: once in the React app, again in the mobile app, again in the bot — and they drift.
• Schema changes break clients you forgot existed, because every client knows table and column names.
• Audit trails are partial, because nothing forces every write through one path.
• Performance tuning means shipping new app builds, because the queries live in the apps.

How the layers are split

1. Frontend (React on web, Flutter on mobile, WhatsApp flows): renders state and calls the API. It knows endpoints and DTOs, never tables.
2. API (ASP.NET Core 8 or Node.js): authenticates the caller, checks role and tenant, validates input, then calls exactly one stored procedure per operation.
3. Database (MSSQL): stored procedures own the data logic — joins, transactions, consistency checks and the audit insert.

A typical endpoint is deliberately boring. The controller maps the request to parameters, calls the procedure, and maps the result set back to a response object.

[HttpPost("orders")]
[Authorize(Roles = "Operator,Admin")]
public async Task<ActionResult<OrderDto>> CreateOrder(CreateOrderRequest req)
{
    var tenantId = User.GetTenantId();
    var order = await _db.QuerySingleAsync<OrderDto>(
        "dbo.usp_Order_Create",
        new { tenantId, req.CustomerId, req.Items, CreatedBy = User.GetUserId() },
        commandType: CommandType.StoredProcedure);
    return CreatedAtAction(nameof(GetOrder), new { id = order.Id }, order);
}

What stored procedures buy us

• One place for data rules. Stock checks, duplicate prevention and status transitions live in the procedure, so every client gets the same behaviour.
• Atomic multi-step writes. An order, its line items and its audit row are written in one transaction, or not at all.
• Tuning without redeploying apps. Indexes and query plans change inside the procedure; the API contract stays the same.
• Least-privilege access. The API's database login can execute procedures but cannot read tables directly.
• Reviewable SQL. Every query that runs in production has a name, an owner and a code review.

REST contracts come before screens

Because the frontend only knows the API, we design the contract first. After the PRD is signed off, we write the database schema and the list of endpoints with request and response shapes. Frontend and backend teams then build in parallel against that contract, and mobile, web and WhatsApp teams never wait on each other.

Contracts are versioned (/api/v1/…). When a breaking change is needed, v2 ships beside v1 and older mobile builds keep working until users update — something that matters when field staff run app versions that are months old.

The trade-offs

This approach costs something. Simple CRUD screens take a little longer because every operation needs a procedure. Developers need to be comfortable in SQL, not just in an ORM. And it is a poor fit for rapid throwaway prototypes.

For systems expected to live for years, with several clients and real money or inventory moving through them, we have found the extra hour per endpoint pays for itself the first time a rule changes and it changes in exactly one place.

When we relax the rule

• Reporting and analytics: read-only replicas feed BI tools directly.
• Search: a dedicated search index is queried through its own API, not through SQL.
• AI retrieval: vector stores sit beside the database and are reached through the API like everything else.

Building a portal or app that several teams or channels will share? Send us a one-paragraph brief and we will sketch the API contract with you on the discovery call.