When I set out to build PharmaPOS, the vision was straightforward: build a blazing-fast, cloud-based POS and inventory management suite tailored for modern pharmacy stores.

However, right from day one, the biggest architectural challenge was Multi-Tenancy.

Think about it: When dozens of independent medical stores use your SaaS platform, Store A ka data kisi bhi haal me Store B ko leak nahi hona chahiye. Whether it is medicine stocks, sensitive customer phone numbers, or daily GST sales summaries, client-side filtering is never enough. We needed guaranteed, 100% database-level isolation.

In this article, I'll walk through how I architected a secure multi-tenant foundation using PostgreSQL Row-Level Security (RLS), handled atomic real-time inventory deductions, and built automated batch-expiry alerts with Supabase.

1 Database-Level Multi-Tenancy (Row-Level Security)

In multi-tenant SaaS architecture, you generally have two main approaches:

  1. Database-per-tenant: Provisioning an isolated database instance for each pharmacy. (Clean isolation, but extremely expensive and complex to maintain at scale).
  2. Shared Database with Tenant ID + RLS: A single PostgreSQL database where every table includes a tenant_id foreign key. (Cost-effective, highly scalable, and rock solid).

We chose the shared database model combined with PostgreSQL's native Row-Level Security (RLS). Instead of writing manual WHERE tenant_id = '...' filters on every single frontend query (jisme human error ka chance rehta hai), PostgreSQL engine itself intercepts each query and enforces tenant boundaries automatically.

-- 1. Enable RLS on the medicines table
ALTER TABLE medicines ENABLE ROW LEVEL SECURITY;

-- 2. Policy: Authenticated users can ONLY query their own pharmacy's data
CREATE POLICY "Tenant Isolation Policy" ON medicines
FOR ALL
TO authenticated
USING (
tenant_id = (auth.jwt() -> 'app_metadata' ->> 'tenant_id')::uuid
);

💡 Key Takeaway: By embedding the tenant_id inside the authenticated user's JWT metadata, seedha database engine level pe security enforce ho jati hai. Even if an attacker inspects and tampers with client-side API requests, PostgreSQL returns an empty set for unauthorized records.

2 Real-Time Stock Deductions Without Race Conditions

At a busy pharmacy billing counter, speed and accuracy are everything. When cashiers scan medicines rapidly during peak hours, concurrent checkouts can easily create race conditions and result in negative inventory counts.

To eliminate race conditions, client-side calculations pe rely karne ki jagah, we execute an atomic PostgreSQL stored function that locks and deducts stock inside an isolated transaction:

CREATE OR REPLACE FUNCTION process_pharmacy_sale(
p_tenant_id UUID,
p_items JSONB
) RETURNS VOID AS $$
DECLARE
item RECORD;
BEGIN
FOR item IN SELECT * FROM jsonb_to_recordset(p_items) AS x(medicine_id UUID, quantity INT) LOOP
-- Atomic deduction with real-time stock verification
UPDATE medicines
SET stock_quantity = stock_quantity - item.quantity
WHERE id = item.medicine_id
AND tenant_id = p_tenant_id
AND stock_quantity >= item.quantity;

IF NOT FOUND THEN
RAISE EXCEPTION 'Stock insufficient for item %', item.medicine_id;
END IF;
END LOOP;
END;
$$ LANGUAGE plpgsql;

3 Batch Expiry Tracking & Automated Alerts

In pharmaceutical retail, selling an expired batch is not just a loss—it is a severe compliance risk. Every medicine batch in PharmaPOS is mapped with a batch_number and expiry_date.

We implemented a dynamic traffic-light visual tracking system:

4 Keyboard-First POS Workflow with React & Framer Motion

Cashiers need maximum throughput during rush hours. We designed the POS interface so that mouse touch kiye bina, staff can perform an entire transaction using intuitive keyboard shortcuts:

Final Thoughts

Building a production-ready SaaS taught me that keeping the frontend simple and letting PostgreSQL handle heavy-lifting transactional integrity is the cleanest architectural pattern. It keeps latency sub-50ms, prevents edge-case bugs, and guarantees 100% data security across tenants.

Want to see the live project in action? Visit PharmaPOS.org or reach out to me at hello@vipulxdev.com.