duckdb-firebird — Roadmap

Living plan. Ordered by dependency / ROI. Acceptance criteria spelled out so each item can be closed independently.

Compatibility matrix

Component	Today (v1.0.x)	Target
DuckDB	v1.5.3 (pinned in CI and build scripts); v1.5.2–v1.5.6 all validated (v1.5.6 on 2026-09-29, full suite 21/21 test files, local matrix `scripts/build_matrix.ps1`)	v1.5.x + Stable C ABI when `StorageExtension` lands in it
Firebird server	3.0, 4.0, 5.0 all tested live (CI matrix)	same
Firebird client (`libfbclient`)	4.0+ recommended (INT128 / TZ / DECFLOAT)	4.0+
GizmoSQL	works against the local build; air-gapped recipe documented	bundled docker image
Platforms	Linux x64, Windows x64	+ macOS arm64, + Linux arm64

v1.5.6 (released 2026-09-28) is a bug-fix line release: no change in the extension API surface we use (extension_loader, CreateTableFunctionInfo, StorageExtension::Register), and it carries the C-API v1 symbol-versioning unification plus additive backports of DuckDB 2.0 API spellings. The build pin intentionally stays v1.5.3 until the DuckDB Community Extensions catalog moves its own target (currently v1.5.5); see docs/pt/duckdb_1_5_compatibility_plan.md for the validation record.

The XSQLDA codes for SQL_INT128 / SQL_TIMESTAMP_TZ / SQL_TIME_TZ / SQL_DEC16 / SQL_DEC34 are wired in firebird_types.cpp and verified live against Firebird 4+ servers. The historical FB4+ gap was DECFLOAT NULL-on-fetch; v0.6.0 fixes it with a lossless VARCHAR fallback via server-side CAST(... AS VARCHAR(64)) (no OO API needed for this fallback). A native decimal-float decoder or a lossy numeric fast path remains future work.

Milestone v0.4 — Firebird 4/5 live coverage

1. FB4 + FB5 docker fixtures (foundation)

Without these the rest of the milestone is unverifiable.

Add scripts/setup_test_firebird_v4.sh and setup_test_firebird_v5.sh mirroring the existing v3 script. Use firebirdsql/firebird:v4 and firebirdsql/firebird:v5 images (or build from C:\tmp\firebird-src for v5 RC1 if no image exists yet).
Add a biz4.fdb fixture exercising every new FB4+ type: INT128, NUMERIC(38,…), DECFLOAT(16), DECFLOAT(34), TIMESTAMP WITH TIME ZONE, TIME WITH TIME ZONE.
New CI workflow build-linux-fb4.yml (matrix: fb-version ∈ {3, 4, 5}) replays both sqllogictest files plus a new firebird_fb4_types.test.

Acceptance: green CI on FB3 / FB4 / FB5 with all 11 existing types + the 5 new FB4 types.

2. Live type verification — INT128 / TIMESTAMP_TZ / TIME_TZ — DONE

Verified against Firebird 5.0.4 on Windows (May 2026). All three map correctly:

INT128 max/min (±170141183460469231731687303715884105727) → HUGEINT round-trip exact.
DECIMAL(38, 5) backed by INT128 → DECIMAL(38, 5) exact.
TIMESTAMP_TZ with IANA zone (America/Sao_Paulo) → TIMESTAMP WITH TIME ZONE; the IANA zone is not preserved (DuckDB's type is offset-only), but the UTC instant matches.
TIME_TZ → TIME WITH TIME ZONE, same offset-only behaviour.

A bug found during this verification has been fixed (LoadTableSchema was missing the FB4 RDB$FIELD_TYPE codes 24–31 — see test_report.md "Firebird 5 live coverage" section).

3. DECFLOAT (DEC16 / DEC34) — silent NULL fixed in v0.6

Historical gap: SQL_DEC16/SQL_DEC34 used to surface as DOUBLE but fetch NULL, because DuckDB has no native IEEE decimal-floating-point type and the legacy ISC API path does not decode Decimal64/Decimal128 directly.

v0.6 resolves this with the conservative, lossless path documented below: server-side CAST(col AS VARCHAR(64)), surfaced as DuckDB VARCHAR. Native Decimal64/Decimal128 decoding or an explicit lossy numeric fast path remain future work only if real workloads justify them.

4. FB5 compatibility sweep

Firebird 5.0 RC1 changelog flags a few items worth verifying:

#7682 ParallelWorkers default from firebird.conf — server-side parallelism. The existing partitions=N named param still controls client-side parallelism; we should document the interaction (use partitions=1 on FB5 with high ParallelWorkers to avoid 2x parallelism).
#7707 Better IN-list optimization — verify our IN (…) pushdown still wins over per-element OR when the list has 1000+ items.
RDB$RELATION_TYPE values — confirm types 1 (view), 2 (external), 4 (GTT preserve), 5 (GTT delete) still match in FB5; spot-check RDB$INDICES, RDB$RELATION_CONSTRAINTS, RDB$INDEX_SEGMENTS for the PK probe path didn't add required columns.
DSQL_unprepare on close — confirm the legacy ISC API path (isc_dsql_prepare / isc_dsql_fetch) still works on FB5; the modern OO API (Attachment::prepare) is preferred upstream but switching is a v1.0 task.

Acceptance: the existing test suite passes unchanged against FB5; README adds a "Firebird 5 + ParallelWorkers" note.

5. GizmoSQL smoke test — scripts shipped, CI wiring pending

Two scripts now in scripts/:

gizmosql_aircache.sh — downloads icu + spatial for ${DUCKDB_VERSION:-v1.5.3} / ${PLATFORM:-linux_amd64} and populates ~/.duckdb/extensions/…. Bind-mount that dir into GizmoSQL on air-gapped hosts.
gizmosql_smoke.sh — brings up Firebird 5 + GizmoSQL containers, loads the extension, applies the biz4 fixture, and asserts a row count + dtype over Arrow Flight SQL via the Python ADBC driver.

Acceptance (still open): a CI job that runs gizmosql_smoke.sh on every push to main. The matrix workflow build-linux-fb-matrix.yml is the obvious host for it; deferred until the GitHub Actions billing hold on this repo lifts.

Milestone v0.5 — Pushdown completeness

6. LIMIT pushdown — deferred (upstream gap)

Investigation against DuckDB v1.5.3 source shows TableFunction has no pushdown_limit hook and LogicalGet has no limit field; a SQL LIMIT N lands in a LogicalLimit above the scan, so the planner never tells our scanner about it. The optimiser stops calling the scan once N rows are emitted (cheap on a small N), so the missing piece only matters when the cost of opening the cursor over a huge result set is itself prohibitive.

Workaround in place: firebird_scan(…, row_limit=N) named param already emits ROWS N in the Firebird SQL — the user just has to write it explicitly when they know the limit at query time. The firebird_query.cpp builder fully supports it; no further change needed for the manual path.

Re-evaluate when DuckDB exposes a pushdown_limit hook on TableFunction (tracked upstream — no fixed milestone). At that point: wire the hook, set partitions = 1 whenever a limit is present (avoid per-partition N×partitions over-fetch), drop this deferred note.

7. LIKE prefix pushdown — DONE

pushdown_complex_filter hook now lifts BoundFunctionExpression of the ~~ (LIKE) scalar function with a constant RHS, requires at least one literal character before any %/_, and emits col LIKE 'pattern' ESCAPE '\' into the Firebird WHERE. Patterns starting with %/_, non-LIKE expressions, and any shape we don't recognise stay in filters so DuckDB re-applies them above the scan.

Observation from live testing on FB5: DuckDB v1.5.3 already decomposes the simple LIKE 'prefix%' case into col >= 'prefix' AND col < 'prefiq' before the hook runs, so the normal TableFilter pushdown path consumes those. Our hook catches the patterns DuckDB does not decompose — embedded %/_ (e.g. 'S%Textil', 'C_racao%') and any future LIKE variant that doesn't fit the range-rewrite. The two paths cooperate.

Single-quote escaping in the pattern is handled by SqlLiteral; backslash escape semantics by the ESCAPE '\' clause.

Milestone v0.5 — Analytics performance

PM review on v0.4.4 set the v0.5 scope: paging, prepared statements with bind variables, and predicate-pushdown gap fill. Arrow RecordBatch produced directly by the scanner was explicitly moved to v1.x — that is a different product/API, not part of v0.5. The current Arrow integration (DuckDB converts Vector / DataChunk to Arrow at the Flight SQL boundary) is already correct.

Items are listed in delivery order; each has a clear acceptance test.

9. `row_offset` + physical paging — DONE

Shipped in the v0.5 series and released in v0.5.0+.

What landed:

New row_offset=M named param on firebird_scan(...) alongside the existing row_limit=N. FirebirdBindData.offset_override carries it through the bind path.
FirebirdQueryBuilder::Build accepts both and emits ROWS M+1 TO M+N when both are set (Firebird is 1-based, inclusive); falls back to the existing ROWS N when only limit is set.
Bind-time guards keep the API honest without breaking the v0.4 row_limit=N syntax:
1. row_offset without row_limit raises a BinderException (skip-then-drain is expensive and surprising).
2. row_limit / row_offset combined with partitions > 1 (the user explicitly opted into parallel scan and paging) raises — those two are incompatible. The implicit case (no partitions= argument, default 0) is silently coerced to serial so the v0.4 firebird_scan(..., row_limit=100) spelling keeps working.
3. row_offset + row_limit overflow guard (sum > idx_t::max raises at bind time so the scanner never builds malformed SQL).

row_offset is per-scan only — deliberately not surfaced as an ATTACH option, because paging is a per-query decision rather than a catalog-wide policy.

Caveat documented in the README and the named-parameter table: physical paging is unstable under concurrent writes — rows at positions [M, M+N) can shift between calls. Recommend keyset pagination over a sortable PK for live workloads.

10. Prepared statements with bind variables — second

Two-part item. Part (a) — input XSQLDA bind variables — is DONE in the v0.5 series. Part (b) — LRU statement cache per connection — is deferred until there's a benchmark showing the reuse-gain justifies the lifetime-management complexity under the ATTACH connection pool.

Part (a): bind variables — DONE

FirebirdStatement gained a parametrised constructor that runs isc_dsql_describe_bind, allocates the input XSQLDA + per-column buffers, encodes each Value positionally, and passes the input sqlda to isc_dsql_execute. The builder's TranslateFilter emits ? placeholders for bindable types and accumulates the matching values into Result.params; the scanner threads them through FirebirdConnection::OpenCursor(sql, params).

Encoding covers SQL_TEXT / SQL_VARYING (length-prefixed), SHORT / LONG / INT64, FLOAT / DOUBLE, BOOLEAN, DATE, TIME, TIMESTAMP. NULL is signalled through the indicator slot.

Strings always parametrise (the security gain is unconditional — a user-supplied apostrophe can't break the SQL). HUGEINT / DECIMAL still ship as inline literals via SafeLiteralInline (numeric, no quoting hazard). Unsigned ints (UINTEGER, UBIGINT, …) and TZ types stay residual — Firebird has no unsigned counterpart on the bind path, and FB4's TZ-literal syntax varies enough that inlining would regress.

Part (b): LRU statement cache — deferred

Skipped in v0.5. Bind variables already deliver the security/correctness win that motivated the item; the prepare-per-cursor cost is bounded by the ATTACH connection pool's reuse, and the LRU would have to reason about cursor lifetime under that pool to be safe. Revisit when there is a concrete workload + microbenchmark showing prepare time is the bottleneck.

Risk to track when we do it: parameter XSQLDA encoding for FB4 types (INT128, TIMESTAMP_TZ, DECFLOAT) differs from the output side. Each needs a live test on FB4 + FB5 before being claimed.

Acceptance (when we do it): a benchmark issuing 10 000 sequential point lookups fb.main.CUSTOMER WHERE CUST_ID = $1 against a remote Firebird returns end-to-end at ≤ 1.2× the cost of the equivalent direct-isql parameterised loop.

11. Predicate pushdown — fill the gaps — DONE (NOT IN + NOT bool)

Shipped in v0.5. The pushdown_complex_filter hook now lifts BoundOperatorExpression(COMPARE_NOT_IN) and BoundOperatorExpression(OPERATOR_NOT) of a boolean column ref in addition to the existing LIKE 'prefix%' translation. BETWEEN doesn't need a hook — DuckDB already decomposes it into col >= a AND col <= b and the TableFilterSet path handles both sides. Safe CAST(...) rewrites are intentionally still residual; DuckDB's expression simplifier folds the no-op cases and the rest don't move the needle on the workloads we care about today (revisit when there is one that does).

All new pushdowns honour the IsNoneTextColumnGated invariant — NONE-text columns under non-strict transcoding stay residual, matching the v0.4.2 contract.

Original v0.5 list still open (revisit only if measured):

Plug the holes that pushdown_complex_filter still leaves on the DuckDB-side FILTER node. Each addition is conditional on the NONE-text gate already in place (IsNoneTextColumnGated): a predicate that touches a transcoded NONE column never pushes.

NOT IN (...): structurally identical to IN; emit col NOT IN (...) and reuse the same literal-formatting path.
Boolean NOT expr: detect BoundOperatorExpression(NOT) with a translatable child; emit NOT (...).
COL BETWEEN a AND b when DuckDB hasn't decomposed it: detect the function shape in pushdown_complex_filter and emit col BETWEEN a AND b directly.
CAST(col AS X) op literal for safe X (INTEGER, BIGINT, DECIMAL, DATE) — only when Firebird's CAST(...) shares semantics with DuckDB's.
LIKE 'suffix' / LIKE '%mid%' — measured benchmark first. On indexed columns these will already use a seq scan on Firebird's side, so the pushdown may not move the needle; document the finding and only ship if the benchmark is clearly positive.

Invariant: a predicate only goes to Firebird if we can prove it is bit-for-bit equivalent to DuckDB's evaluation. Anything else stays residual.

Acceptance: EXPLAIN shows the new predicates on the FIREBIRD_SCAN's Filters: line; existing 245 sqllogictest assertions stay green and new ones (one per added predicate) cover the happy path + the NONE-text-gate residual path.

12. Automatic `LIMIT` pushdown — contingent on DuckDB API

Hold this until the upstream DuckDB API exposes a real pushdown_limit hook on TableFunction or a stable OptimizerExtension entry point — neither is in v1.5.3. The existing row_limit=N named param remains the documented path in the meantime; the README already labels it as the supported v0.4.x mechanism.

If we ship a custom OptimizerExtension rewrite (the duckdb-postgres approach) without an upstream commitment, we inherit the maintenance burden of tracking DuckDB's planner changes release by release. The cost-benefit only favours that when there is no near-term upstream alternative; revisit each DuckDB minor.

Acceptance (when we do it): EXPLAIN SELECT * FROM fb.main.BIG_T LIMIT 100 shows ROWS 100 on the FIREBIRD_SCAN node; same query without an explicit row_limit= runs in ms not seconds on a 1 M-row remote table.

13. Discovery / metadata coverage tests — DONE

Shipped in v0.5. test/sql/firebird_metadata.test (79 assertions) covers SHOW TABLES FROM fb, DESCRIBE fb.main.TAB, information_schema.tables, information_schema.columns, case-insensitive table lookup (fb.main.employee == fb.main."EMPLOYEE"), schema-not-found errors, and the read-only enforce on INSERT / DROP TABLE / CREATE TABLE against the ATTACH catalog. Documented gaps the suite intentionally pins the current behaviour for:

is_nullable now reflects RDB$NULL_FLAG for table columns. Domain / per-column precedence handled by COALESCE(rf.RDB$NULL_FLAG, f.RDB$NULL_FLAG, 0) in LoadTableSchema; declared NOT NULL columns surface as NO and get a NotNullConstraint in the ATTACH CreateTableInfo.
Views still appear as table_type='BASE TABLE' in information_schema.tables. The catalog layer doesn't yet differentiate RDB$RELATION_TYPE = 1 from regular tables. Deferred past v0.5 (needs ViewCatalogEntry path).

13b. (Historical placeholder — see above)

firebird_tables(...) + the ATTACH (TYPE firebird) catalog already populate the entries DuckDB clients query. Lock that in with explicit sqllogictest coverage so a future refactor cannot break BI / dbt-style introspection:

SHOW TABLES FROM fb;
DESCRIBE fb.main.TAB1;
SELECT * FROM information_schema.tables WHERE table_catalog = 'fb';
SELECT * FROM information_schema.columns WHERE table_catalog = 'fb' ORDER BY ordinal_position;

Acceptance: a new test/sql/firebird_metadata.test exercises each form against the EMPLOYEE + FB4_TYPES fixtures and matches the expected name/type rows verbatim.

Milestone v0.5.x — Observability + connection pool

Phase 1 (v0.5.2 / v0.5.3) and Phase 2 (v0.5.4 candidate) cover the operational surface a federated reader needs to be honest with its users: what SQL was sent, what it cost, and which connection ran it.

14. Remote-query telemetry — DONE (v0.5.2 / v0.5.3)

Two zero-arg table functions scoped to the current ClientContext:

firebird_last_query() — last attempted remote query (1 row max).
firebird_query_log() — ring buffer, opt-in via SET firebird_query_log_size = N.

15 columns shared between both: remote_sql, redacted binds, table_name, projected_columns, pushed_filters, residual_filters, rows_read, firebird_time_us, total_time_us, connection_id, connection_reused, parallel_scan, partitions, captured_at, sanitized error_message. Text and BLOB binds surface as <text:redacted>; password=... and scheme://user:pass@host patterns are stripped from error text.

Acceptance: test/sql/firebird_observability.test covers defaults, projection + predicate pushdown surfacing, no-leak sentinel cross-check, ATTACH path, ring-buffer rotation + disable.

15. Configurable connection pool — DONE (v0.5.4 candidate)

FirebirdConnectionPool grows three opt-in knobs read at ATTACH time and validated up front:

SET firebird_pool_enabled = true|false — default true. When false, every Acquire opens a fresh connection and Release destroys it.
SET firebird_pool_max_size = N — caps the idle queue (not active leases). 0 = unlimited (default).
SET firebird_pool_idle_timeout_ms = MS — discards connections that sat in the idle queue longer than MS. Clock starts at Release(). 0 = no expiry (default).

Connection identity surfaces through the observability slot: connection_id is a process-wide monotonic id assigned by the extension (not a Firebird attachment id); connection_reused is true when the lease came from the idle queue.

Settings are read at the ATTACH that creates the pool. A later SET does not retune an existing pool; users must DETACH + ATTACH to apply a change.

By design, the direct firebird_scan(...) table function does not go through any pool — each call builds its own one-shot connection. That keeps the no-ATTACH path simple and avoids forcing pool semantics onto callers that don't want a catalog.

Acceptance: test/sql/firebird_pool.test covers defaults, negative-value rejection, ATTACH reuse, opt-out fresh-every-time, and direct-scan id behaviour. Pool defaults reproduce pre-Phase-2 behaviour (unlimited LIFO, no expiry, enabled) so existing deployments observe no change.

Hotfix v0.5.5 — runtime `libfbclient` loading

v0.5.4 shipped a vcpkg.json declaring libfbclient as a build-time dependency, but the upstream vcpkg registry used by duckdb/community-extensions does not carry a libfbclient port, so the community CI failed before CMake on every platform of PR #1980. v0.5.5 removes the build-time dependency entirely.

What changed:

vcpkg.json removed; descriptor drops requires_toolchains: vcpkg.
CMakeLists.txt no longer links libfbclient. Headers ship vendored under third_party/firebird/include/ (Interbase Public License; THIRD_PARTY_NOTICES.md).
New src/firebird_client_loader.{hpp,cpp} resolve the 16 ISC entry points the extension actually calls (isc_attach_database … fb_interpret, including isc_open_blob2) via dlopen / LoadLibrary on the first firebird_* call. Lazy + mutex-guarded; once-per-process.
DUCKDB_FIREBIRD_CLIENT_LIBRARY environment variable lets callers pin a specific library; the override is authoritative (failed load throws instead of falling back).
firebird_client.cpp defines TU-local macros that redirect every ISC entry-point name to the loader table - no call-site edits, error-context strings unaffected (preprocessor does not expand inside "...").
README + CONTRIBUTING + docs/pt/function_manual.md document the runtime requirement and the env-override knob.

Acceptance: build green on Windows local without linking fbclient; runtime smoke against the EMPLOYEE fixture green; DUCKDB_FIREBIRD_CLIENT_LIBRARY with a bad path raises the documented IO Error: DUCKDB_FIREBIRD_CLIENT_LIBRARY is set to '...' but that file could not be loaded; full regression sweep (8 firebird_* sqllogic suites) reports 398 assertions, 0 fail.

Once the v0.5.5 tag is published, duckdb/community-extensions PR #1980 must be re-pointed at repo.ref: v0.5.5 so the community build matrix can pick up the runtime-loader path.

Hotfix v0.5.6 — restore empty `vcpkg.json` manifest

v0.5.5 removed vcpkg.json entirely, but the community CI always invokes the build with -DVCPKG_BUILD=1 -DVCPKG_MANIFEST_DIR=/duckdb_build_dir/. The vcpkg toolchain needs a manifest file in that directory before CMake configures the project, so the missing file moved the failure mode from "libfbclient port not found" to "manifest not found". v0.5.6 restores vcpkg.json with empty dependencies:

{
  "name": "duckdb-firebird",
  "version-string": "0.5.6",
  "description": "DuckDB extension that scans Firebird databases via runtime-loaded libfbclient",
  "dependencies": []
}

The runtime loader, the vendored Firebird headers, the requires_toolchains: vcpkg removal, and every behavioural rule shipped in v0.5.5 stay exactly as published. v0.5.6 is a packaging-only follow-up: the toolchain has a manifest to read, and the manifest declares no Firebird dependency.

Once the v0.5.6 tag is published, duckdb/community-extensions PR #1980 should be re-pointed at repo.ref: v0.5.6 and taken out of draft.

16. dbt sources generator — IMPLEMENTED

firebird_generate_dbt_sources(catalog_name VARCHAR) walks an attached Firebird catalog's RDB$RELATIONS / RDB$RELATION_FIELDS and emits a deterministic dbt sources.yml as a single VARCHAR row.

Surface:

Argument is the alias of an existing ATTACH ... (TYPE firebird); the direct firebird_scan(...) path is out of scope by design.
Output is one row, one column yaml VARCHAR. Pipe through COPY (SELECT yaml FROM ...) TO 'sources.yml' to write to disk.
The function name, schema (always "main"), and connection alias appear in the YAML; the Firebird connection string and credentials never do.

Behaviour:

Catalog validation raises BinderException with the function name when catalog_name is NULL, names a non-attached catalog, or names an attached catalog whose GetCatalogType() is not "firebird".
Metadata is read once per call through a FirebirdMetadataLease RAII helper friended on FirebirdCatalog, so the catalog's pool is reused without exposing conn_info_.
Tables are sorted by RDB$RELATION_NAME; columns by RDB$FIELD_POSITION. Views (RDB$RELATION_TYPE = 1) appear as tables with no tests: block.
data_type is the DuckDB LogicalType::ToString() mapping of the column - the same string a user sees when querying through ATTACH.
Single-column primary keys emit tests: [not_null, unique]. Composite primary keys deliberately emit no tests: block (a CASE WHEN COUNT(*) = 1 gate on the index segments returns NULL); marking any single member of a composite key as unique would be semantically wrong. Users who need multi-column uniqueness should add dbt-utils.unique_combination_of_columns by hand.
All identifier and free-text strings emitted into the YAML pass through a YamlQuote helper (escape of \\, \", \n, \r, \t, controls via \xNN); regression tests assert that the connection-string sentinel literals SYSDBA, masterkey, firebird://, .fdb, password never appear in the output.

Acceptance: test/sql/firebird_dbt_sources.test covers the 21 contracts above (argument validation, YAML header, source + schema, table ordering, column ordering, data_type for INTEGER / DECIMAL(18,2) / DATE / BOOLEAN, exactly three - not_null markers, composite-PK block carries no tests, view block carries no tests, no-leak sentinel). A TPK_COMPOSITE table is added to both scripts/setup_test_firebird.sh and scripts/smoke_fixture.sql, and test/sql/firebird_metadata.test is updated in lockstep (4 → 5 tables, 16 → 19 columns) so the existing fixture-count assertions stay green. Public manual entry: docs/pt/function_manual.md, Nível 3 - Banco anexado.

Out of scope for this milestone (tracked for later):

Named parameters (source_name, target_schema, include_views).
RDB$DESCRIPTION BLOB capture for table and column comments (currently emitted as "").
Composite-PK tests: block - deliberately empty for the reason above.

Platform — separation of concerns

The duckdb-firebird extension is one component of a larger analytics stack. v0.5 docs make the boundary explicit so reviewers and integrators stop conflating responsibilities:

Layer	Responsibility	Owned by
Source	OLTP data, RDB$ catalog	Firebird server (3 / 4 / 5)
Bridge	Firebird ⇄ DuckDB types, pushdown, paging	duckdb-firebird (this repo)
Engine	Vectorised execute, optimiser, Parquet, S3 IO	DuckDB v1.5.3
Server (remote)	Arrow Flight SQL over the wire	GizmoSQL (DuckDB embedded)
Object store	Bronze / silver / gold artefacts	MinIO / S3 / R2 / GCS
Transformation	Medallion modelling, contract testing	dbt-duckdb
Consumption	Dashboards / ad-hoc	Power BI (Parquet import primary; ADBC Flight SQL for tooling that supports it)

The MVP flow against a legacy ERP database:

LOAD firebird;
INSTALL httpfs; LOAD httpfs;

ATTACH 'C:/legacy/erp.fdb' AS fb
    (TYPE firebird, user 'SYSDBA', password 'masterkey');

CREATE SCHEMA IF NOT EXISTS bronze;
CREATE SCHEMA IF NOT EXISTS silver;
CREATE SCHEMA IF NOT EXISTS gold;

CREATE OR REPLACE VIEW bronze.vendas AS
    SELECT * FROM fb.main.TABENTRADASAIDA;

CREATE OR REPLACE TABLE silver.vendas AS
    SELECT *
      FROM bronze.vendas
     WHERE DATAMOVIMENTO >= DATE '2024-01-01';

-- MinIO / S3-compatible: use CREATE SECRET, NOT the legacy
-- SET s3_* keys (those still work but DuckDB now recommends
-- the secret-based path).
CREATE SECRET IF NOT EXISTS s3_minio (
    TYPE s3,
    KEY_ID    'minioadmin',
    SECRET    'minioadmin',
    ENDPOINT  'minio.local:9000',
    URL_STYLE 'path',
    USE_SSL   false);

COPY (
    SELECT *,
           year(DATAMOVIMENTO)  AS ano,
           month(DATAMOVIMENTO) AS mes
      FROM silver.vendas
) TO 's3://lake/erp/vendas'
  (FORMAT parquet, PARTITION_BY (ano, mes));

Power BI integration — what to promise

For an MVP commercial release:

Primary: Power BI imports the Parquet produced by the DuckDB pipeline above. Stable, supported, no driver questions.
Secondary: ODBC / JDBC gateway against DuckDB. Validate in the target environment; behaviour varies by Power BI release.
Advanced: Arrow Flight SQL via GizmoSQL — useful for Python / JDBC / ADBC tooling and for Power BI integrations that ship ADBC support; do not sell this as first-class universal Power BI today. Microsoft's ADBC story is still ramping (currently anchored on specific connectors like Databricks); avoid overclaim.

Milestone v0.6 — Firebird Native Diagnostics

Strategic decision: the next phase is not firebird_materialize(). Materialization is not part of the core roadmap. DuckDB already owns materialization, local tables, Parquet export, and lakehouse handoff well. If a thin convenience helper is ever considered, it is an improvement suggestion outside the core extension roadmap and must be re-approved by the PM/HUMAN against the ACTION_GUIDE.

Materialization is DuckDB's strength. Firebird-native diagnostics is this extension's differentiator.

Delivery order for v0.6:

firebird_profile_table()
Heavy-view diagnostics
Pushdown report / explainability
firebird_pool_stats()
DECFLOAT fallback
Conservative aggregate pushdown
Adaptive parallel scan recommendations

v0.6.0 release status: published. The remaining release work is the community-extension submission path for PR #1980 and any post-release documentation alignment.

`firebird_profile_table()` analyzer

Status: released in v0.6.0. See docs/en/function_manual.md for the delivered behavior and current limitations.

Shape:

SELECT *
FROM firebird_profile_table('fb.main.TABELA');

The analyzer returns (delivered in the first version unless noted):

Object type: TABLE / VIEW. (delivered)
Whether a primary key exists. (delivered)
Primary-key columns. (delivered)
Existing indexes. (delivered)
Candidate watermark columns. (delivered, by type)
Good filter columns. (delivered, indexed + cheap-type)
Row estimate. (delivered: PK-range upper bound by default; opt-in exact COUNT(*) via exact_row_count=true)
Full-scan risk. (delivered)
Recommended partitions=N. (delivered, advisory, from PK range)
Required-filter recommendations. (delivered as structured alerts filter_before_scan / materialize_before_scan)

Acceptance is diagnostic quality, not query execution speed. The function should help users decide whether to scan live, filter harder, partition, or materialize through DuckDB/dbt/Parquet.

Heavy-view diagnostics

Status: released in v0.6.0 (folded into firebird_profile_table() via the warnings column). See docs/en/function_manual.md.

Detect and explain (delivered unless noted):

View without a primary key. (delivered)
View with joins. (delivered, token detection of JOIN)
View with aggregation. (delivered, GROUP BY / aggregate functions)
View without a selective filter. (delivered, no WHERE in definition)
Cases where DuckDB/dbt/Parquet materialization is the safer workflow. (delivered, surfaced as warnings)

The recommendation path is explicit: heavy Firebird views should often be materialized by DuckDB, dbt, or Parquet pipelines instead of hidden behind an extension-specific materialization wrapper.

Delivered as conservative token-level inspection of RDB$VIEW_SOURCE, not a SQL parser or plan analyzer. The view source text is never returned; comma-joins are not detected; unreadable definitions emit an explicit view definition not inspected warning. No new function or schema change — the existing 10-column firebird_profile_table() shape and its warnings column carry the diagnostics.

Pushdown report / explainability

Status: released in v0.6.0. Delivered by EXPANDING the existing firebird_last_query() / firebird_query_log() telemetry (no new function) — schema grew from 15 to 18 columns. See docs/en/observability.md.

Expose what the connector sent to Firebird and what DuckDB still had to apply locally. Built on the existing remote-query telemetry instead of a disconnected diagnostics surface.

The report makes these decisions visible (delivered unless noted):

projected columns (already in telemetry)
pushed filters (already in telemetry)
pushed paging / explicit row limits (delivered — limit_pushed / offset_pushed, NULL when no paging pushed)
serial vs. parallel scan (already in telemetry)
partition count (already in telemetry)
predicates or operators not pushed (already in telemetry — residual_filters)
why an operator stayed local (delivered — not_pushed_reasons, coarse: NONE_CHARSET / UNSUPPORTED_OP / ROWID_OR_INVALID_COLUMN / UNSUPPORTED_PROJECTION_MAPPING)

Delivered as structured fields in the existing telemetry path, kept factual, compact, and testable — not a cost-based optimizer, advisor, or planner replacement. Reasons are coarse tags captured at the existing pushdown-decision points; TranslateFilter was not broadly refactored.

`firebird_pool_stats()`

Status: released in v0.6.0. See docs/en/function_manual.md.

Shape: firebird_pool_stats('fb') — explicit ATTACH alias, one row per call. It does NOT enumerate catalogs (no no-argument form), reads only counters the pool already tracks, and never leases a connection.

Expose pool state so the numbers have context (delivered unless noted):

configured maximum idle size (delivered — max_idle_size)
configured idle timeout (delivered — idle_timeout_ms)
pool enabled flag (delivered — pool_enabled)
idle connections (delivered — idle_connections)
lifetime created / reused / discarded (delivered — total_created / total_reused / total_discarded)
in-use / active connections (deferred — needs a new lease counter on the pool; reported before implementing per scope)
last error, if tracked safely (deferred — no pool-level error history yet)

This closes most of the Phase 2 observability debt without making pool metrics the lead feature of v0.6, and without new pool instrumentation: the first version surfaces only what the pool already counts.

DECFLOAT fallback

Status: released in v0.6.0. See docs/en/function_manual.md.

Resolve the remaining Firebird 4+ gap with a conservative fallback. Delivered as lossless text: DECFLOAT(16) / DECFLOAT(34) are projected server-side as CAST(col AS VARCHAR(64)) and surfaced as VARCHAR, ending the previous silent-NULL behavior (the column was typed DOUBLE but always fetched NULL). No local Decimal64/Decimal128 decoder, no DOUBLE default. A lossy numeric fast path (opt-in) remains possible future work when precision loss is acceptable.

Debt: the DECFLOAT test fixture is dedicated (scripts/fixture_decfloat.sql via FIREBIRD_DECFLOAT_DB) and skips in CI. Promoting it into the main setup_test_firebird.sh fixture — with the coordinated metadata / dbt-sources test updates that a new relation forces — is a deliberate future step.

Conservative aggregate pushdown

Status: deferred (investigated during v0.6.0, not implemented). Decision recorded so the same path is not reopened blindly.

Investigation finding (DuckDB v1.5.3): there is no extension-facing aggregate-pushdown hook on TableFunction. The clean path for a COUNT(*) shortcut is get_partition_stats — the optimizer (StatisticsPropagator::TryExecuteAggregates) already folds COUNT(*) with no GROUP BY into a constant when the scan returns a PartitionStatistics with an exact count. The blocker: GetPartitionStatsInput exposes only {table_function, bind_data} and does not give the callback access to table_filters. The optimizer applies the filter after calling the callback. So inside get_partition_stats we cannot tell a bare COUNT(*) from a COUNT(*) ... WHERE .... Implementing it anyway would run a remote SELECT COUNT(*) for a filtered query, only for the optimizer to bail on the filter and fall back to a normal scan — an invisible extra round-trip and misleading telemetry on a filtered query. We do not accept that surprise cost on production Firebird.

Deferred until DuckDB exposes table filters to the partition-stats callback, or another clean hook appears, or we explicitly accept the filtered-query round-trip tradeoff. bind_operator / bind_replace are not viable here: they run before the regular bind and cannot see the LogicalAggregate above the scan.

When eventually unblocked, the first version stays deliberately small.

Initially allowed:

COUNT(*)
MIN / MAX
controlled SUM
simple GROUP BY

Out of scope:

joins
complex HAVING
calculated expressions
BLOB / TEXT
problematic charset NONE
heavy views
uncertain overflow

The analyzer should gate this work: push aggregation only when the Firebird object is simple enough and the result semantics are boring.

Adaptive parallel scan recommendations

Status: released in v0.6.0. See docs/en/function_manual.md.

Diagnostic / recommendation-only — not autotuning. firebird_scan is unchanged: nothing is parallelized automatically, and no performance gain is promised. The work refines the existing firebird_profile_table() recommended_partitions and warnings (10-column schema unchanged):

recommend partitions=N from the single-column numeric PK MIN/MAX range width only (no row count, no COUNT(*), no full scan), capped at 8 (delivered)
recommend partitions=1 even for a numeric PK when the range is small, with an explicit note (delivered)
recommend partitions=1 with a warning for views, no-PK, composite, and non-numeric PK (delivered)
emit a server-side parallelism caveat when partitions > 1 is suggested, pointing at Firebird 5 ParallelWorkers — a generic caveat, since the server's parallel setting is not probed (delivered)

Do not start with fully automatic tuning. Automatic parallelism can surprise production Firebird systems, especially when server-side parallelism is already configured. Automatic adaptation stays out of scope until the recommendation path is observable, benchmarked, and boring across Firebird 3/4/5.

Limitation: the recommended_partitions > 1 branch is not exercised by a CI fixture — a wide-range numeric-PK table would force a new relation into the main fixture and cascade metadata / dbt-sources test updates. The span heuristic is deterministic and covered by code; the = 1 paths and the warnings are covered by firebird_profile_table.test.

v0.6 release closure

v0.6.0 is published for real production use. The release gate completed: Windows build, GitHub Linux/Windows checks, FB 3/4/5 matrix, local Docker community-path simulation, and Docker smoke. After v0.6.0, the roadmap accepts only core stabilization and community-submission work unless PM/HUMAN explicitly opens a new milestone:

release-blocking build, portability, packaging, or CI defects;
regressions in Firebird scan, ATTACH, type mapping, pushdown, telemetry, pool behavior, or diagnostics;
documentation required for users to run the released extension safely;
community-extension metadata required to update PR #1980.

Known non-blocking technical debt stays recorded instead of delaying v0.6:

row estimates and structured required-filter recommendations in firebird_profile_table(); (delivered post-v1.0.2)
active/in-use count and last_error in firebird_pool_stats(); (delivered post-v1.0.2)
promoting the DECFLOAT fixture into the main CI fixture; (delivered post-v1.0.2 as main-suite execution: firebird_decfloat.test runs in the canonical suite step on every FB4/FB5 leg and in local bootstraps on Firebird 4+; DECVALS deliberately keeps its own database because the main fixture's relation lists are pinned by static cross-version expectations)
exercising recommended_partitions > 1 in CI; (delivered post-v1.0.2: firebird_partitions_scan.test, dedicated sparse-wide-PK fixture, all three Firebird legs)
aggregate pushdown, blocked by DuckDB v1.5.3 API limitations.

Release-testing checklist (run before every push/tag/release)

Completed for v0.6.0 and required again before future publication:

HUMAN final acceptance test for the complete v0.6 feature set.
Windows native build and Firebird sqllogictest group green.
Docker/Linux gcc community-path simulation green from a fresh recursive clone of the committed HEAD.
Optional but recommended: Docker smoke test that loads the extension and runs at least one synthetic Firebird query end-to-end.
git status clean on the release branch.
PT/EN docs parity checked for every user-visible change.
Changelog / release notes prepared.
Version and tag approved by HUMAN.
community-extensions/description.yml updated to the approved tag/ref.
Explicit handling plan for duckdb/community-extensions#1980.

Release-test notes:

The extension must not link libfbclient at build time; it must continue to load the client library at runtime.
Tests against C:\Athenas\restaurado.fdb may read metadata and aggregate counts only. Do not save, export, materialize, log, commit, or push real data from that database.
New release artifacts, Docker scratch files, and local simulation files must stay ignored unless they are intentionally part of the public source.

Milestone v1.x — Arrow-native scanner (deferred)

A firebird_arrow_scan function that emits arrow::RecordBatch directly from the XSQLDA fetch loop — bypassing the Vector / DataChunk step DuckDB normally drives. Only worth building when there's a concrete consumer that benefits measurably over the current DuckDB→Arrow conversion path. v0.5 deliberately stays out of this and keeps the README's "Arrow note" honest.

Milestone v1.0 — Distribution

8. Community-extensions submission

community-extensions/description.yml already exists. Process:

Tag the approved v0.6 release on this repo.
Fork duckdb/community-extensions.
Copy description.yml to extensions/firebird/description.yml, set repo.ref to the approved v0.6 tag.
Open PR; their CI builds Linux/Windows/macOS binaries from the tag.
If PR #1980 already exists, update it explicitly to the new v0.6 description/ref instead of opening a conflicting submission.

Acceptance: INSTALL firebird FROM community; LOAD firebird; works on a vanilla DuckDB CLI on three platforms.

9. (Deferred — depends on upstream) Stable C ABI

The DuckDB Stable C ABI (duckdb_extension.h) currently does not support StorageExtension. Migrating today would mean dropping ATTACH 'firebird://…' AS fb (TYPE firebird). Watch duckdb/duckdb#… for the catalog C API; when it lands, port firebird_storage.cpp to it. Until then, ship per-DuckDB-minor-version binaries via community-extensions.

Out of scope (rejected for now)

Writes — INSERT / UPDATE / DELETE / CREATE TABLE against fb.main.…. The use case is "lift Firebird OLTP data into DuckDB for analytics", not "use Firebird through DuckDB". Adding writes changes the transaction profile (isc_tpb_read is hard-wired) and the risk envelope (a bug deletes user data).
DDL emission — firebird_attach_sql() already covers the view- based path; replicating Firebird's DDL inside DuckDB is a much bigger surface than the wins justify.
Replacement scan (so SELECT * FROM 'file.fdb'.EMPLOYEE Just Works) — neat trick but ATTACH is the right surface for a multi-table source.

Additional non-core suggestions kept outside the roadmap:

firebird_materialize() as a roadmap item - DuckDB already owns materialization, local tables, Parquet export, and lakehouse handoff. A future convenience wrapper may be discussed as a separate improvement suggestion, but it is not part of the core extension roadmap.
CDC / ETL / orchestration / governance features - these belong to dbt, Airflow, Dagster, dlt, Meltano, CDC tools, or data-governance systems. The extension stays focused on safe, observable Firebird access from DuckDB.

FilesExpand file tree

roadmap.md

Latest commit

History

roadmap.md

File metadata and controls

duckdb-firebird — Roadmap

Compatibility matrix

Milestone v0.4 — Firebird 4/5 live coverage

1. FB4 + FB5 docker fixtures (foundation)

2. Live type verification — INT128 / TIMESTAMP_TZ / TIME_TZ — DONE

3. DECFLOAT (DEC16 / DEC34) — silent NULL fixed in v0.6

4. FB5 compatibility sweep

5. GizmoSQL smoke test — scripts shipped, CI wiring pending

Milestone v0.5 — Pushdown completeness

6. LIMIT pushdown — deferred (upstream gap)

7. LIKE prefix pushdown — DONE

Milestone v0.5 — Analytics performance

9. row_offset + physical paging — DONE

10. Prepared statements with bind variables — second

Part (a): bind variables — DONE

Part (b): LRU statement cache — deferred

11. Predicate pushdown — fill the gaps — DONE (NOT IN + NOT bool)

12. Automatic LIMIT pushdown — contingent on DuckDB API

13. Discovery / metadata coverage tests — DONE

13b. (Historical placeholder — see above)

Milestone v0.5.x — Observability + connection pool

14. Remote-query telemetry — DONE (v0.5.2 / v0.5.3)

15. Configurable connection pool — DONE (v0.5.4 candidate)

Hotfix v0.5.5 — runtime libfbclient loading

Hotfix v0.5.6 — restore empty vcpkg.json manifest

16. dbt sources generator — IMPLEMENTED

Platform — separation of concerns

Power BI integration — what to promise

Milestone v0.6 — Firebird Native Diagnostics

firebird_profile_table() analyzer

Heavy-view diagnostics

Pushdown report / explainability

firebird_pool_stats()

DECFLOAT fallback

Conservative aggregate pushdown

Adaptive parallel scan recommendations

v0.6 release closure

Release-testing checklist (run before every push/tag/release)

Milestone v1.x — Arrow-native scanner (deferred)

Milestone v1.0 — Distribution

8. Community-extensions submission

9. (Deferred — depends on upstream) Stable C ABI

Out of scope (rejected for now)

9. `row_offset` + physical paging — DONE

12. Automatic `LIMIT` pushdown — contingent on DuckDB API

Hotfix v0.5.5 — runtime `libfbclient` loading

Hotfix v0.5.6 — restore empty `vcpkg.json` manifest

`firebird_profile_table()` analyzer

`firebird_pool_stats()`