This is the build log for CorgiCafe — a multi-tenant time-tracking SaaS I built for a five-location café group in Barcelona. The interesting parts are not the UI; they are the data isolation strategy (PostgreSQL schema-per-tenant), the tablet check-in flow that survives a wet bar, and the compliance plumbing for Spain's mandatory time-tracking law (Real Decreto 8/2019). The whole thing shipped in six weeks, replaced a paper spreadsheet process, and cut payroll preparation from four hours to under one.
If you are weighing whether to build a custom internal SaaS instead of paying for an off-the-shelf one — or you are deciding between schema-per-tenant, row-level tenancy, or a separate database per customer — this is the post I needed when I started.
Why we did not use Factorial, Bizneo, or Sesame
Spain has a healthy market for HR tools, and the obvious move was to subscribe to one. The problem was the math at five locations. Factorial and Bizneo both quoted around EUR 4-6 per employee per month with a per-location surcharge, which landed at roughly EUR 350/month for fifty employees — EUR 4,200/year, every year, forever, without owning the data or the workflow.
The bigger issue was the workflow. The owner did not want a generic HR product. He wanted the payroll export formatted exactly the way his accountant already processed payroll, with the overtime multipliers his contracts used, and his specific holiday calendar for Catalonia. Customising any of the SaaS options to that level either was not supported or required the enterprise tier.
The break-even point: a six-week custom build at fixed price comes in around EUR 6,000-8,000. After 18-24 months it is cheaper than the SaaS subscription, and after that it just keeps paying back. For a stable business that is not pivoting its operations every quarter, custom wins on any horizon longer than two years. That is the calculation that justified the project.
The tenancy decision: shared, row-level, or schema-per-tenant
Multi-tenant SaaS has three honest options for data isolation in PostgreSQL:
- Single shared schema, tenant_id column on every row. Simplest to build. Easy to ship a query that leaks data across tenants. Backups and per-tenant exports are awkward.
- Schema-per-tenant. One PostgreSQL schema per customer, identical structure. Migrations apply to all schemas. Cross-tenant queries are impossible by accident.
- Database-per-tenant. Maximum isolation, maximum operational overhead. Justified for regulated industries (healthcare, banking) or very large customers.
For a single café group with five locations behaving as five logical tenants, schema-per-tenant was the sweet spot. Each location's manager sees only their location. The owner's account has access to all schemas. A new location is provisioned by running migrations against a fresh schema — about two seconds of setup.
The implementation hook in Flask is straightforward: a request-scoped middleware sets the PostgreSQL search_path based on the logged-in user's tenant.
from flask import g, request
from sqlalchemy import event
@app.before_request
def set_tenant_schema():
if not current_user.is_authenticated:
return
g.tenant = current_user.tenant_schema # e.g. "loc_eixample"
db.session.execute(text(f'SET search_path TO "{g.tenant}", public'))Two cautions. First, validate the schema name against an allow-list before interpolating it into SQL — never trust user input here, even from your own user model. Second, connection pooling will reuse connections across requests, so reset search_path at the start of every request, not just on login.
Migrations across many schemas
Alembic does not natively know about schema-per-tenant. The wrapper I use loops over the tenants table at the top of every migration:
def upgrade():
tenants = op.get_bind().execute(
text("SELECT schema_name FROM tenants WHERE active = true")
).scalars().all()
for schema in tenants:
op.execute(f'SET search_path TO "{schema}"')
op.create_table(
'shift',
sa.Column('id', sa.Integer, primary_key=True),
sa.Column('employee_id', sa.Integer, sa.ForeignKey('employee.id')),
sa.Column('check_in', sa.DateTime, nullable=False),
sa.Column('check_out', sa.DateTime, nullable=True),
)For five tenants this is fine. At fifty tenants you will want to parallelise it, and at five hundred you should be running migrations through a job queue with per-tenant status tracking. Build the simple version first; you almost never grow into the complex one as fast as you fear.
The check-in flow on a wet bar tablet
The kiosk is a EUR 130 Android tablet bolted to the wall behind the bar. Employees punch a four-digit PIN to clock in and out. Sounds simple. In practice, the constraints were:
- Wet hands. Touchscreens get false-touched constantly. The PIN pad has large keys with a 200ms debounce on each.
- The tablet runs all day. The web app has to survive a tab that has been open for sixteen hours without leaking memory.
- The Wi-Fi at one location dies for 5-15 minutes per day. Check-ins must not be lost.
The third constraint forced the most interesting design choice: the kiosk page is a small offline-first PWA that buffers events to IndexedDB and pushes them when connectivity returns. Each event carries a client-side UUID so retries are idempotent on the server. Most days this code does nothing visible. On the bad days it quietly catches twenty events that would otherwise be lost.
// Kiosk-side, simplified
async function recordPunch(employeeId, kind) {
const event = {
client_id: crypto.randomUUID(),
employee_id: employeeId,
kind: kind, // "in" or "out"
at: new Date().toISOString(),
};
await db.events.add(event); // IndexedDB
flushQueue(); // best-effort, no await
}
async function flushQueue() {
const pending = await db.events.where('synced').equals(0).toArray();
for (const ev of pending) {
try {
await fetch('/api/punch', { method: 'POST', body: JSON.stringify(ev) });
await db.events.update(ev.id, { synced: 1 });
} catch { return; } // try again on next tick
}
}On the server, the /api/punch endpoint deduplicates on client_id within a 24-hour window. If the same UUID arrives twice (network retry, or queue replay after restart), the second call returns the same response as the first. Idempotency keys are one of those patterns that look paranoid until the day they save your payroll.
Compliance with Real Decreto 8/2019
Spain's labour law mandates that every employer record the daily working hours of every employee, with check-in and check-out times, kept for four years and available to inspection on demand. The law says nothing about tablets or PWAs — paper would be legal — but the audit logs need to be tamper-evident.
The plumbing I added to satisfy this:
- Every check-in event is append-only. There is no UPDATE on the
shifttable from application code; corrections are handled as new events with acorrection_ofreference and an audit reason. - Daily PDF exports are generated automatically and stored, signed with the application's GPG key, in an S3 bucket with object lock for four years. If the inspector asks, the answer is "here is the day".
- The export format matches what the labour inspector's office expects, including totals, breaks, and overtime per employee per week.
Catalonia's holiday calendar lives in a small table that is updated yearly by hand. A scheduled job runs at the start of every month to surface "next month has 18 working days, expected labour cost EUR X" to the owner — which is the kind of number that drives shift-scheduling decisions.
The Excel export the accountant actually wanted
The single feature that justified the whole build, in the owner's eyes, was a one-click monthly export that landed in the accountant's inbox in the exact format he had been transcribing into his payroll software for years. Columns: employee NIE, base hours, overtime hours, holiday hours, hours worked on a Sunday or holiday (different multiplier under the convenio collectivo for hospitality in Catalonia), and total gross.
I built this with openpyxl in about a day, including styling. The accountant's reaction was the line that pays for itself: "I used to spend half a day on the Eixample location alone." That is the entire ROI in one sentence.
from openpyxl import Workbook
from openpyxl.styles import Font, Alignment
def payroll_workbook(month):
wb = Workbook()
ws = wb.active
ws.title = f"Nómina {month:%Y-%m}"
headers = ["NIE", "Empleado", "H. Base", "H. Extra", "H. Festivo", "Bruto EUR"]
ws.append(headers)
for cell in ws[1]:
cell.font = Font(bold=True)
for emp in employees_for_month(month):
ws.append([
emp.nie,
emp.full_name,
emp.base_hours,
emp.overtime_hours,
emp.holiday_hours,
emp.gross_eur,
])
return wbWhat broke in production
The PIN was not unique enough. Two employees picked 1234 in the first week. Lesson: do not let users pick PINs in a context where collisions matter. The fix was to auto-generate a four-digit PIN at hire, mailed to the employee with their first payslip.
One manager kept clocking herself out at 23:59 to "tidy up" the day. That broke overtime detection because the algorithm treated it as a fresh shift starting at midnight. Fix: a hard cap on shift duration (12 hours) and an alert that surfaces "edited shift" events to the owner. Behavioural fixes need to be designed in as much as bugs do.
The PWA cached itself into a corner. A bad service-worker deployment served stale assets for 36 hours on one location's tablet. Now every deploy bumps a version number, and the kiosk shell page checks it on focus and forces a reload if stale.
Numbers after six months
- Payroll preparation: 4 hours → under 1 hour per location per month.
- Overtime down 30% after the owner could see real-time labour cost per shift and adjusted scheduling.
- EUR 600/month saved on overtime alone, separate from the elimination of payroll errors.
- Zero compliance findings in the labour inspection that hit two of the locations in month four.
- 50+ employees on the system, with 100% adoption from week two.
- Build time: 6 weeks, single developer, fixed price.
What I would change today
If I were starting fresh in 2026 I would still pick schema-per-tenant for this scale, but I would consider Postgres row-level security policies as a defence-in-depth layer on top. They cost almost nothing at runtime and would prevent a future bug in the search_path middleware from leaking data.
I would also push harder on biometric check-in via the tablet's front camera. Face match plus a fallback PIN is more pleasant than a PIN alone, and Spanish labour law allows it with explicit consent. The friction at the hire stage is real — you need a documented opt-in process — but the daily friction afterwards drops to zero.
And I would skip Flask in favour of FastAPI for the API endpoints. The kiosk PWA does not need Flask's template engine, and FastAPI's native async handling makes the burst of check-ins at shift change cleaner to reason about.
FAQ
How much does a custom system like this cost?
For a single-tenant version (one company, one to ten locations), EUR 5,000-8,000 fixed price, six to eight weeks. Multi-tenant SaaS for selling to other companies starts around EUR 15,000 because the admin, billing, onboarding, and per-tenant migrations add real scope.
Why Flask and not Django?
Personal preference at the time and the project was small enough that Django's batteries were overkill. For a multi-tenant SaaS today I default to Django with django-tenants, which gives schema-per-tenant out of the box and integrates with Django's migration system cleanly.
How do you handle backups for schema-per-tenant?
pg_dump --schema=loc_eixample per tenant, nightly, encrypted, retained for thirty days plus weekly snapshots for a year. Restore-of-one-tenant is the test you should actually run, not just the backup.
What if a customer wants to take their data with them?
Schema-per-tenant makes this trivial. Dump their schema, hand them a SQL file plus a CSV export of every table. Same export script powers the GDPR data portability obligation.
Is Spanish labour law really this strict?
Yes, and the fines for non-compliance scale fast — EUR 626 to EUR 6,250 per affected employee for a serious infraction. A custom system pays for itself on the first inspection it survives clean, regardless of the productivity savings.
Patterns worth stealing
Three things from this build I have reused on every multi-tenant project since:
Idempotency keys on every state-changing endpoint. The cost is one UUID column and a check; the value is sleeping through the next network outage.
Append-only event logs for anything regulated. Even if your application logic does not need it, the audit conversation is much easier when you can replay the day.
Build the export the user actually asks for, before anything fancy. The Excel that matches the accountant's template is more valuable than any analytics dashboard you can imagine.
If you are looking at building a similar system — internal tool, multi-tenant SaaS, or a single-customer custom platform — happy to walk through the architecture for your case on a 15-minute call. I usually return a fixed-price scope within 24 hours.