The Karlsruhe Pass – A Practical Example of Successful Software Modernization

In 2020, together with jfbw (Jugendfreizeit- und Bildungswerk Karlsruhe), we designed and offered a web-based application for managing the Karlsruhe Pass and the Karlsruhe Children’s Pass. It was intended as a successor solution for an existing Access database that could no longer be effectively developed.

The focus was on clear management of family and personal data, support for central workflows in issuing and renewing passes, creation of PDF documents, issuance of vouchers, differentiated statistical evaluations, and role and rights management.

1.1 Program Context

The Karlsruhe Pass enables people with low incomes, as well as children and families, to participate in offers in areas such as leisure, culture, education, and mobility (e.g., KVV discounts, zoo, pools, courses) with reductions or, in some cases, complete cost reimbursement. The pass is generally valid for twelve months and can be renewed annually; the Children’s Pass addresses children and young people with specific benefits and rules.
Current information (e.g., on the Deutschlandticket with a discount for pass holders) is available at karlsruher-pass.de.

1.2 Problem Statement

• Legacy Microsoft Access system, no longer maintainable due to OS updates.
• Need for role-based, internal web access for jfbw employees.
• Secure, audit-proof processes for pass/voucher issuance and statistical reports.

1.3 Target Vision (Initial Implementation 2020)

• Web application exclusively for internal employees.
• Family management as the central entry point.
• Pass issuance/renewal, voucher issuance, statistics, employee management.

2.1 Family-Centric Data Model

Family as an organizational anchor:

• Individuals belong to exactly one family (single individuals = 1-person family).
• Duplicate cleansing (person merging) with validation.
• Family operations: splitting, merging, moving individuals.
• Search/filter functions by family ID, person, last name.

Added Value: The model maps real-world case constellations (patchwork, changing households) without over-engineering, keeps the data clean, and supports future statistical dimensions.

2.2 Pass Lifecycle

New issuance & renewal with system-predefined start/end dates and rules:

• Automatic pass type (Karlsruhe Pass vs. Children’s Pass) via date of birth.
• Unique pass number (validation).
• Validity logic (no overlapping active passes, defined special cases for the Children’s Pass).
• Proof upload (PDF)
• Printing of forms via direct print button (requires local print server)

Added Value: Smooth processing, consistent validities, and printable output without media disruption.

2.3 Statistics

• Time-period-related sums of active passes by type, postal code, eligibility basis.
• Definition: A pass counts if its validity and the evaluation period overlap.

Added Value: Reliable key figures for reporting and budget/offer management.

2.4 Employees & Governance

• Role/rights management, activate/deactivate/delete.
• Audit: Creator / creation date, last change (person/date) for family/person/pass/voucher.
• Reassign deleted accounts → Pseudo-employee “Deleted Employee”.

Added Value: Traceability and operational security during personnel changes.

3.1 Automatic Deletion (GDPR Compliance)

• Documents: Deletion 10 years after upload.
• Passes & Vouchers: Deletion 10 years after validity end.
• Applications: Deletion 10 years after creation.
• Persons/Families: Family-wise deletion if no passes/applications exist and >3 months since creation; empty families daily.
• Nightly job, statistical history remains stable (months/years are not retrospectively “rewritten”).

Added Value: Legal certainty, storage hygiene, and predictable data retention cycles.

3.2 District Enrichment via ArcGIS (Karlsruhe City Geo-Portal)

• Auto-recognition of district, postal code, city based on street + house number (pre-fill, manually editable).
• One-time mass enrichment of existing data (> 50,000 records).
• Catalog: 27 Karlsruhe districts.

Added Value: Geographical precision for planning & statistics – without manual maintenance.

3.3 Statistics 2.0 – Multidimensional & Pivot-Ready

Evaluations for a freely selectable period (start/end month) and up to 5 criteria simultaneously, including:

• Type, benefit (eligibility basis), age cohorts (0–5, 6–17, 18–59, 60+),
• Gender (f/m/d/unknown), nationality, districts,
• New vs. renewal, number of guardians/children/family members.
  Export as Excel with 3 sheets: Summary, By Years, By Months; monthly closings automatically freeze key figures after 6 months.

Added Value: In-depth professional insight and pivot capability for ad-hoc analyses.

3.4 “Extras” (Additional Services)

• Creation of active/inactive extras with variants (e.g., “Das Fest: Fri/Sat/Sun”), free-text notes.
• Mass assignment to family members with an active pass; duplicate protection per person.

Added Value: Fast, traceable allocation of additional entitlements.

3.5 Direct Pass & Voucher Printing (Komdruck IDP 2103)

• Web2Print/VM configuration including exact dimensions/orientation.
• Drivers set to correctly use black IR-readable security ink.

Added Value: Minimize media disruptions, reduce misprints, save time.

• Web-Only, Role-Based Access: internal use, password forgotten, secure session handling.
• Transparent History: who/what/when changed – essential for administration & audit.
• Collision Protection: no simultaneous valid passes per person; uniqueness of pass/voucher numbers.
• Batch Jobs: nightly auto-deletion, monthly closings.
• Data Quality: duplicate rules, ArcGIS-supported address normalization.
• Reporting-Ready: Excel exports specifically pivot-capable.
• Integration Capability: REST interfaces (digital pass), QR workflows; connectable to the Karlsruhe.App ecosystem idea.
• Operational Clarity: Deliberate delimitations (no scanner/special printer auto-binding in the browser, no cash book) to avoid complexity – and to specifically deepen where it adds value (direct printing, geocoding, statistics).

• Pre-selections (e.g., start/end, address/last name for family creation) speed up processing.
• Error prevention through product rules instead of a “flood of error messages”.
• Consistent simplicity: Families as a structure – without redundant master data.
• PDF outputs for passes/vouchers precisely fitted for forms.
• Quick search by family ID, person name, numbers.

• Minimal Principle: only required attributes, no mandatory gender field in the basic system (later optional for Statistics 2.0).
• Retention & Deletion Concept: automated, auditable, documentable.
• Transparency through change logs, rights management.
• Data Transfer (digital passes): interface hardening, one-time QRs, automatic expiration.
• Operating Model: external, secured web application, no access to Windows shares → upload as a copy (conscious decision in favor of security & traceability).

• Productivity: significantly faster processing, fewer media disruptions.
• Data Quality: fewer duplicates, valid validities, complete address records with district.
• Reporting: Pivot-capable exports
• Governance: clear responsibilities, rights, history; audit-proof.
• Future-Proofing: API-First thinking and app integration as a perspective.

Conception & Foresight

We translate business logic into robust, maintainable software: simple where possible – specific where necessary. Family-centricity, pass rules, extras: all designed from operational needs.

Solution-Oriented

Instead of all-capable monoliths: conscious delimitations and targeted deepening where it brings benefits (direct printing, geo-enrichment).

State of the Art

• Secure web architecture, role-based, automated jobs.
• Interface design (digital pass, QR flows) connectable to the Karlsruhe.App strategy.
• Data Governance (deletion routines) as built-in compliance.
• Analysis-First: Excel exports that truly enable reporting.