Plain SQL as output
ETL processes generated automatically from metadata. The generated code is the contract - versioned, diffable, with no extra abstraction layer.
About meDI²
DI2
Data Integration²
Generating ETL pipelines from metadata - maintainable, readable, auditable.
A project of my own, self-built from spec through UX to production deploy. This page shows not only what DI² is - but also how it is built.
What DI² is in half a minute
Vision, output format, method lineage and data quality philosophy - compressed into four paragraphs.
DI² generates maintainable, readable and auditable ETL processes from the metadata of your source system - automatically, in plain SQL. The output currently runs on PostgreSQL and SQL Server.
The starting point is tables, columns, types and relationships - they become the model, enriched with domain knowledge such as allowed values, business keys and historization rules. From this model, extraction and SCD2 historization are generated automatically; the transformation into DWH or OLTP deliberately remains handcraft - DI² delivers the scaffolding, you write the business logic.
Data quality is the product, not a feature. Business rules as WHERE clauses in the metadata; faulty records land in an error table instead of silently in the DWH. Three logging levels - process, step, stack trace - queryable with a SELECT.
Not a reinvention: The method has existed since 2015 in constant iteration - initially as a VBA/Excel-based code generator, today as a PostgreSQL-native engine with a Next.js web layer. DI² is the re-implementation of an approach proven over more than 10 years, on a modern stack.
How DI² makes decisions
Every tool makes assumptions about what matters. These are ours - explicit, so they stay open to debate.
Tables instead of clicks
The process is derived directly from tables, columns, types, relationships - data-model-driven instead of click-driven.
Two RDBMS today, portable
Versioned SQL runs on PostgreSQL and SQL Server today. Porting to other dialects (Snowflake, BigQuery, Oracle, DuckDB) is manageable because the output stays plain SQL.
One procedure per file
Every code change visible as a Git diff. Plain SQL without a DSL - engineers and analysts understand it without ramp-up.
Every run verifiable
Three logging levels - process, step, stack trace - all queryable with a SELECT. Not felt, but measured.
Rules become metadata
Allowed values, value ranges, business and alternate keys, historization rules live in the model - not as comments in the code.
Three generated stages
Extraction and SCD2 historization are generated automatically; the DWH/OLTP transformation remains handcraft - deliberately.
Quality is the product
Business rules as WHERE clauses. Faulty records land in an error table - not silently in the DWH.
Five steps
From metadata to production
- 01Connect a sourceMSSQL · PostgreSQL · MySQL
- 02Scan the schemainformation_schema
- 03EnrichBK · DQ · historization
- 04Generate SQLDDL · staging · SCD2
- 05OrchestrateSQL synchronous · Python async
Four environments, one VPS, own identity
Self-hosted on Hetzner (EU data center, Germany) - no Vercel, no Supabase, no US managed service for sensitive layers. Data, auth and mail stack entirely within the EU, GDPR-compliant. Docker Compose with a multi-app layout, Nginx as reverse proxy, Let's Encrypt for SSL.
Browser
HTTPS
All-Inkl DNS
A record → VPS
GitHub Actions
SSH deploy
↓ ↓ ↓
Hetzner Cloud VPS
Docker Compose · multi-app layout · /home/fupi/app/di2/<env>
Nginx Reverse Proxy · Let's Encrypt SSL
Application
Next.js DEV
:3000
Application
Next.js INT
:3001
Application
Next.js TEST
:3002
LIVE
Application
Next.js PROD
:3003
PostgreSQL 17
di2_postgres
App data · audit · stored procedures
Self-hosted · pg client with connection pooling
Self-hosted · pg client with connection pooling
Keycloak 26
di_keycloak
OIDC IdP · own Postgres instance · custom theme
Auth.js v5 · JWT sessions (8h) · role-based
Auth.js v5 · JWT sessions (8h) · role-based
Stack
- Next.js 16 / React 19 / TypeScript
- PostgreSQL 17 (self-hosted)
- Tailwind 3 + shadcn/ui
- Auth.js v5 (NextAuth) + Keycloak OIDC
Auth & Identity
- Keycloak as the central IdP
- JWT sessions (8h), role-based
- Local user shadow + reconcile job
- Custom theme (DI²) for login & mails
CI/CD
- GitHub Actions → SSH → Hetzner
- 4 environments in parallel on 1 VPS
- schema_apply_log audit trail
- Security gate before prod deploy
Eight phases, one skill per phase, human-in-the-loop
AI-driven development does not mean "Claude writes code on its own". It means: specialized skills per workflow step, clear checkpoints, and at every phase the system waits for a decision.
Phase 1 · Spec
Requirements
/requirements
Feature spec with user stories, acceptance criteria, edge cases - and a mark on what can only be checked by clicking.
Phase 2 · Design
Architecture
/architecture
Technical architecture, PM-friendly, without code.
Phase 3 · UX
UX & Mockups
/ux
UX critique + mockups (HTML / React + shadcn) as the template - plus the click test cases, before anything is built.
Phase 4 · Build
Backend
/backend
API routes, DB schema, stored procedures, RLS policies.
Phase 4 · Build
Frontend
/frontend
UI with shadcn/ui - against real APIs, no mocks.
Phase 5 · Test
QA
/qa
Runs the declared test cases and logs the run. Acceptance + edge cases + feature-scoped security checks.
Phase 6 · Review
Code Review
/review
Diff against spec & conventions; approve / request changes.
Phase 7 · Deploy
Deploy
/deploy <env>
GitHub Actions: dev → int → test → prod, every stage gated - red tests mean: no deploy.
Phase 8 · Audit
Security
/security
Mandatory gate before prod: OWASP, auth, headers, dependencies.
Cross-cutting · Always-on
Skills that run across the workflow
Bug loop
/bug - docs/bugs/bug-NNNN-<slug>.md
Can be triggered at any time - documentation, targeted fix, QA re-test, close. Bugs are never reopened; a regression gets a new ID.
Maintenance checks
/check-updates · npm · Docker · Actions · VPS
Periodic maintenance: dev dependencies and VPS components - read-only findings, no auto-apply.
Two test nets, one catalog, the expectation before the code
AI-driven development is suspected of dropping tests first. This section states what is checked, how it is checked, and when the expectation is fixed.
Two nets
Automated where it can be computed - clicked where it only shows on screen
Automated
Vitest · unit and component tests
Branching logic, validation, sorting, wording per language. The tests sit right next to the code they check and move with it on every refactoring.
Manual
docs/test-cases · click test cases
Only what cannot be computed: the interplay of several views, the moment a result becomes visible, messages, sign-in and sign-out.
Red means: no deployEvery push runs lint, convention checks, tests and build. Before every deploy the tests run a second time - if they fail, or if a test file is missing from the run, the deploy does not start.
The catalog
Test cases as a track of their own, next to features and bugs
The catalog holds 158 active test cases in 22 suites. Each one is written so that someone who has never seen the code can work through it.
- TEST-NNNN
- A third identifier next to diapp-NNNN for features and BUG-NNNN for bugs. A test case keeps its number and its location for good.
- Declaration ≠ run log
- The test case describes steps and expected result. What a run produced goes into a separate log - a result inside the test case file would devalue the next run.
- Suites
- A suite is what gets clicked through in one session on the same starting state. A test case may belong to several.
- Coverage
- Every test case names the files it targets. Which cases are due after a change follows from the diff - not from memory.
- Run classes
- Smoke runs on every deployment. Regression runs when its coverage is hit, and in full before every production deploy.
The obvious objection
Who checks when the same instance writes spec, code and test?
In the classic setup, separating tester and developer prevents a test from being written along the finished implementation. AI-driven development has no such separation. It is therefore established in time instead of in people.
- Expectation/requirements
The expected result is written down as an acceptance criterion in the spec - before a single line of code exists.
- Test case/ux
The test case takes over that expectation word for word and adds the click steps from the mockups. Only then does the build start.
- Run/qa
After the build the case is run and logged. If the result differs, that is a finding - the expectation is not adjusted.
Two writing rules hold the separation in place: the expected result contains no term that does not appear on screen. And every test case contains at least one statement about what must not happen.
This section was built the same way: its test cases were in the catalog before it existed.