Research report: KarhutlaSQL must become a policy engine, not just a gateway
KarhutlaSQL's current feature list (auth, OIDC, per-catalog permissions, session kill, metrics, queuing, JSON audit, TLS/mTLS, timeouts, CLI) is already on par with GizmoSQL Core plus part of Enterprise, but it has not yet touched the layer bank auditors look for most: row/column-level access control tied to data classification, a per-column trail of PII access that can answer data-subject requests within 3x24 hours, and proof of integrity for OJK reporting figures. This research finds that Indonesian regulation never mentions "row-level security" or "masking", but it does require classification of personal data (POJK 11/2022 Article 45), a security level matched to sensitivity (PDP Law Article 35), an audit trail of all IT activity (POJK 11/2022 Article 54), and processing records plus proof of compliance (PP 33/2026 Articles 74 and 138), with sanctions of up to 2% of annual revenue taking full effect on 16 January 2027 (Rajah & Tann). Because DuckDB has no notion of users, GRANT, HMAC, or a result cache, all of these controls have to be built in the Go server layer, and this is exactly where KarhutlaSQL can differentiate itself from GizmoSQL, whose README mentions neither RLS, masking, nor an audit log (GitHub gizmosql). Core recommendations for v1.x: tag-based masking and row filters rewritten by the server, per-column access-history audit written to WORM Parquet with 10-year retention, Trino-style resource groups on top of golang.org/x/sync/semaphore, a query_history table based on duckdb_get_profiling_info, and a Go MCP server in the same binary. For v2: DuckLake as the multi-writer path and version-locked reporting snapshots, a Delta Sharing endpoint, and a Postgres-wire listener for Power BI/Excel. An important timing note: DuckDB 2.0.0 and DuckLake 1.1 were released today, 21 October 2026, and both materially change the HA architecture options (DuckDB blog 2.0).
DuckDB 2.0 and DuckLake 1.1 change the HA map, not the governance map
Release 2.0 ("Cinnamon Teal") brings four things directly relevant to a server: aggregations now spill to disk when they exceed memory, async I/O throughout the engine, a "metrics layer rework for long-running instances", and a C API with a stable ABI so extensions no longer need rebuilding every release (DuckDB blog 2.0). The first reduces the OOM risk of bank analytical queries (aggregations per branch/segment with high cardinality) that were previously cancelled outright (DuckDB memory management); the last makes it easier for KarhutlaSQL to ship custom extensions (e.g. an HMAC UDF) via CREATE EXTENSION REPOSITORY with a signing key as an offline mirror in air-gapped environments. Quack, DuckDB's official HTTP client-server protocol, is now declared mature in 2.0 after a beta in 1.5.3, but it uses application/duckdb serialization, not Arrow Flight (DuckDB Quack); Quack is not a KarhutlaSQL competitor but a 2027 backend option for a single "primary" DuckDB process accepting writes from many Flight SQL pods.
The bigger change comes from DuckLake 1.1, declared production-ready with a PostgreSQL catalog as the recommended multi-writer/multi-reader path, replacing the "one RW process, N READ_ONLY processes, copy snapshot files" choreography (DuckDB concurrency 2.0). With DuckLake, every KarhutlaSQL pod can ATTACH 'ducklake:postgres:...' (DATA_PATH 's3://bucket/') to MinIO and write with automatically retried optimistic concurrency; MotherDuck claims ~100 transactions per second versus ~1 for older lakehouse formats, plus data inlining for small inserts and a single CHECKPOINT for flush/compact/vacuum (MotherDuck DuckLake deep dive). Time travel AT (VERSION => n), ATTACH (SNAPSHOT_VERSION n), and per-file Parquet encryption with keys in the catalog (DuckLake encryption) are free raw material for the OJK reporting features discussed below. The practical implication: do not invest v1.x effort in a file-copy read-replica mechanism; build a neutral "catalog registry" abstraction, then make DuckLake+Postgres+MinIO the v2 reference topology. What does not change in 2.0: DuckDB still has no user/role, no current_user, no HMAC, no built-in query timeout, no result cache, and no materialized views (DuckDB utility functions; Securing DuckDB). All governance remains the Go server's job.
Row- and column-level access control is the largest gap against the PDP Law
Almost every customer transaction and balance column counts as specific personal data because PDP Law Article 4 includes "personal financial data" alongside biometrics and health (Mondaq). Article 35 requires a security level matched to sensitivity, POJK 11/2022 Article 45(1)(a) requires banks to classify personal data, and SEOJK 29/2022 Appendix I.b demands least privilege, a single unique ID, and the four-eyes principle (POJK 11/2022; SEOJK 29/2022). The planned per-catalog permission only answers "may this user open the database or not"; an auditor will ask why a churn analyst can see raw NIK (national ID) and account numbers.
Industry patterns have converged and can be copied directly. Trino applies row filters as boolean expressions and column masks as SQL expressions in first-match-wins JSON rules with default deny and hot reload via security.refresh-period (Trino file-based access control); Trino-OPA defines a JSON contract returning allow, {"expression"} for row filters, and {"viewExpression","index"} for masks (Trino OPA). Snowflake and Databricks bind policies to tags, not columns, so new tables in a tagged schema are protected automatically (Snowflake tag-based policies; Databricks ABAC). Dremio uses boolean UDFs and same-typed masking UDFs that may read a user→region lookup table (Dremio row/column policies). An implementation in Go+DuckDB follows this recipe: session identity (user, groups, business unit, unmask:<tag> attributes) injected from the JWT into the DuckDB connection as session variables before lock_configuration=true; a governance catalog in a policy.duckdb file encrypted with ENCRYPTION_KEY, ATTACH (READ_ONLY) to every session, holding the tables datasets, columns, tags, column_tags, policies, policy_approvals; masking as persistent CREATE MACRO mask_nik(v), mask_rekening(v), bucket_saldo(v) in that file (DuckDB CREATE MACRO); and a SQL rewriter in the server that replaces tagged columns with macro(col) and adds the WHERE from the policy engine before execution. Without a parser, the cheap fallback is per-persona views generated from the catalog. For policy-as-code, store rules as Trino-style JSON or Rego in Git and include the policy version hash in every audit record. Stable pseudonymization needs a keyed hash that DuckDB does not have; hash() is forbidden because its values may change between versions (DuckDB utility functions), so use sha256(salt || value) with a salt from Vault/HSM, or an HMAC UDF via a 2.0 stable-ABI extension.
| Feature | Why (regulation) | Product reference | Go+DuckDB implementation | Effort |
|---|---|---|---|---|
| Session identity + attributes (groups, business unit, clearance) | SEOJK 29 single unique ID, least privilege | All | JWT claims → per-connection session variables, before lock_configuration | S |
| Default-deny object privileges (schema/table/column) | SEOJK 29 Appendix I.b 2.3.g | Trino file rules | grants table in policy.duckdb; checked in GetFlightInfo before execution | M |
| Row filter (RLS) via WHERE rewrite | PDP Law 35, 39(1) | Trino OPA expression, Dremio boolean UDF | SQL parser + predicate injection; fallback per-persona views | M |
| Dynamic + conditional column masking + hidden columns | PDP Law 4 & 35; POJK 11 Art. 45 | Snowflake CASE policy, Dremio masking UDF | Persistent CREATE MACRO; rewriter substitutes macro(col) | M |
| Tags/classification + tag-bound policies (ABAC) | POJK 11 Art. 45(1)(a) personal data classification | Snowflake tags, UC has_tag_value, Immuta | tags, column_tags tables; tag propagation to views/CTAS | M |
| Policy-as-code (JSON/Rego in Git, hot reload) | SEOJK 29 four-eyes, proof of compliance PP 33 Art. 138 | Trino security.refresh-period, OPA | File watch/SIGHUP; policy hash in audit records; optional OPA sidecar | S |
| Four-eyes for policy changes | SEOJK 29 Appendix I.b 4.1.a | Snowflake APPLY vs OWNERSHIP | policy_approvals table, pending/approved status, two distinct identities | S |
| Keyed-hash pseudonymization / token vault | PDP Law 35 (pseudonymization as a safeguard) | Snowflake external tokenization | sha256(salt‖v) with a salt from Vault, or an HMAC extension | S–M |
| Regex auto-classification (NIK, account, NPWP tax ID, phone) | Speeds up POJK 11 Art. 45 | Immuta discovery | Job scanning column samples → tag suggestions for stewards | S |
Per-column access history and proof of integrity for OJK reports
The planned per-statement JSON audit log is not enough for two questions that will certainly be asked: "who accessed customer X's data in this period" and "which version of the data was this report figure computed from". POJK 11/2022 Article 54(2) requires an audit trail of all IT activity, PP 33/2026 Article 74 requires a record of all processing activities (ROPA) including retention periods, data-subject requests must be answered within 3x24 hours after verification, and 3x24-hour breach notification must be backed by "logs and forensic reports" (Rajah & Tann; RSM Indonesia). There is no explicit log retention figure in POJK or PP 33; the conservative 10 years comes from Law 8/1997 Article 11 for financial documents (Hukumonline). The right model is Snowflake ACCESS_HISTORY: one row per statement with direct_objects_accessed, base_objects_accessed (base tables behind views, including filter/join columns), and policies_referenced (Snowflake access history). DuckDB's built-in QueryLog is insufficient because it is unstructured and has no user (DuckDB logging); the server must record the parser's resolution itself (columns touched down to the base table), the PII tags hit, and the masks applied, then write them as daily-partitioned Parquet to a MinIO bucket with object-lock. From that table, the endpoint audit.pii_access(subject_id, from, to) answers DSARs within the SLA.
For OJK reporting, the new POJK 22/2025 regime (effective 2 October 2025) digitizes all reporting through the OJK reporting system with master reports and a responsible reporting officer (OJK POJK 22/2025), while SLIK (the credit information system) requires debtor reports that are "complete, accurate, current, whole, and timely" no later than the 12th of the month, with a correction fine of Rp50,000 per debtor per day and mandatory segregation of duties between preparer, verifier, and submitter (POJK 18/2017). Server features with immediate value: snapshot-bound report runs (a report_runs(report, period, snapshot_version, sql_hash, checksum, created_by, verified_by) table that always queries AT (VERSION => n) in DuckLake), snapshot retention of at least 6 months aligned with the APOLO correction window (Summary of POJK 12/2019), signed export with a SHA-256 manifest + Ed25519 by the server, and a data-quality gate of SQL assertions that blocks the "verified" status. This is not a workflow engine, just a system table plus three statuses with different roles.
Data residency and cryptography round out this cluster. POJK 11/2022 Article 35(1) requires electronic systems in data centers and DRCs located in Indonesia; the OJK FAQ confirms core banking may not be offshore (OJK FAQ). The technical guardrail is cheap: a per-session prologue SET enable_external_access=false; SET disabled_filesystems='LocalFileSystem'; SET allowed_directories=[...]; SET lock_configuration=true, only registered catalogs can be ATTACHed, and S3 secrets are created as temporary with SCOPE 's3://bucket/' to the internal MinIO endpoint, because CREATE PERSISTENT SECRET writes credentials unencrypted to disk (DuckDB secrets manager). DuckDB at-rest encryption (AES-256-GCM since 1.4) is suitable for policy/audit/cache files, but DuckDB itself states it does not yet meet NIST requirements and does not cover key rotation (DuckDB encryption blog); make LUKS/MinIO KES/HSM the primary layer and fetch the ENCRYPTION_KEY from Vault at startup.
| Feature | Why (regulation) | Product reference | Go+DuckDB implementation | Effort |
|---|---|---|---|---|
| Per-column access history (base objects, PII tags, applied policies) | POJK 11 Art. 54(2); PP 33 Art. 74, 138; 3x24-hour DSAR | Snowflake ACCESS_HISTORY, Trino event listener | Parser + rewriter output → daily Parquet in MinIO object-lock | M |
| Configurable audit retention ≥10 years + CSV/Parquet export for OJK | Law 8/1997 Art. 11; semi-annual internal audit reports | Ranger audit to HDFS/Solr | MinIO lifecycle; audit export --period job | S |
audit.pii_access(subject_id) endpoint | PDP Law Art. 5-13 access rights; breach notification | — | Query over the audit table via the recorded row filter | S |
| Purpose tagging + ROPA registry | PP 33 Art. 74; PDP Law Art. 24 proof of consent | Immuta purposes/intents | x-purpose header matched to a per-dataset purposes table; reject if unregistered | M |
| Policy/grant change log with approver | SEOJK 29 four-eyes | Snowflake object_modified_by_ddl | DuckDB 2.0 triggers on policy.duckdb or a log from the admin API | S |
| Snapshot-bound report runs + signed export + DQ gate | POJK 22/2025; POJK 18/2017 Art. 4, 8, 30 | DuckLake time travel | report_runs, AT (VERSION => n), Ed25519 manifest, SQL assertions | M |
| Per-session residency/egress guardrail | POJK 11 Art. 35; Art. 17 | GizmoSQL INIT_SQL_COMMANDS | Hardening prologue + SCOPE'd temporary secrets | S |
| External key management (Vault/HSM → ENCRYPTION_KEY, S3 secrets) | SEOJK 29 cryptographic methods | — | Fetch at startup; rotation via COPY FROM DATABASE | S–M |
| Access anomaly detection (PII volume, outside working hours) | SEOJK 29 Chapter IV item 4 | — | Rules over query_history → Prometheus/Grafana alerts | S |
Workload management and observability decide whether the server survives in production
The DuckDB knobs that actually exist are per instance: memory_limit, threads, max_temp_directory_size, temp_directory, and lock_configuration + allowed_configs (DuckDB configuration), plus per-connection duckdb_interrupt and duckdb_query_progress (DuckDB C API). There are no resource groups, priorities, or per-user quotas. The planned statement queuing should be designed directly as Trino-style resource groups: hardConcurrencyLimit, maxQueued (exceeding it → reject), softMemoryLimit, fair/weighted scheduling, and first-match selectors on user, userGroup, source, queryType, clientTags (Trino resource groups). Dremio adds two separate timeouts, an enqueued time limit defaulting to 5 minutes and a runtime limit, plus a reject action in routing rules (Dremio WLM); ClickHouse adds per-interval quotas (queries, read_rows, execution_time per hour) that reset on restart (ClickHouse quotas). In Go: a weighted semaphore per group with a bounded queue, rejection with a retriable gRPC RESOURCE_EXHAUSTED (GizmoSQL uses UNAVAILABLE when --max-sessions is reached, with admin bypass (GizmoSQL docs)), queued and execution timeouts calling duckdb_interrupt, and a client timeout that cancels the query when the DoGet stream is abandoned. Hard memory isolation is only possible with separate DuckDB instances per load class, exactly the Watershed pattern that runs 30 GB and 180 GB variants behind a router and reaches >95% success rate (Watershed). Cost estimation is not worth pursuing: Dremio itself admits query_cost() is not a reliable predictor; use duration history per normalized query hash.
The result cache is the feature with the highest benefit/effort ratio for BI dashboards: results are already Arrow IPC (the Flight wire format, no re-encoding), key = hash(normalized SQL + parameters + identity + session options), default TTL 60 seconds, admission by min_query_duration/min_query_runs, a 20 MB per-entry limit, not shared between users unless privileges are identical, and skipped for non-deterministic functions, following the semantics of ClickHouse (ClickHouse query cache) and Dremio (Dremio reflections). With DuckLake, the catalog snapshot ID goes into the cache key so invalidation is equivalent to Snowflake's "data did not change". Materialized views can simply be tables refreshed on a schedule by an internal scheduler; IVM for DuckDB is only a research prototype without join support (cwida/ivm-extension).
Observability beyond Prometheus is built from per-connection duckdb_get_profiling_info after each query with limited tracked_metrics, not global enable_logging, which risks capturing secrets (DuckDB profiling). A query_history table (query_id, session, user, resource_group, sql_hash, redacted sql_text, state, queued_ms, exec_ms, rows_returned, bytes_sent, peak_memory, rows_scanned, cache_usage, error) also becomes the source for the slow-query log (store EXPLAIN ANALYZE (FORMAT json) only for slow queries), daily chargeback per team from JWT claims, and quota input. OTel via the otelgrpc interceptor gives a span per Flight RPC with the attributes enduser.id, flightsql.resource_group, flightsql.queued_ms, sent to an on-prem Collector + Tempo; the ADBC Go driver already supports client-side tracing (ADBC Flight SQL driver). On the operations side, tiered backup uses COPY FROM DATABASE (hot) and EXPORT DATABASE (FORMAT parquet) (cold, portable across storage versions) (DuckDB EXPORT), and for DuckLake it is enough to back up the Postgres catalog plus MinIO bucket versioning. An admin API as custom Flight DoActions (list/kill session, cancel query, reload config, clear cache, attach/detach catalog, snapshot_now, health) and a readiness probe based on a GizmoSQL-style health-check query complete the Kubernetes deployment: a writer StatefulSet + reader Deployment, preStop = gRPC drain + CHECKPOINT.
| Feature | Why | Product reference | Go+DuckDB implementation | Effort |
|---|---|---|---|---|
| Resource groups + selectors + two timeouts (queued/exec) + client timeout | Multi-team stability; SEOJK 29 availability | Trino resource groups, Dremio WLM | x/sync/semaphore, bounded queue, duckdb_interrupt, YAML reload on SIGHUP | M |
| DuckDB instance per load class/tenant | Hard memory isolation | Watershed, Duckgres | *sql.DB pool per group with different memory_limit/threads | M |
| Quotas per user/team per interval | Chargeback; abuse prevention | ClickHouse quotas | Counters from query_history, reset on restart | S |
| Arrow IPC result cache | BI dashboards; CPU savings | ClickHouse query cache, Snowflake | In-process/disk LRU, key including identity + snapshot ID | M |
| Scheduled MVs (refreshed tables) | Heavy daily reports | Dremio reflections (pattern) | Internal scheduler CREATE OR REPLACE TABLE on the writer pod | S |
query_history + slow-query log + chargeback | IT audit, capacity | ClickHouse system.query_log, MotherDuck RECENT_QUERIES | duckdb_get_profiling_info + __admin table | M |
| OpenTelemetry tracing | 24-hour/5-day incident forensics | ADBC Go OTel, Flight tracing middleware | otelgrpc interceptor + custom attributes | S |
| Admin API via DoAction + health-check query | 24/7 operations | GizmoSQL --health-check-query, MotherDuck interrupt | Admin role; every action audited | S |
| Tiered backup + versioned schema migrations | POJK 11 DRP tested annually | — | COPY FROM DATABASE, EXPORT DATABASE, schema_migrations table | S |
| DuckLake multi-writer topology + catalog registry | HA without file choreography | DuckLake 1.1 | ATTACH ducklake:postgres per pod; re-attach without restart | M (v2) |
Bulk ingest CommandStatementIngest, PollFlightInfo, full GetSqlInfo | JDBC/ADBC drivers enable the right paths | Flight SQL spec, arrow-go v18.8 BaseServer | duckdb-go appender; duckdb_query_progress; advertise FLIGHT_SQL_SERVER_CANCEL | M |
AI integration and the client ecosystem decide adoption inside the bank
An MCP server is the cheapest and most valuable AI-era feature because KarhutlaSQL already has auth, policy, and timeouts; MCP is just UX. GizmoSQL-MCP (TypeScript) exposes list_catalogs/schemas/tables, describe_table, run_query with a hard cap of 500 rows and a 60-second timeout, explain_query, an opt-in execute_statement marked destructive, and DDL resources; stdio + Streamable HTTP transports as an OAuth 2.1 resource server (GizmoSQL-MCP; MCP changelog 2025-06-18). KarhutlaSQL should write it in Go in the same binary with an identical tool set, validating internal Keycloak JWTs via the already planned JWKS. Text-to-SQL guardrails must be enforced in the server, not in the prompt: a real SQL parser that only accepts one SELECT, LIMIT injection, tenant scoping from the authenticated session, and audit per tool call (Draxlr). An ai_sandbox connection profile (read_only + enable_external_access=false + allowed_directories + lock_configuration + a small memory_limit) is DuckDB's official recommendation for untrusted SQL (Securing DuckDB). The replacement for Cortex Analyst/Genie in an air-gapped environment is a system table verified_queries(name, question, sql, verified_by, verified_at) plus a YAML semantic model (tables, relationships, metrics, dimensions) exposed as an MCP resource (Snowflake VQR). Vector search remains "preview": the VSS extension is explicitly not recommended for production because persistence is experimental and indexes risk corruption (DuckDB VSS); brute-force array_cosine_distance is safe for 1–2 million call-center transcript vectors.
On the client side, the ADBC Flight SQL drivers (C/C++, Python, Go, R, C#, Rust) and the universal JDBC driver come for free, but with two holes: the Flight SQL JDBC driver does not support parameterized queries and getCatalog() is empty (Dremio JDBC), and Power BI provides no generic ADBC connector; refresh through the on-premises gateway stays on ODBC until Spring 2027 (Microsoft transition to ADBC). For a bank that depends on Power BI and Excel, the realistic paths are Dremio's Flight SQL ODBC 0.9.0 (Windows, performance not yet optimized, cancel does not reach the server (Dremio ODBC)) or a secondary Postgres wire protocol listener in front of the same engine, the Duckgres pattern (Go, MIT) that also provides per-user file isolation and a control plane routing sessions to workers over Flight SQL on a Unix socket (PostHog/duckgres). Metabase has no Flight SQL path at all (Metabase DuckDB driver), so Postgres-wire is the only door. Grafana already has Flight SQL plugins from GizmoSQL and InfluxData with alerting (Grafana GizmoSQL plugin); if wire-compatible, alerts on query results come for free. Superset needs a karhutlasql:// SQLAlchemy dialect on top of adbc_driver_flightsql. dbt v2 bundles embedded DuckDB via ADBC and supports DuckLake in catalogs.yml, but has no host/port notion for a remote DuckDB (DuckDB blog dbt Fusion); the pragmatic path is dbt running against the same DuckLake Postgres catalog as KarhutlaSQL, or a thin dbt-karhutlasql adapter. Airflow already provides an AdbcHook with a Flight SQL backend (Airflow apache-arrow provider), so orchestration does not need to be built. For data sharing between units or subsidiaries, Delta Sharing is an open protocol with >15 clients including Power BI and Tableau, based on short-lived pre-signed URLs (VLDB 2025); a Go endpoint on top of DuckLake/Parquet in MinIO is a differentiating v2 feature.
| Feature | Why | Product reference | Go+DuckDB implementation | Effort |
|---|---|---|---|---|
| Go MCP server (stdio + Streamable HTTP, OAuth 2.1 RS) | Internal agents, on-prem Claude/Copilot | GizmoSQL-MCP, MotherDuck MCP | Same binary; identical tool set; Keycloak JWKS | M |
ai_sandbox connection profile + SELECT-only parser + LIMIT injection | SQL from an LLM = untrusted | Securing DuckDB, Draxlr | Role → profile; Go parser; Interrupt() | S |
| Verified queries + YAML semantic model as MCP resources | Air-gapped text-to-SQL accuracy | Snowflake VQR, Databricks Metric Views | System table + resource endpoint | S–M |
| SQLAlchemy dialect + ADBC/JDBC documentation | Superset, notebooks | Voltron Data, GizmoSQL | Thin Python package | S |
| Compatible Grafana datasource | Free alerting, ops dashboards | GizmoSQL/InfluxData plugin | Wire compatibility testing; fork if needed | S–M |
| Secondary Postgres-wire listener | Power BI ODBC gateway, Excel, Metabase | Duckgres, Buena Vista | Go pgwire listener forwarding to the same engine + policy | L (v2) |
| Scheduled jobs (cron SQL, target table, webhook) | MV refresh, DQ gate, AML batch | MotherDuck Flights | jobs table + scheduler on the writer pod | S |
GitOps policy CLI karhutlasql policy apply + embedded mode | DX, control evidence in Git | Collibra access-as-code (pattern) | Idempotent YAML; karhutlasql.NewServer(db) library | S–M |
| Delta Sharing endpoint | Sharing version-locked data products across units | Delta Sharing reference server, Kotosiro | Go REST + MinIO pre-signed URLs + DuckLake snapshots | L (v2) |
HTTP/WebSocket POST /v1/query → Arrow IPC | Vue/DuckDB-Wasm front ends without a Python proxy | gizmodata websocket proxy, DuckDB-Wasm | Go handler, same JWT auth | S |
Priorities: v1.x locks in compliance, v2 locks in scale
The PP 33/2026 deadline (full sanctions on 16 January 2027) and OJK PADK 2026 (effective 1 March 2026, revoking SEOJK 21/2017, covering personal data protection and internal audit (PADK 1/2026 abstract)) put governance in v1.x and scale in v2.
| Priority | v1.x (Q4 2026 – Q1 2027) | v2 (2027) |
|---|---|---|
| Must | Session identity + attributes; default-deny object privileges; column masking + tags/classification; row filters via rewrite; per-column access history to WORM Parquet, 10-year retention, OJK export; residency/egress guardrail + SCOPE'd temporary secrets; resource groups + two timeouts + client timeout; query_history + slow-query log; ai_sandbox profile + SELECT-only parser; DoAction admin API + health check | DuckLake (Postgres + MinIO) as the reference multi-writer topology; snapshot-bound report runs + signed export + DQ gate; instance per load class/tenant; Postgres-wire listener |
| Should | Policy-as-code JSON/Rego in Git + hot reload + four-eyes; audit.pii_access endpoint; Arrow IPC result cache; OTel tracing; per-team quotas + chargeback; Go MCP server; bulk ingest + PollFlightInfo + full GetSqlInfo; tiered backup + schema migrations; Vault/HSM key management; GitOps policy CLI | Purpose tagging + ROPA registry; Delta Sharing endpoint; verified queries + semantic model; official Grafana datasource; thin dbt adapter; embedded mode |
| Could | Regex auto-classification; simple scheduled jobs; scheduled MVs; HTTP/WebSocket Arrow IPC endpoint; rule-based access anomaly detection | Vector search preview (brute force); Terraform provider on top of the policy CLI; Quack as the primary backend; aggregation policy (minimum group size) |
Features that should not be built in-house, because they already exist, are immature, or sit at the wrong layer: pipeline orchestration (Airflow AdbcHook and Temporal already exist; KarhutlaSQL only needs a simple SQL scheduler); a full alert engine (Grafana alerting on top of a Flight SQL datasource); an identity provider and user directory (on-prem Keycloak/AD via the already planned OIDC/JWKS, do not store passwords in the server); an in-house ODBC driver (use Dremio 0.9.0 or wait for ADBC consolidation; a Postgres-wire listener gives wider coverage for similar effort); persistent HNSW or a vector database (VSS is not production-ready, is not counted against memory_limit, and its index cannot be recovered after a crash); true IVM/materialized views and Dremio-Reflections-style automatic query rewrite (needs access to DuckDB's optimizer); a query cost estimator (Dremio itself says it is unreliable); at-rest encryption as the only layer (DuckDB is not yet NIST-compliant and has no key rotation; LUKS/MinIO KES/HSM remain mandatory); a native CDC connector (Debezium → Kafka/Redpanda → consumer, or DuckLake data inlining); file-copy read replicas (DuckLake 1.1 makes them obsolete); a custom Kubernetes operator before StatefulSet/Deployment + Helm have proven insufficient; and a full Cube-style semantic layer (YAML + verified queries is enough; Cube/Malloy can be layered on top if needed).
Conclusion
The most important shift from this research is how to position KarhutlaSQL: not "GizmoSQL in Go" but a policy enforcement point for bank analytical data, with DuckDB as an engine that is deliberately kept ignorant of identity. Everything DuckDB lacks (users, GRANT, RLS, masking, HMAC, result cache, resource groups, access history) is exactly the space for product differentiation, and everything DuckDB 2.0/DuckLake 1.1 newly provides (aggregation spill, stable ABI, multi-writer, time travel, per-file encryption) is raw material that should be used as-is, not imitated. One design decision determines almost every Must feature: having a SQL parser and rewriter on the GetFlightInfo path. Without it, masking, row filters, per-column access history, LIMIT injection, and query-type classification all degrade to per-persona views and regexes. Invest in the parser first.
Remaining uncertainties should be verified before committing effort: Appendix I of OJK PADK 2026 (75 pages) has not been read and likely contains detailed access-control provisions replacing SEOJK 21/2017; the behaviour of SET VARIABLE/getvariable() with respect to lock_configuration has not been tested; SAVEPOINT support in DuckDB and MinIO object lock with DuckLake have not been confirmed; and the final OJK reporting file format under POJK 22/2025 has not been obtained from a primary source. All four are one-day verification tasks, and their outcome only shifts implementation details, not priorities.