SmartStore AI — Phase 6 Implementation Guide

RBAC & Multi-Store Isolation

This phase adds two independent layers of access control — and proves, with a real test against a real database, that they're actually independent, not redundant.

What got built

backend/app/rbac.py              — ROLE_PERMISSIONS map + require_permission()
backend/app/db_context.py         — set_store_context(), the RLS session-variable setter
backend/app/api/products.py        — GET /products/{id}/cost-price, demonstrating both layers together
backend/alembic/versions/..._enable_row_level_security_on_products.py
backend/tests/test_rbac_rls.py

Updated: app/api/ask.py (now requires auth + permission), app/config.py + app/database.py
(split into an admin engine and a restricted, RLS-respecting app engine)

The most important thing in this phase: two layers, proven independent

test_employee_cannot_view_other_store_product_cost_price_even_with_correct_permission is the test that matters most here. It gives a store employee the correct role (RBAC passes — they're permitted to view cost prices) but requests a product belonging to a different store than the one they're scoped to. RBAC alone has no way to catch this — it only knows about roles, not which store a specific row belongs to. RLS is what actually blocks it, returning a 404 instead of another store's cost data. This is Volume 4, Chapter 3's "defense in depth, not redundancy" principle, verified against a real Postgres instance, not just asserted.

Two real bugs this phase caught (both genuinely worth knowing about)

1. Superusers silently bypass Row-Level Security — always, with no exception. The smartstore role created back in Phase 1 is a superuser, which meant my first attempt at testing RLS "worked" (returned all rows) regardless of the policy — not because the policy was wrong, but because RLS doesn't apply to superusers or table owners at all, full stop. The fix: a real, separate non-superuser role (smartstore_app) that the running application actually connects as for every request — app/database.py now has two engines: engine (the admin/owner credential, used by Alembic, seed.py, ingest.py — batch jobs that legitimately need cross-store access) and app_engine (the restricted role, used only by get_db(), which every per-request handler depends on). If you ever "test" RLS and it seems to do nothing, check which role you're actually connected as first.

2. Running the full test suite together (not just one file at a time) surfaced two real regressions a partial run hid: - Phase 3's /ask test broke the moment Phase 6 added an auth requirement to that same endpoint — a real example of why running the entire suite, not just the file you're currently working on, matters before considering a phase done. - Several tests commit() real rows with fixed emails/UIDs ("new@example.com", "employee-a", etc.) so that the FastAPI TestClient's separate database connection can actually see them — but that same fixed data collides with itself on a second run, since nothing was cleaning it up. The fix: every test that needs to commit (for cross-connection visibility) now generates a unique identifier with uuid.uuid4() rather than reusing a fixed string — confirmed by running the full suite twice in immediate succession and getting 21/21 both times.

Setting up the restricted role yourself

CREATE ROLE smartstore_app LOGIN PASSWORD 'smartstore_app';
GRANT CONNECT ON DATABASE smartstore TO smartstore_app;
GRANT USAGE ON SCHEMA public TO smartstore_app;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO smartstore_app;
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO smartstore_app;

Run this once against your local Postgres (and again in every environment — staging, production RDS — as part of that environment's setup, not just locally).

Verified test results (the full suite, run twice in a row)

21 passed in 11.26s    (first run)
21 passed in 2.65s     (immediate second run — confirms idempotency, not luck)

What's next

Phase 7 — Agent Layer & Tool Calling adds the ReAct loop on top of this same auth/RBAC foundation — every tool call an agent makes still goes through the same permission and store-scoping checks built here.