ToolUp.biz
← All case studies

Case Study 02 - Cinema / SaaS

BOR Buddy

Multi-tenant box-office returns for independent cinemas and mixed-use venues

In Plain English

Independent cinemas have to file box-office returns every week to distributors like Paramount and Trafalgar, and to industry data bodies like Numero and Comscore. Most venues do it by hand in spreadsheets, one email at a time, and hope nothing gets missed.

So I built a subscription app that takes the venue's ticketing export, parses it once, and fans the numbers out to every receiver in the format they each want. One venue, many distributors, one reconciled pipeline - and every figure in every outbound email traces back to the exact ticket line it came from.

The big picture

A multi-tenant reporting spine that sits between a venue's ticketing system and every party that needs its numbers - distributors (Paramount, Trafalgar, ROH, NT Live), industry data (Numero, Comscore, Pearl & Dean), and the venue's own approvers. The same app adapts its surface for a super admin managing the whole catalogue, a booking agent maintaining industry IDs and regional distributor mappings, a venue admin managing screens and capacities, a venue approver signing off weekly returns, and a read-only viewer. Every return is source-traceable back to the ticketing export it came from.

Ingestion & catalogue

  • Ticketsolve email ingest - venues forward the 'Detail by event and ticket price' PDF export; the system parses it (text-based, single-day enforced with a plain-English rejection reason for multi-day files), extracts per-performance admits, Full / Concession / Comp splits, gross / net / VAT, and idempotently upserts daily BOR rows keyed on (tenant, date, film).
  • Reserved-section handling - reserved-section rows are treated as comps per operator confirmation, so paid-vs-comp splits reconcile against the header totals.
  • Films catalogue with EDI numbers - global film IDs live on film_edi_catalogue / distributors_catalog; per-receiver IDs (MovieXchange, Maccsbox, Numero theatre key, etc.) live in destination_distributor_map / destination_venue_map so a change of receiver doesn't touch the film record.
  • Films import wizard - CSV imports with field mapping, EDI number validation, and a booking-agent review queue for unmatched titles and regional distributor assignments.

Returns & approval workflow

  • Daily flashes - one email per day of business to the venue's flash-reporting receivers (Numero + Comscore this week, API adapter for Numero next). Includes admits, capacity, occupancy %, gross/net/VAT and the ticket-type split.
  • Weekly BOR - one email + CSV per distributor, carrying only that distributor's titles. Savage House to Paramount, NT Live / ROH to Trafalgar Releasing, each with its own template and attachment naming convention.
  • Zero-day auto-submit - a nightly cron seeds zero-value daily rows for days with no business so gaps in the flash stream are explicit, not silent. Real returns replace the seeded row when the Ticketsolve export lands.
  • Approval gate - dailies land as awaiting_approval; the venue approver signs them off before dispatch. Once approved or sent, ingest refuses to clobber the row - a signed-off return is immutable.
  • Send-history table - every dispatch (which distributor, which channel, which attachment, which response) is logged and re-queryable, with a retry hook for transient failures.

Governance & multi-tenancy

  • Role-based access - Super Admin (edit all), Booking Agent (edit industry IDs, approve regional distributors), Venue Admin (edit screens and capacities, notify BA/SA), Venue Approver (approve BORs), Viewer (read only). Roles held in a dedicated user_roles table with a has_role security-definer function - never on the user profile, to avoid privilege-escalation via row updates.
  • Row-level security on every tenant table - a venue can only see its own films, performances, dailies, weeklies, distributor mappings, send history, inbound emails and audit rows. Booking agents get scoped cross-tenant read for the review queues they own.
  • Tenant secrets - receiver credentials (SMTP, Numero API keys, distributor endpoints) are stored as per-tenant secrets, never hardcoded, never shared across tenants.
  • Source traceability - every BOR row carries inbound_email_id, source parser key, VAT rate applied and per-screening breakdown. Any figure in any outbound email can be traced back to the exact Ticketsolve line it came from.

Adapter framework

  • Neutral payloads, per-receiver adapters - a single FlashDailyPayload shape is produced once; each receiver (Numero session, Comscore email, Pearl & Dean ad-playback, future BFI / MPAA / EU observatory feeds) has its own adapter file that maps the neutral payload to that receiver's wire format. Nothing outside src/lib/destinations/adapters/<receiver>.ts knows about a specific receiver.
  • Transport-agnostic dispatch - the same adapter output can be emailed, POSTed to an API, or dropped as a signed CSV, chosen per destination in configuration.
  • Signed webhooks - public receiver hooks live under /api/public/* with HMAC signature verification before any write.

Integrations

  • Lovable Cloud - auth, Postgres with RLS, server functions, storage.
  • Ticketsolve - inbound PDF ingest via forwarded email.
  • Numero, Comscore, Pearl & Dean - outbound flash and session reporting (email & automated API sends).
  • Distributor mail - outbound weekly BORs to Paramount, Trafalgar Releasing, ROH, NT Live, etc., on the venue's own sending domain.
  • Stripe - subscription billing per tenant, per screen tier.

What makes it different

Most cinema back-office tools are either single-tenant desktop software or a proprietary bolt-on to one ticketing system. BOR Buddy is subscription-native, ticketing-system-agnostic (Ticketsolve today, Spektrix / Savoy / Vista / Monad next via the same adapter pattern), and treats the venue to distributor to industry-data chain as one reconciled pipeline instead of three parallel spreadsheets. The receiver adapters mean a new distributor or a new data body is a config change, not a rewrite.

Back to case studiesAbout AlexHome