DEV Community: bennaceur walid

Why `exit code 0` is lying to you

bennaceur walid — Fri, 03 Apr 2026 09:01:48 +0000

Your cron job ran at 2am. The exit code was 0. Everything's fine, right?

Last month, my SIRENE data import script ran successfully every Sunday for 3 weeks straight. Exit code 0. No errors in the logs. Green across the board.

Except it was importing 0 rows instead of 16.8 million.

Nobody noticed for 3 weeks.

The problem with "did it run?"

Most cron monitoring tools answer one question: did the job execute? They use a dead man's switch — your script pings a URL when it finishes, and if the ping doesn't arrive, you get an alert.

That's useful. But it misses the most dangerous failure mode: the job that runs successfully but produces garbage.

Your backup script runs but the database connection silently fails → 0 bytes backed up
Your ETL job processes 12 rows instead of 14,000 → exit code 0
Your import takes 45 minutes instead of 2 → no error, just slow degradation

Exit code 0 doesn't mean "everything is fine." It means "I didn't crash."

What if your monitoring could understand the output?

I built Crontiq to answer a different question: did it run AND was the output normal?

The idea is simple. Instead of just pinging a URL, you send your job's output as JSON:

# At the end of your script:
curl -X POST https://ping.crontiq.io/p/$API_KEY/import-sirene \
  -H "Content-Type: application/json" \
  -d "{\"rows\": $ROW_COUNT, \"duration_ms\": $DURATION, \"errors\": $ERROR_COUNT}"

Crontiq does three things automatically:

Extracts every numeric value from your JSON (including nested objects like {"db": {"rows": 100}} → db.rows = 100)
Tracks each metric over time with sparkline graphs
Detects anomalies using a moving average + 2 standard deviations

No configuration. No thresholds to set. No schema to define. You just send JSON and it figures it out.

The math is embarrassingly simple

The anomaly detection isn't machine learning or anything fancy. It's a moving average over the last 10 values, plus 2 standard deviations:

average = avg(last 10 values)
stddev = stddev(last 10 values)
is_anomaly = abs(current_value - average) > 2 * stddev

If your rows metric usually hovers around 16,800,000 and suddenly drops to 0, that's a deviation of ~7,200 standard deviations. You get an alert.

If it gradually decreases from 16.8M to 16.7M over a few weeks, nothing happens — that's within normal range.

Simple? Yes. But nobody else does it automatically. Healthchecks.io stores your POST body as raw text. Cronitor requires you to format metrics in a specific query parameter syntax (&metric=count:3329). With Crontiq, you just send whatever JSON your script already produces.

The badge that sells itself

Here's the part I'm most excited about. When you make a monitor public, Crontiq generates a live SVG badge:

[![SIRENE Import](https://crontiq.io/badge/cq_chk_xxx.svg)](https://crontiq.io/public/cq_chk_xxx)

This badge doesn't just say "passing" or "failing" — it shows actual data:

import-sirene | 16.8M rows | ✓ healthy

Put it in your GitHub README and every visitor sees proof that your data pipeline is running and healthy. Click the badge and you get a public status page with sparklines and history.

I use it on my own projects. Here's what it looks like in practice:

GEOREFER examples — monitors a 16.8M row French business database
GreenCalc examples — monitors a carbon footprint API

How it's built

The stack is straightforward:

Java 21 / Spring Boot 3.3 — handles the ping ingestion
PostgreSQL with partitioned tables — stores pings and metrics by month
Redis — caches monitor status for fast badge generation
Hetzner — single server, Docker containers

The ping endpoint is designed to respond in under 50ms. The actual JSON parsing, metric extraction, and anomaly detection happen asynchronously. Your script never waits for Crontiq to finish thinking.

The JSON flattening is recursive — it turns any nested structure into dot-notation keys:

{"db": {"connections": 5, "pool_size": 20}, "rows": 14209}

Becomes:

db.connections = 5
db.pool_size = 20
rows = 14209

Each key gets its own sparkline on the dashboard.

Free. Actually free.

Crontiq is the free front door to the AZMORIS developer ecosystem. 20 monitors, unlimited pings, auto-metrics, email alerts — no credit card, no trial period.

Why free? Because I built it to solve my own problem first (monitoring my own APIs), and because a good loss leader brings more value than a paywall.

Try it: crontiq.io

Crontiq is part of the AZMORIS ecosystem, which includes GEOREFER (French business data API), Doxnex (document intelligence), GreenCalc (carbon footprint API), and IDonex (identity management).

Build a Green Finance Feature in 30 Minutes with the GreenCalc API

bennaceur walid — Mon, 30 Mar 2026 11:52:54 +0000

The EU is spending billions on the Renovation Wave — 35 million buildings to be retrofitted by 2030. France, Germany, Italy, Belgium, and Spain each have their own subsidy programs with different eligibility rules.

If you're building a PropTech app, energy audit tool, or renovation marketplace, your users will ask: "How much subsidy can I get?"

This tutorial shows you how to answer that question with a single API call.

What We're Building

A feature that takes a household profile and returns:

Which subsidy programs they qualify for
Estimated amounts in EUR
Net cost after all subsidies

We'll use the GreenCalc API — a REST API covering 12 subsidy programs across 5 EU countries.

Prerequisites

A free sandbox API key (works immediately, no sign-up required)
Any HTTP client (we'll show cURL, JavaScript, and Python)

Sandbox key for testing:

gc_sandbox_000000000000000000000000000000000

Step 1: Check What's Available

First, let's see which countries and work types are supported:

# List countries
curl https://greencalc.io/api/v1/eligibility/countries \
  -H "X-Api-Key: gc_sandbox_000000000000000000000000000000000"

# List work types
curl https://greencalc.io/api/v1/eligibility/work-types \
  -H "X-Api-Key: gc_sandbox_000000000000000000000000000000000"

Countries return with their available program count, work types include things like WALL_INSULATION, HEAT_PUMP, SOLAR_PANELS, etc.

Step 2: Check Eligibility

The /eligibility/check endpoint tells you which programs a household qualifies for without running a full financial simulation:

const response = await fetch("https://greencalc.io/api/v1/eligibility/check", {
  method: "POST",
  headers: {
    "X-Api-Key": "gc_sandbox_000000000000000000000000000000000",
    "Content-Type": "application/json",
  },
  body: JSON.stringify({
    country_code: "FR",
    household_income: 25000,
    household_persons: 3,
    property_type: "HOUSE",
    property_age_years: 25,
    work_types: ["WALL_INSULATION", "HEAT_PUMP"],
  }),
});

const result = await response.json();
console.log(result.eligible);       // true
console.log(result.programs.length); // 2-3 programs
console.log(result.total_estimated); // e.g. 12300

This household qualifies for MaPrimeRénov' and CEE in France — around €12,300 in subsidies.

Step 3: Run a Full Simulation

Add estimated_cost to get the complete financial picture:

import requests

result = requests.post(
    "https://greencalc.io/api/v1/eligibility/simulate",
    headers={
        "X-Api-Key": "gc_sandbox_000000000000000000000000000000000",
        "Content-Type": "application/json",
    },
    json={
        "country_code": "FR",
        "household_income": 25000,
        "household_persons": 3,
        "property_type": "HOUSE",
        "property_age_years": 25,
        "owner_occupant": True,
        "work_types": ["WALL_INSULATION", "HEAT_PUMP"],
        "estimated_cost": 18000,
    },
).json()

print(f"Renovation cost: €{result['estimated_cost']}")
print(f"Subsidies:       €{result['total_subsidies']}")
print(f"You pay:         €{result['net_cost']}")
print(f"Savings:         {result['savings_percentage']}%")

Output:

Renovation cost: €18,000
Subsidies:       €12,300
You pay:         €5,700
Savings:         68.3%

The simulation also returns zero-interest loan eligibility (like France's Éco-PTZ up to €30,000) — so you can show users their financing options alongside grants.

Step 4: Compare Countries

Building for a multi-country audience? The /compare endpoint runs simulations across multiple countries in one request:

curl https://greencalc.io/api/v1/eligibility/compare \
  -X POST \
  -H "X-Api-Key: gc_sandbox_000000000000000000000000000000000" \
  -H "Content-Type: application/json" \
  -d '{
    "country_codes": ["FR", "DE", "IT"],
    "household_income": 30000,
    "household_persons": 4,
    "property_type": "HOUSE",
    "property_age_years": 20,
    "work_types": ["HEAT_PUMP", "SOLAR_PANELS"],
    "estimated_cost": 22000
  }'

This returns a side-by-side comparison — useful for showing users which country offers the best deal or for cross-border analytics.

Handling Errors

The API returns RFC 7807 Problem Details for errors:

{
  "status": 400,
  "title": "Validation Error",
  "detail": "country_code must be one of: FR, DE, IT, BE, ES"
}

Common status codes:

400 — Invalid input (missing field, bad country code)
401 — Invalid or missing API key
422 — Business rule violation (e.g., incompatible work types)
429 — Rate limit exceeded (sandbox: 100 req/min)

Integrating Into Your UI

Here's a minimal React component that uses the simulation endpoint:

function SubsidyCalculator({ profile }) {
  const [result, setResult] = useState(null);

  async function calculate() {
    const res = await fetch("/api/v1/eligibility/simulate", {
      method: "POST",
      headers: {
        "X-Api-Key": process.env.GREENCALC_API_KEY,
        "Content-Type": "application/json",
      },
      body: JSON.stringify(profile),
    });
    setResult(await res.json());
  }

  return (
    <div>
      <button onClick={calculate}>Calculate Subsidies</button>
      {result && (
        <div>
          <p>You could save <strong>€{result.total_subsidies}</strong></p>
          <p>Net cost: €{result.net_cost} ({result.savings_percentage}% off)</p>
          <ul>
            {result.programs.map(p => (
              <li key={p.program_id}>{p.name}: €{p.estimated_amount}</li>
            ))}
          </ul>
        </div>
      )}
    </div>
  );
}

What's Next

Webhooks — Get notified when subsidy rules change in a country you're tracking
More countries — Netherlands, Portugal, Austria coming soon
Batch simulations — Run hundreds of profiles in one request

The sandbox key works for all endpoints with no sign-up. Start building:

Built by AZMORIS Group. Questions? Open an issue or reach out at contact@greencalc.io.

How we mapped EU renovation subsidies into one JSON response

bennaceur walid — Mon, 30 Mar 2026 08:12:30 +0000

A homeowner in Lille wants to insulate their roof and install a heat pump. They could be eligible for MaPrimeRenov, CEE certificates, a zero-interest Eco-PTZ loan, and reduced VAT — four separate schemes, each with its own income thresholds, work-type restrictions, and ceiling amounts. Cross the border into Belgium and it's a different set of regional premiums. Go to Germany and you're dealing with KfW loans and BAFA grants.

We built GreenCalc to turn all of that into a single POST request. Here's what we learned.

The problem: 15 schemes, 5 countries, rules that change every January

The EU renovation subsidy landscape is a mess — by design. Each country runs its own programs with its own eligibility rules. Here's what we're dealing with:

Country	Main schemes	Income-based?	Climate zones?	Update frequency
France	MaPrimeRenov, CEE, Eco-PTZ, TVA 5.5%	Yes (4 categories)	Yes (H1/H2/H3)	January + July
Germany	KfW 262, BEG EM, BAFA audit	No (flat rates)	No	Variable
Italy	Superbonus, Ecobonus, Bonus Casa	No	No	When the government changes
Belgium	Primes Wallonie, Renolution Bruxelles, Mijn VerbouwPremie	Yes (per region)	No	January
Spain	PREE, MOVES III	Partially	No	Variable

A fintech developer who wants to show users "how much subsidy can you get?" would need to become a regulatory expert in five countries. Or call one API.

Decision #1: Config-driven, not code-driven

The most important architecture decision was this: subsidy rates live in the database, not in Java code.

When France updates MaPrimeRenov thresholds every January, we run one SQL statement:

UPDATE greencalc.subsidy_scheme_rates
SET    rate_value = 30.00,
       updated_at = NOW()
WHERE  country_code   = 'FR'
  AND  scheme_code    = 'ma_prime_renov'
  AND  work_type      = 'ROOF_INSULATION'
  AND  income_category = 'MODEST';

No code change. No JAR rebuild. No deployment. The rate is read from PostgreSQL on every request, so changes are live instantly.

The subsidy_scheme_rates table has this shape:

country_code | scheme_code     | work_type        | income_category | rate_type  | rate_value | max_amount | unit
FR           | ma_prime_renov  | ROOF_INSULATION  | VERY_MODEST     | FIXED      | 75.00      | 6000.00    | PER_M2
FR           | ma_prime_renov  | ROOF_INSULATION  | MODEST          | FIXED      | 60.00      | 4800.00    | PER_M2
FR           | cee             | ROOF_INSULATION  | ALL             | FIXED      | 12.00      | NULL       | PER_M2
DE           | kfw_beg         | HEAT_PUMP        | ALL             | PERCENTAGE | 25.00      | 15000.00   | FIXED

This means non-technical staff can update rates with a SQL client. The code only contains the calculation logic — how to combine rates with household income, surface area, and work types.

Decision #2: The Strategy pattern for multi-country logic

Each country has wildly different rules. France has income-based categories that depend on climate zone. Germany has flat percentage grants. Italy has cascading bonuses that interact with each other.

We needed each country to be its own self-contained module, while the simulation engine stays generic. Classic Strategy pattern:

public interface ICountryRulesProvider {

    String getCountryCode();

    String determineIncomeCategory(
        BigDecimal fiscalIncome, int householdSize, String zone);

    List<EligibleSubsidy> calculateSubsidies(
        List<PlannedWork> plannedWorks,
        String incomeCategory,
        String climateZone,
        BigDecimal totalEstimatedCost);
}

Each country implements this. Here's a simplified view of the France provider:

@Service
public class FranceRulesProvider implements ICountryRulesProvider {

    // 2026 income thresholds for zones H1/H2
    // Index: [household_size - 1][very_modest, modest, intermediate]
    private static final int[][] THRESHOLDS_H1_H2 = {
        {17009, 21805, 30549},  // 1 person
        {24875, 32000, 44907},  // 2 persons
        {29917, 38442, 54071},  // 3 persons
        {34948, 44884, 63235},  // 4 persons
        {40002, 51326, 72400}   // 5+ persons
    };

    @Override
    public String getCountryCode() { return "FR"; }

    @Override
    public String determineIncomeCategory(
            BigDecimal fiscalIncome, int householdSize, String zone) {
        int[][] thresholds = "H3".equalsIgnoreCase(zone)
            ? THRESHOLDS_H3 : THRESHOLDS_H1_H2;
        int index = Math.min(householdSize, 5) - 1;
        int[] bracket = thresholds[index];
        int income = fiscalIncome.intValue();

        if (income <= bracket[0]) return "VERY_MODEST";
        if (income <= bracket[1]) return "MODEST";
        if (income <= bracket[2]) return "INTERMEDIATE";
        return "HIGH";
    }

    @Override
    public List<EligibleSubsidy> calculateSubsidies(
            List<PlannedWork> works, String incomeCategory,
            String climateZone, BigDecimal totalCost) {
        List<EligibleSubsidy> subsidies = new ArrayList<>();
        for (PlannedWork work : works) {
            // Per-work schemes: rates loaded from DB
            calculateMaPrimeRenov(work, incomeCategory)
                .ifPresent(subsidies::add);
            calculateCee(work, climateZone)
                .ifPresent(subsidies::add);
        }
        // Cross-work schemes
        calculateEcoPtz(works, totalCost).ifPresent(subsidies::add);
        calculateTvaReduite(totalCost).ifPresent(subsidies::add);
        return subsidies;
    }
}

The registry auto-discovers all providers via Spring DI:

@Service
public class CountryRulesRegistry {

    private final Map<String, ICountryRulesProvider> providers;

    public CountryRulesRegistry(List<ICountryRulesProvider> providerList) {
        this.providers = providerList.stream()
            .collect(Collectors.toMap(
                p -> p.getCountryCode().toUpperCase(),
                p -> p
            ));
        log.info("Initialized with {} providers: {}",
            providers.size(), providers.keySet());
    }

    public ICountryRulesProvider getProvider(String countryCode) {
        ICountryRulesProvider provider = providers.get(
            countryCode.toUpperCase());
        if (provider == null)
            throw new BusinessValidationException(
                "UNSUPPORTED_COUNTRY",
                "Country not supported: " + countryCode);
        return provider;
    }
}

Adding a sixth country? Create a new @Service that implements ICountryRulesProvider. The registry picks it up automatically. Zero changes to the simulation engine.

Normalizing work types across languages

"Isolation des combles" in France is "Dachdammung" in Germany and "Isolamento del tetto" in Italy. If your API accepts free-text work descriptions, you need NLP in five languages. We went with normalized codes instead:

Code                       | FR                        | DE                  | IT
ROOF_INSULATION            | isolation_combles         | dachdaemmung        | isolamento_tetto
HEAT_PUMP_AIR_WATER        | pac_air_eau               | waermepumpe_luft    | pompa_calore_aria
WALL_INSULATION_EXTERIOR   | ite                       | wdvs                | cappotto_termico
WINDOWS_DOUBLE_GLAZING     | fenetres_double_vitrage   | fenster_2fach       | finestre_doppio_vetro
SOLAR_PANELS_PHOTOVOLTAIC  | panneaux_pv               | photovoltaik        | pannelli_fotovoltaici

The API consumer always sends ROOF_INSULATION regardless of which country they're querying. Our mapping table converts it to the local code before looking up rates.

This solved an annoying integration problem: developers who integrate GreenCalc don't need five separate dropdown menus with localized work types. One universal set of 11 codes works everywhere.

Automated regulatory monitoring

The scariest part of running a subsidy API isn't building it — it's keeping it current. When France publishes new MaPrimeRenov thresholds on January 1st, how quickly do you notice?

We built a watcher that hashes the HTML of 10 official government URLs daily:

@Service
@ConditionalOnProperty(
    name = "greencalc.regulatory.enabled",
    havingValue = "true")
public class RegulatoryWatcherService {

    @Scheduled(cron = "${greencalc.regulatory.cron:0 0 6 * * *}")
    public void scheduledCheck() {
        List<RegulatorySource> sources =
            sourceRepository.findByActiveTrue();
        for (RegulatorySource source : sources) {
            HttpResponse<String> response = httpClient.send(
                HttpRequest.newBuilder()
                    .uri(URI.create(source.getSourceUrl()))
                    .GET().build(),
                HttpResponse.BodyHandlers.ofString());

            String newHash = sha256(response.body());
            if (!newHash.equals(source.getLastContentHash())) {
                // Content changed — create alert for review
                createAlert(source, "CONTENT_CHANGED",
                    source.getLastContentHash(), newHash);
            }
            source.setLastContentHash(newHash);
            sourceRepository.save(source);
        }
    }

    static String sha256(String content) {
        MessageDigest digest = MessageDigest.getInstance("SHA-256");
        byte[] hash = digest.digest(
            content.getBytes(StandardCharsets.UTF_8));
        return HexFormat.of().formatHex(hash);
    }
}

When a hash changes, an alert appears in our admin panel. A human reviews it, updates the rates in the database, and logs the change to a public changelog at GET /api/v1/eligibility/changelog.

It's not perfect — some government sites are SPAs where the hash changes on every load due to CSRF tokens. We're considering diffing the text content instead. But for static pages (which most official subsidy pages are), SHA-256 comparison catches 100% of real changes.

The result: one POST, all subsidies

Here's a real request to the sandbox (no API key needed):

curl -X POST https://greencalc.io/api/v1/eligibility/simulate \
  -H "X-Api-Key: gc_sandbox_000000000000000000000000000000000" \
  -H "Content-Type: application/json" \
  -d '{
    "country_code": "FR",
    "household": {
      "annual_income": 25000,
      "household_size": 3,
      "is_owner": true
    },
    "property": {
      "type": "HOUSE",
      "construction_year": 1975,
      "energy_rating": "E",
      "postal_code": "59000",
      "surface_m2": 100
    },
    "planned_works": [
      {"work_type": "ROOF_INSULATION",
       "estimated_cost_eur": 8000, "surface_m2": 80},
      {"work_type": "HEAT_PUMP_AIR_WATER",
       "estimated_cost_eur": 12000}
    ]
  }'

Response (sandbox mode — amounts are illustrative):

{
  "simulation_id": "ef2d5406-7216-4802-853d-116d32494fa4",
  "country": {
    "code": "FR",
    "name": "France",
    "schemes_last_updated_at": "2026-01-01"
  },
  "household_category": "SANDBOX",
  "eligible_subsidies": [
    {
      "scheme_code": "ma_prime_renov",
      "scheme_name": "MaPrimeRenov",
      "provider": "ANAH",
      "type": "GRANT",
      "amount_eur": 5000.0,
      "conditions": [
        "Primary residence",
        "Property at least 15 years old"
      ],
      "cumulative": true
    },
    {
      "scheme_code": "cee",
      "scheme_name": "Certificats d'Economies d'Energie",
      "type": "GRANT",
      "amount_eur": 3200.0
    },
    {
      "scheme_code": "eco_ptz",
      "scheme_name": "Eco-PTZ (zero-interest loan)",
      "type": "LOAN",
      "amount_eur": 20000.0
    },
    {
      "scheme_code": "tva_reduite",
      "scheme_name": "TVA 5.5% (reduced VAT)",
      "type": "VAT_REDUCTION",
      "amount_eur": 2900.0
    }
  ],
  "summary": {
    "total_estimated_cost_eur": 20000.0,
    "total_grants_eur": 8200.0,
    "total_loans_eur": 20000.0,
    "total_tax_savings_eur": 2900.0,
    "estimated_out_of_pocket_eur": 8900.0,
    "estimated_new_energy_rating": "C",
    "estimated_energy_saving_percent": 35.0
  }
}

For a household spending 20,000 EUR on renovations: 8,200 EUR in grants, 20,000 EUR in zero-interest loans, 2,900 EUR in tax savings. Estimated out-of-pocket: 8,900 EUR. All from one API call.

Try it yourself

The sandbox requires no signup — just a fixed API key:

# Swap "FR" for "DE", "IT", "BE", or "ES"
curl -X POST https://greencalc.io/api/v1/eligibility/simulate \
  -H "X-Api-Key: gc_sandbox_000000000000000000000000000000000" \
  -H "Content-Type: application/json" \
  -d '{"country_code":"DE","household":{"annual_income":50000,"household_size":2,"is_owner":true},"property":{"type":"HOUSE","energy_rating":"F","postal_code":"10115","surface_m2":120},"planned_works":[{"work_type":"WALL_INSULATION_EXTERIOR","estimated_cost_eur":35000,"surface_m2":150}]}'

Interactive docs: greencalc.io/docs

Regulatory changelog: greencalc.io/api/v1/eligibility/changelog

GreenCalc is built by AZMORIS Group. The stack is Java 21, Spring Boot 3.3, PostgreSQL 16, and too many government PDFs.

How We Query 16.8M SIRENE Establishments in 66ms

bennaceur walid — Sat, 07 Mar 2026 12:25:22 +0000

The French SIRENE database contains information about every registered business in France — over 30 million establishments. We imported 16.8 million active ones into GEOREFER and needed to make them searchable by name in under 100ms.

Here's how we did it with PostgreSQL 16 and pg_trgm.

The Challenge

Our establishment table has 16.8 million rows. Users need to search by:

SIREN (9 digits) — exact match, trivial with a B-tree index
SIRET (14 digits) — exact match, same
Company name — fuzzy match, this is where it gets interesting

The name search needs to handle:

Partial matches: "Total" should find "TotalEnergies SE"
Typos: "Miclein" should find "Michelin"
Accent insensitivity: "Societe Generale" should match "Societe Generale"

The Naive Approach: ILIKE

First attempt:

SELECT * FROM georefer.establishment
WHERE company_name ILIKE '%total%'
LIMIT 25;

EXPLAIN ANALYZE result:

Seq Scan on establishment
  Filter: (company_name ~~* '%total%')
  Rows Removed by Filter: 16799975
  Planning Time: 0.1ms
  Execution Time: 12,847ms

12.8 seconds. Full sequential scan on 16.8M rows. Unusable.

Enter pg_trgm

PostgreSQL's pg_trgm extension breaks strings into trigrams (3-character sequences) and uses GIN indexes to find similar strings efficiently.

CREATE EXTENSION IF NOT EXISTS pg_trgm;

CREATE INDEX idx_establishment_name_trgm
ON georefer.establishment
USING GIN (company_name gin_trgm_ops);

Now with trigram similarity:

SELECT *, similarity(company_name, 'total') as sim
FROM georefer.establishment
WHERE company_name % 'total'
ORDER BY sim DESC
LIMIT 25;

EXPLAIN ANALYZE:

Bitmap Heap Scan on establishment
  Recheck Cond: (company_name % 'total')
  -> Bitmap Index Scan on idx_establishment_name_trgm
     Index Cond: (company_name % 'total')
  Planning Time: 0.3ms
  Execution Time: 66ms

66ms. From 12.8 seconds to 66ms — a 194x improvement.

The Import Strategy: 3 Phases

Importing 16.8M rows isn't straightforward. We use a 3-phase approach:

Phase 1: Schema + Staging

CREATE TABLE georefer.establishment (
    id SERIAL PRIMARY KEY,
    siren VARCHAR(9) NOT NULL,
    siret VARCHAR(14) NOT NULL UNIQUE,
    company_name VARCHAR(255),
    commercial_name VARCHAR(255),
    legal_form VARCHAR(10),
    naf_code VARCHAR(6),
    employee_range VARCHAR(5),
    postal_code VARCHAR(5),
    city VARCHAR(100),
    department_code VARCHAR(3),
    is_headquarters BOOLEAN DEFAULT FALSE,
    is_active BOOLEAN DEFAULT TRUE,
    created_date DATE,
    last_update DATE
);

Phase 2: Bulk COPY

We use PostgreSQL COPY for maximum throughput:

COPY georefer.establishment
(siren, siret, company_name, ...)
FROM '/tmp/sirene_active.csv'
WITH (FORMAT csv, HEADER true, DELIMITER ',');

COPY is 10-50x faster than batch INSERT for bulk loading. The 16.8M rows load in about 8 minutes.

Phase 3: Index Creation

We create indexes AFTER the bulk import (creating them before would slow the import):

-- Exact lookups
CREATE INDEX idx_establishment_siren ON georefer.establishment(siren);
CREATE INDEX idx_establishment_siret ON georefer.establishment(siret);

-- Geographic filtering
CREATE INDEX idx_establishment_postal ON georefer.establishment(postal_code);
CREATE INDEX idx_establishment_dept ON georefer.establishment(department_code);
CREATE INDEX idx_establishment_city ON georefer.establishment(city);

-- Fuzzy name search
CREATE INDEX idx_establishment_naf ON georefer.establishment(naf_code);
CREATE INDEX idx_establishment_name_trgm ON georefer.establishment USING GIN (company_name gin_trgm_ops);

Combined Query: Name + Geographic Filter

The real power comes from combining trigram search with geographic filters:

SELECT *, similarity(company_name, 'boulangerie') as sim
FROM georefer.establishment
WHERE company_name % 'boulangerie'
  AND department_code = '75'
  AND is_active = true
ORDER BY sim DESC
LIMIT 25;

This returns all bakeries in Paris in ~45ms, even across 16.8M rows.

The API Layer

The Spring Boot service exposes this via REST:

# Search by SIREN
curl 'https://georefer.io/geographical_repository/v1/companies?siren=552120222' \
  -H 'X-Georefer-API-Key: YOUR_API_KEY'

# Search by name + department
curl 'https://georefer.io/geographical_repository/v1/companies/search?name=michelin&department_code=63' \
  -H 'X-Georefer-API-Key: YOUR_API_KEY'

The response includes all SIRENE fields:

{
  "success": true,
  "data": [
    {
      "siren": "855200507",
      "siret": "85520050700046",
      "company_name": "MANUFACTURE FRANCAISE DES PNEUMATIQUES MICHELIN",
      "naf_code": "22.11Z",
      "employee_range": "5000+",
      "postal_code": "63000",
      "city": "CLERMONT-FERRAND",
      "is_headquarters": true
    }
  ]
}

Performance Summary

Query Type	Before (no index)	After (pg_trgm)	Improvement
Name search	12,847ms	66ms	194x
Name + dept filter	13,102ms	45ms	291x
SIREN exact	8,200ms	0.3ms	27,333x
SIRET exact	8,150ms	0.2ms	40,750x

Lessons Learned

Always create indexes after bulk import — creating them before can make the import 10x slower
pg_trgm GIN indexes use a lot of disk — our 16.8M row trigram index is ~2.3 GB
Set maintenance_work_mem high during index creation — SET maintenance_work_mem = '1GB' cuts index creation time in half
COPY beats INSERT every time for bulk loading — use COPY for anything over 10K rows

Try It

GEOREFER exposes 16.8M SIRENE establishments through a simple REST API:

Free tier: 100 req/day, no credit card
Docs: https://georefer.io/docs
Sign up: https://georefer.io/#signup

AZMORIS Engineering — "Software that Endures"

Building a French Address Validation API with 26M Addresses

bennaceur walid — Sat, 07 Mar 2026 12:20:47 +0000

The French government's Base Adresse Nationale (BAN) contains 26 million addresses — every street, every house number, every hamlet across mainland France and overseas territories. We built GEOREFER to make this data accessible through a single REST API, combined with company lookup from the SIRENE database.

This is the technical story of how we did it.

The Problem: Fragmented French Geographic Data

If you're building a FinTech product in France, you need to validate customer addresses for KYC compliance. Sounds simple, right?

Here's what the landscape looks like in 2026:

API Adresse (BAN) — Free, but no SLA, rate-limited to 50 req/s, and no company data
La Poste RNVP — The gold standard for postal validation, but no public REST API
Google Address Validation — Global coverage but $0.005/request adds up fast, and no SIRENE integration
INSEE API SIRENE — Company data, but separate authentication, slow responses (~500ms), and no address validation

To do proper KYC, you need at least two of these APIs, with different auth mechanisms, different response formats, and different rate limits.

We decided to build one API that does it all.

Architecture Overview

GEOREFER is built on a straightforward Java stack:

Java 11 + Spring Boot 2.7.5
PostgreSQL 16 (42M+ rows across 12 tables)
Redis 7 (API key cache, TTL 5min)
Elasticsearch 7.17 (city autocomplete, fuzzy search)

The architecture follows a clean layered approach:

REST Controllers (17 controllers, 39 endpoints)
    |
Business Services (12 interfaces, 16 implementations)
    |
Repositories (JPA + Elasticsearch)
    |
PostgreSQL + Redis + Elasticsearch

Importing 26M Addresses from the BAN

The BAN publishes its data as CSV files, updated monthly. The full dataset is around 3.5 GB compressed.

Our import strategy:

Download the latest BAN CSV export
Parse with streaming CSV reader (no full file in memory)
Batch insert using JDBC batch operations (batch size = 5000)
Index city data into Elasticsearch for autocomplete

The key challenge was handling the French administrative hierarchy:

Region (18) → Department (101) → Commune (35,000+) → Address (26M)

Each commune has an INSEE code (5 digits), one or more postal codes, and belongs to exactly one department. Paris, Lyon, and Marseille have arrondissements that function as sub-communes with their own INSEE codes.

We store communes in a french_town_desc table with full hierarchy:

SELECT f.name, f.insee_code, f.postal_code,
       d.name as department, r.name as region
FROM georefer.french_town_desc f
JOIN georefer.department d ON f.department_code = d.code
JOIN georefer.region r ON d.region_code = r.code
WHERE f.name ILIKE 'paris%'

Address Validation with GeoTrust Scoring

The core feature is POST /addresses/validate. You send a French address, and we return:

Confidence score (0-100) — how sure we are the address exists
GeoTrust Score (0-100) — composite reliability score for KYC
Validated address — normalized, corrected, with GPS coordinates
AFNOR format — postal-standard NF Z 10-011 formatting

The GeoTrust Score is a weighted composite:

Component	Weight	What it measures
Confidence	35%	Street-level address matching
Geo Consistency	25%	Cross-validation: postal code vs commune vs department
Postal Match	20%	Postal code precision (exact, partial, invalid)
Country Risk	20%	FATF/GAFI country risk rating

curl -X POST 'https://georefer.io/geographical_repository/v1/addresses/validate' \
  -H 'Content-Type: application/json' \
  -H 'X-Georefer-API-Key: YOUR_API_KEY' \
  -d '{
    "street_line": "15 Rue de la Paix",
    "postal_code": "75002",
    "city": "Paris",
    "country_code": "FR"
  }'

Response:

{
  "success": true,
  "data": {
    "validated_address": {
      "street_line": "15 Rue de la Paix",
      "postal_code": "75002",
      "city": "PARIS",
      "country": "France"
    },
    "confidence_score": 95,
    "geotrust_score": {
      "overall": 92,
      "level": "LOW",
      "components": {
        "confidence": 95,
        "geo_consistency": 100,
        "postal_match": 100,
        "country_risk": 0
      }
    }
  }
}

Elasticsearch for City Autocomplete

City autocomplete needs to be fast — under 50ms for a good UX. We use Elasticsearch's Completion Suggester with a custom analyzer:

city_analyzer: edge_ngram (min=2, max=15) + ascii_folding
city_search_analyzer: standard + ascii_folding

The ASCII folding is critical for French cities. Users type "Beziers" but the official name is "Beziers". Our analyzer handles both.

The GET /cities/autocomplete?q=marseil&limit=5 endpoint returns results in under 50ms, even with 35,000+ communes indexed.

We also support fuzzy search with GET /cities/search?q=Monplier — using Elasticsearch's fuzziness AUTO parameter, this correctly returns "Montpellier" despite the typos.

Multi-Tenant API Keys & Rate Limiting

GEOREFER is a SaaS with 5 subscription plans:

Plan	Daily Limit	Rate/min	Price
DEMO	50	10	Free
FREE	100	10	Free
STARTER	5,000	30	49 EUR/mo
PRO	50,000	60	199 EUR/mo
ENTERPRISE	Unlimited	200	Custom

Each API key gets its own token bucket (Bucket4j) for rate limiting. Authentication goes through a Spring filter chain:

Request → API Key validation (Redis cache) → Quota check → Rate limit → Feature gate → Controller

The Feature Gate controls which endpoints each plan can access. For example, company search (/companies) requires PRO or higher, while city search is available on all plans.

What's Next

We're currently at 16.8 million SIRENE establishments imported and 35,000+ communes indexed. The API handles 39 endpoints across geographic data, address validation, company search, and admin/billing.

If you're building anything that touches French addresses or company data, give it a try:

Free tier: 100 requests/day, no credit card required
Docs: https://georefer.io/docs
Sign up: https://georefer.io/#signup
Examples: https://github.com/azmoris-group/georefer-examples

In the next article, we'll deep-dive into how we query 16.8M SIRENE establishments in 66ms using PostgreSQL trigram indexes.

AZMORIS Engineering — "Software that Endures"