Secure analytics
Each branch manager logs in with their own role and must only see their branch's sales — while a trigger keeps daily totals up to date automatically, and a view ranks their staff.
The pieces and how they connect
This capstone wires several features into one system. A trigger on sales keeps daily_totals current with an upsert. Row-level security on both tables limits each manager to their branch. A view ranks staff using window functions, and security_invoker makes the view respect the caller's RLS policies instead of its owner's.
- Trigger: AFTER INSERT ON sales FOR EACH ROW → upsert into daily_totals.
- RLS: USING (branch = current_user) on sales and daily_totals.
- Roles: one per manager, named after the branch, with SELECT and INSERT.
- View: WITH (security_invoker = true) so policies apply to readers.
Build and test one piece at a time: the trigger with a superuser first, then RLS, then the view as each manager.