Cash Flow Dashboard

Problem

Running a multi-location restaurant group means cash flows in from a dozen different directions. Each location has its own POS system spitting out transaction data, but none of them talk to each other. The operators I worked with were spending hours every week manually pulling reports, copying numbers into spreadsheets, and trying to figure out which locations were profitable and which were bleeding money.

The real pain point wasn't just the manual work. It was the delay. By the time anyone had a clear picture of their cash position, the numbers were already stale. Decisions about inventory, staffing, and expansion were being made on week-old data. For a business where margins are razor-thin, that kind of lag can mean the difference between a good month and a bad one.

They needed real-time financial visibility across every location, with data that updated automatically and a dashboard that made the numbers actually useful.

Approach

I built a centralized dashboard that pulls POS transaction data from multiple restaurant locations, normalizes it, and presents a unified view of cash flow across the entire group. The system runs as a containerized service with a clean REST API that each POS location can push data to.

TypeScript was the obvious choice for end-to-end type safety. From the API request handlers through the data access layer to the response objects, every data shape is explicitly typed. Drizzle ORM handles the PostgreSQL interactions with schema definitions that double as the source of truth for the database structure. No runtime surprises, no "what fields does this table have again?" moments.

The whole thing runs in Docker containers, which keeps the deployment story simple and consistent. Same environment locally, in staging, in production. The PostgreSQL database uses a multi-tenant schema design where each restaurant group's data is logically isolated but physically co-located for query performance.

Architecture

Data flows from individual POS systems at each restaurant location into the central API server. The API normalizes incoming transaction data, validates it against the expected schema, and persists it to PostgreSQL through Drizzle ORM. The dashboard reads from the same database, aggregating transactions across locations and time periods to generate the cash flow views.

Key Technical Details

The Drizzle ORM schema uses a multi-tenant pattern with a groupId column on every transactional table. Queries always scope to a specific group, and a middleware layer enforces this so no endpoint can accidentally leak cross-group data.

const transactions = pgTable("transactions", {
  id: serial("id").primaryKey(),
  groupId: integer("group_id").notNull().references(() => groups.id),
  locationId: integer("location_id").notNull(),
  amount: numeric("amount", { precision: 12, scale: 2 }).notNull(),
  type: varchar("type", { length: 20 }).notNull(),
  timestamp: timestamp("timestamp").defaultNow().notNull(),
});

The data sync uses a polling-based approach rather than webhooks. Most POS systems in the restaurant world don't support outbound webhooks reliably, so the API exposes a batch upload endpoint that location managers can configure their POS exports to hit on a schedule. Each batch is processed idempotently using a combination of location ID and transaction timestamp as a natural key.

For the dashboard aggregation, I used PostgreSQL window functions to calculate rolling averages and period-over-period comparisons without pulling raw transactions to the application layer. The heavy lifting stays in the database where it belongs.

SELECT
  location_id,
  date_trunc('week', timestamp) as week,
  SUM(amount) as total,
  AVG(SUM(amount)) OVER (
    PARTITION BY location_id
    ORDER BY date_trunc('week', timestamp)
    ROWS BETWEEN 3 PRECEDING AND CURRENT ROW
  ) as rolling_avg
FROM transactions
WHERE group_id = $1
GROUP BY location_id, week
ORDER BY week DESC;

Links

• Code samples and architecture details available on request