Building a High-Speed School Marketplace (Part 2): Multi-Role Security with Supabase RLS

This is Part 2 of our technical series on building a high-speed, lightweight school marketplace. In Part 1: Why We Ditched Frameworks for Vanilla JS & PWA (https://dev.to/juna_go15/building-a-high-speed-school-marketplace-why-we-ditched-frameworks-for-vanilla-js-pwa-27dl), we discussed how we optimized the frontend for budget mobile devices. Today, we look at the backend architecture.

When planning the architecture for Aksara-Mart—our digital marketplace for vocational school canteens and cooperatives—we faced a fundamental infrastructure question:

Do we really need to build, maintain, and scale a traditional custom backend server (like Node.js/Express, Python, or Go) just to shuttle data between the browser and the database?
For a school environment, maintaining a traditional backend comes with real trade-offs:

1. Infrastructure & Hosting Overhead: A dedicated server needs 24/7 uptime monitoring, memory management, and scaling configuration for peak recess traffic.
2. Maintenance Burden: API routes, middleware controllers, and ORM abstractions require continuous patching and maintenance.
3. Unnecessary Latency: Every simple query (browsing the lunch menu, checking an order status) has to make an extra hop through an intermediate API server layer. We wanted an architecture that could minimize backend server workload while keeping running costs close to zero. That led us to Supabase. By allowing our frontend JavaScript to communicate directly with PostgreSQL via auto-generated APIs and WebSockets, we could offload the bulk of routine read queries directly to the client. However, letting client-side JavaScript query a database directly sounds like a security nightmare—unless you have a bulletproof authorization layer built into the database itself. That is where PostgreSQL Row Level Security (RLS) becomes the cornerstone of the entire system.

1. The Architecture: Direct Client Queries Without the Middleman In a traditional web application, every user request passes through three layers:

[ Browser (Client) ] ──▶ [ Custom API Server (Express/Node) ] ──▶ [ Database ]

The API server spends most of its CPU cycles doing mundane tasks: authenticating tokens, verifying if a user is allowed to read a row, running the query, and formatting JSON.
With Supabase and PostgreSQL RLS, we simplified the architecture into a direct, high-performance two-tier model:

[ Browser (Vanilla JS Client) ] ──────────────▶ [ Supabase PostgreSQL + RLS ]
• Direct catalog SELECT                         - Kernel-level row authorization
• Direct order history lookup                   - Instant query evaluation (< 3ms)
• Real-time WebSocket subscriptions             - Atomic RPC stored procedures

The Advantages:
• Zero Backend Server Maintenance: No intermediate server processes to crash, restart, or run out of memory during a rush.
• Minimal Compute Overhead: PostgreSQL handles filtering and JSON serialization natively in C, which is vastly faster than parsing objects in Node.js.
• Direct Client Queries: Frontend JavaScript can fetch exactly the columns it needs, when it needs them, directly from the client.

1. The Core Problem: Securing Direct Database Access If frontend JavaScript talks directly to the database using a public API key, what prevents a student from querying other users' private orders or inspecting canteen cost margins? This is where Row Level Security (RLS) comes in. Instead of writing authorization logic in backend middleware (if (req.user.id !== order.user_id) return res.status(403)), the authorization rules are compiled directly into PostgreSQL table definitions. • Without RLS: A client query supabase.from('orders').select('*') returns every order in the database. • With RLS: PostgreSQL automatically injects an internal filter into the query plan:

-- Automatically enforced by PostgreSQL on every query
WHERE email_customers = auth.jwt() ->> 'email'

Even if a user inspects the network tab and writes a custom script asking for all records, the database engine guarantees they only receive the rows they are explicitly permitted to see.

1. Managing Roles Across the School Ecosystem A school marketplace serves several distinct groups with different permissions: | Role | Access Scope | How Queries Are Handled | | :--- | :--- | :--- | | siswa (Students) | Browse active products, view own order history. | Direct client SELECT constrained by RLS. | | guru (Teachers) | Same as students, with priority fulfillment flags. | Direct client SELECT constrained by RLS. | | kasir (Cashiers) | View all active incoming orders, print receipts. | Direct client SELECT with staff privileges; no direct price/stock editing. | | manajer (Managers) | Manage catalog items, update stock, view revenue. | Full CRUD permissions on product tables via RLS. | | admin (System Admins) | System-wide configuration and role management. | Full administrative access. | The admin_role Table To keep role checks clean and reliable, we maintain a dedicated role table in PostgreSQL:

CREATE TABLE public.admin_role (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
email TEXT UNIQUE NOT NULL,
role TEXT NOT NULL,
status TEXT NOT NULL DEFAULT 'active'
);
ALTER TABLE public.admin_role ENABLE ROW LEVEL SECURITY;

Practical Gotchas We Addressed:

1. Case-Insensitive Emails: Mobile keyboards often capitalize the first letter of an email ([email protected]). Always use lower(trim(email)) in policies to prevent login lockouts.

2. Dual-Spelling Tolerance: In bilingual environments, staff may enter roles in English or Indonesian (manager vs manajer, cashier vs kasir). We check role aliases with IN ('manager', 'manajer', 'cashier', 'kasir') so minor spelling differences don't break authentication.

3. The Separation of Concerns: Direct Reads vs Server-Side Writes
   Allowing frontend JavaScript to query the database directly works brilliantly for reads, but transactional writes require a different boundary.
   A. Direct Client Reads (Safe via RLS)
   Frontend JavaScript directly reads from the database for:
   • The product catalog:
   

// Direct client query in skripmart.js
const { data: products } = await supabase
.from('product_list')
.select('id, name, price, stock, image_url')
.eq('status', 'active');

RLS ensures that wholesale costs (modal_price) and archived items are filtered out before data leaves PostgreSQL.
• Order tracking:

// Customers only receive their own orders
const { data: myOrders } = await supabase
.from('orders')
.select('*')
.order('created_at', { ascending: false });

B. Controlled Server-Side Writes (Via Database RPC)
While reading data directly from JavaScript is safe, creating orders and modifying stock should never happen via direct client INSERT or UPDATE:

// ❌ ANTI-PATTERN: Never allow direct client inserts for financial transactions
await supabase.from('orders').insert({
total_price: clientCalculatedTotal, // Client could tamper with this value!
items: cartItems
});

If we allowed direct client inserts on the orders table, a user could theoretically alter prices or bypass stock limits.
Instead, we follow a simple architectural rule:

Frontend JavaScript reads data directly using RLS.
Frontend JavaScript writes transactions via PostgreSQL Stored Procedures (RPC).
When a student checks out, the client invokes an RPC procedure (v2_create_order):

// ✅ SECURE: Transaction logic runs atomically inside PostgreSQL
const { data, error } = await supabase.rpc('v2_create_order', {
p_customer_name: userProfile.name,
p_delivery_type: deliveryOption,
p_items: cartPayload
});

Inside this procedure, the database recalculates prices from the official catalog, checks remaining stock, decrements inventory, and creates the order records in a single atomic transaction.

1. What We Gained from This Architecture By offloading query processing directly to the client and database engine:
2. Zero Backend Maintenance: We do not maintain or pay for a custom Node.js/Express server layer.
3. Sub-3ms Authorization Overhead: RLS policy checks happen natively within PostgreSQL's query optimizer with virtually no latency penalty.
4. High Reliability During Traffic Spikes: When hundreds of students browse the menu simultaneously during recess, Supabase and Cloudflare serve the requests directly without choking an intermediate web server.
5. Complete Data Privacy: Every customer and staff member only receives data that belongs to their assigned role.

What's Next in Part 3?
With direct reads secured by RLS and client-side queries running smoothly, we still had to solve one critical concurrency problem:
During the 15-minute school break, dozens of students click "Checkout" at the exact same millisecond for the last few items in the canteen.
How do we prevent race conditions and overselling without a heavy application server?
In Part 3: Atomic Checkouts & Zero Overselling with PostgreSQL RPC, we will break down how we implemented pessimistic row locking (FOR UPDATE) inside PostgreSQL to handle high-concurrency checkout spikes in under 42 milliseconds!