samber/developer-platform-skills已通过检查
SKILL DETAIL
sql-jdbc-access-design
samber/developer-platform-skills/sql-jdbc-access-design
Design customer-facing SQL access to a product's data - a JDBC/ODBC endpoint, warehouse share, or hosted query surface. Covers the architecture gate by scan frequency (zero-copy share, replicated copy, per-tenant compute isolation), engine-level tenant isolation with secure views and row-level security, an additive-only schema-stability contract enforced in CI, BI-tool connectivity and driver certification, query governance and cost caps, short-lived credential issuance, and the pricing shape. Use whenever the user mentions JDBC, ODBC, SQL access for customers, connecting Tableau or Power BI to product data, or pricing a SQL add-on - even if they never say "SQL access". Do NOT use for bulk file delivery - use samber/developer-platform-skills@bulk-data-sharing-design instead.
安装量 · 322查看来源
Installation
npx skills add https://github.com/samber/developer-platform-skills --skill sql-jdbc-access-design
技能文件
SKILL.md
最近同步 · 2026年10月23日
evals/evals.json›
{
"skill_name": "sql-jdbc-access-design",
"evals": [
{
"id": 1,
"prompt": "We're Trellisway, HR analytics SaaS, about 180 customers, all our data sits in one Snowflake account. Customers keep asking for direct access so they can join our headcount data with their own finance data. Here's what's on the whiteboard: create one view per customer (v_headcount_acme etc.) with WHERE customer_id = 'acme', plus one catch-all view that filters on CURRENT_USER() so we don't have to maintain 180 of them, then put the views and the base fact table into the share so customers can drill down when the view doesn't have enough detail. Our app has done tenant filtering in the API layer for four years and we've never leaked anything. QA would verify it by querying each view from our own account and eyeballing the customer_id column before we flip it on. Poke holes in this before we build it.",
"expected_output": "A review that kills the CURRENT_USER() predicate as NULL across a share, replaces the per-customer views with a single secure view joined to a private entitlement table on the share-aware account identity, keeps base and mapping tables out of the share, rejects the app-layer filter as evidence of safety, and replaces provider-side eyeballing with a consumer-side zero-foreign-rows probe gate.",
"files": [],
"expectations": [
"States that CURRENT_USER() and CURRENT_ROLE() return NULL inside a secure view once it crosses an account-to-account share, so the catch-all view matches nothing or everything depending on the predicate's shape",
"Names CURRENT_ACCOUNT() as the share-aware identity function that is safe across a share boundary",
"Prescribes a single secure view joining a private base table to a private entitlement or mapping table on the caller's account identity, instead of one view per customer",
"States the base fact table and the entitlement mapping table must never enter the share",
"Rejects putting the base fact table into the share for drill-down",
"Rejects the four-year clean record of the application-layer tenant filter as evidence this design is safe, on the grounds that an external SQL handle leaves no application layer to intercept a query",
"States the engine-level policy must make a query that omits the tenant filter return nothing, rather than relying on every query being written correctly",
"Rejects verifying isolation by querying the views from the provider's own account",
"Requires probing isolation from the consumer side, naming Snowflake's SIMULATED_DATA_SHARING_CONSUMER session parameter or a second test account",
"Sets the isolation gate as zero foreign rows across every shared view and policy, as a binary pass/fail check before GA",
"Requires the isolation probe to be re-run after every policy or schema change, not only once at launch",
"Notes the same NULL-identity trap applies to row-access policies, where IS_DATABASE_ROLE_IN_SESSION() is the equivalent primitive"
]
},
{
"id": 2,
"prompt": "Paceline Metrics here, logistics visibility SaaS, everything already lands in BigQuery. Two groups are asking for SQL access. Six enterprise accounts (six figures each) want to park a shipment-performance dashboard on it; each one has roughly 50 people opening it every weekday morning, and the tables behind it run about 300 GB per customer. Then about 40 mid-market accounts who'd run a handful of ad-hoc queries a week against maybe 2 GB each. My instinct is that the enterprises are paying the most so they get dedicated infrastructure, and mid-market shares one pooled setup. Does that hold up? What would you actually build?",
"expected_output": "An architecture recommendation that rejects contract value as the gate, ranks the three rungs, keeps the low-frequency mid-market cohort on a zero-copy share, and steps the 50-viewer daily dashboard cohort up to a replicated copy or read endpoint using the scan-frequency tipping heuristic.",
"files": [],
"expectations": [
"States that scan frequency, not customer size, tier, or contract value, is the variable that gates the architecture",
"Rejects assigning dedicated infrastructure to the enterprise cohort on the basis of contract value",
"Presents three architecture rungs: zero-copy warehouse share, replicated copy or dedicated read endpoint, and per-tenant compute isolation",
"Names zero-copy warehouse sharing as the default rung and justifies it by the data already living in a cloud warehouse",
"States the zero-copy share is a metadata grant with no data copied, and that live access is paid per query scan plus cross-region egress",
"Cites the tipping heuristic of roughly 20 scans per day on a roughly 200 GB table, past which live re-scanning costs more per month than a replicated copy costs per year",
"Labels that tipping heuristic as one practitioner's rule of thumb rather than a benchmark or a measured figure",
"Places the 50-viewer weekday-morning dashboard cohort past the tipping line and recommends the replicated copy or dedicated read endpoint rung for it",
"Keeps the roughly 40 low-frequency mid-market accounts on the zero-copy share rung",
"Names the price of the replicated rung: staleness to the last refresh, plus a second copy of the data and its PII to govern",
"Names the two conditions that promote per-tenant compute isolation (arbitrary customer-authored SQL at dashboard-or-agent frequency across many tenants, or a shared cluster whose worst-case query already threatens the product) and does not promote it here on contract size",
"Warns against pointing the replicated copy at the live operational schema"
]
},
{
"id": 3,
"prompt": "Bellcurve Health, clinical ops SaaS. Single Postgres 16 primary, no warehouse anywhere in the stack, about 900 customer orgs sharing one database. Enterprise customers want to point their own analysts at us directly, so we want to stand up a read replica they connect to with psql and Metabase. The plan: turn on RLS across the ~40 tenant tables, policy is USING (tenant_id = current_setting('app.tenant_id')::uuid), and our API sets app.tenant_id from the X-Org-Id header when it opens the connection. We also have four reporting views sitting on top of those tables that we'd expose as-is. Half the team would rather skip all of this and just give each customer its own Postgres schema. Tell me what's wrong here.",
"expected_output": "A teardown naming the FORCE RLS gap, the client-supplied tenant identity, the missing WITH CHECK, the OR-composition and view-bypass traps, the per-row function cost, plus rejection of schema-per-tenant at this scale and of exposing the live operational schema, with the zero-copy rung deleted because there is no warehouse.",
"files": [],
"expectations": [
"Requires ALTER TABLE ... FORCE ROW LEVEL SECURITY, explaining that the table owner otherwise bypasses RLS entirely",
"Rejects deriving app.tenant_id from the client-supplied X-Org-Id header and requires tenant identity injected server-side from a verified token",
"Requires WITH CHECK alongside USING on write-path policies, because USING filters only reads and a USING-only policy lets a tenant write rows attributed to another tenant",
"Warns that multiple permissive policies on a table combine with OR, so a single USING (true) policy silently defeats every stricter policy, and requires auditing all of a table's policies together",
"Requires WITH (security_invoker = true) on the four reporting views, since a view otherwise runs with its creator's privileges and returns every row regardless of caller",
"Warns about infinite policy recursion when a helper function called from a policy reads another RLS-protected table, and prescribes SECURITY DEFINER with SET search_path pinned",
"Recommends wrapping a per-row function call in a subquery so the predicate evaluates once per query rather than once per row",
"Requires indexing every column a policy predicate references, tenant_id first",
"States that RLS does not itself grant SELECT/INSERT/UPDATE/DELETE, so matching GRANTs and column grants are still required",
"Rejects schema-per-tenant at roughly 900 tenants, naming connection pooling and DDL migrations as what breaks down",
"States that an OLTP-only stack with no warehouse deletes the zero-copy sharing rung entirely rather than demoting it",
"Rejects exposing the live operational schema to customers and requires a curated mart layer replicated from the system of record"
]
},
{
"id": 4,
"prompt": "Quarrystone, e-commerce ops SaaS. We've had a Snowflake share live for seven months with 30 customers on it, built straight on our production tables because that was the fastest way to ship. Two changes land next sprint: renaming cust_ord_dt to order_placed_at because nobody can read the old name, and changing order_total from NUMBER to a string carrying the currency code now that we're going multi-currency. Plan is to announce both in the changelog two weeks ahead, and cut a 'share v2' next quarter for anything bigger than that. One of our customers has also built a certified Tableau connector against us. How should we actually be managing schema change on this surface?",
"expected_output": "A schema-stability contract: additive-only with expand/contract, both proposed changes rejected as in-place, CI enforcement instead of a changelog announcement, a four-part contract, a curated mart layer replacing the production tables, and a warning that the certified connector is a second stability surface.",
"files": [],
"expectations": [
"States that once customers query the tables the schema is an API: columns are fields, tables are endpoints, and their dashboards are clients",
"Adopts an additive-only rule: add columns and tables freely, never rename, retype, or drop in place",
"Rejects both the in-place rename of cust_ord_dt and the in-place retype of order_total",
"Prescribes expand/contract in order: add the new column, deprecate the old one in the docs, migrate consumers, remove only when nothing depends on it",
"Rejects a two-week changelog announcement as sufficient governance and requires the contract be enforced in CI rather than by convention",
"Names a CI enforcement mechanism such as dbt model contracts failing the run on a breaking column change, or the datacontract CLI validating a contract against a PR before merge",
"Specifies the contract in four parts: schema (columns, types, nullability), quality (completeness thresholds), freshness (refresh SLA), and availability (uptime or latency)",
"Explains that schema changes are more dangerous than API changes because they often happen accidentally, such as a source-system update or a readability rename, and propagate downstream with no deprecation header",
"Rejects serving the live production schema and requires a curated mart layer so internal schema churn stops at the replication boundary",
"Names a shipped additive-only precedent such as Amplitude's event schema, which only ever adds tables and columns as new event types arrive",
"States the schema gate as zero breaking changes reaching the customer-facing schema past the CI contract check, and that a breaking change that ships is a gate failure even if no customer noticed",
"Warns that the customer's certified connector package is a second stability surface: a schema change can break its customization file or expected column typing even when the data contract itself is honored"
]
},
{
"id": 5,
"prompt": "Northgate Freight Analytics. We turned on Snowflake Secure Data Sharing last month and provisioned reader accounts for the 12 customers who aren't Snowflake shops themselves. Commercially it's a flat $2,000 per month per customer for SQL access. On the safety side we've set a 10-minute statement timeout on the warehouse the reader accounts use, so worst case a bad query dies after ten minutes. Two of those customers have already attached dashboards that refresh every 15 minutes. Finance asked me to confirm our downside is bounded before they sign off on the next 20 accounts. Is it?",
"expected_output": "A clear no: reader-account compute bills to the provider without a cap, the statement timeout bounds one query rather than the month, and the missing controls (resource monitor, queued-statement timeout, concurrency cap) plus the cost gate and the escalation rule are all specified.",
"files": [],
"expectations": [
"States that reader-account warehouses can consume unlimited credits billed to the provider's own account by default",
"Requires a resource monitor on every reader account and names it the single most important cost-control step of this surface, not optional hardening",
"Distinguishes the statement timeout, which bounds one query, from the spend cap or resource monitor, which bounds the month, and states the 10-minute timeout alone does not bound the downside",
"Adds a queued-statement timeout so queries do not wait indefinitely for a slot",
"Adds a concurrency cap per tenant or workload so one customer's parallel dashboard refresh cannot occupy shared compute",
"States that none of these controls are on by default and each must be configured per external warehouse or engine before GA",
"States the cost gate: every external compute handle has a statement timeout, a concurrency cap, and a spend cap before the first customer connects, audited as a pass/fail checklist per handle",
"Names the flat-price risk of the unbounded heavy user whose compute cost exceeds the flat price, acceptable only with the caps in place",
"Prescribes moving a tenant that regularly queues others or trips monitors onto isolated or larger compute",
"Explicitly rejects raising the shared cap for everyone to accommodate one heavy tenant",
"Distinguishes capping compute seconds and spend on a long-running engine from counting requests at an API gateway, and requires endpoint-level connection or query-rate limits to stay consistent with the published API rate-limit policy",
"Names the consumer-pays alternative at the same rung, where compute bills to the customer's own account, citing HubSpot's Snowflake share gated to its top data tier"
]
},
{
"id": 6,
"prompt": "Lattice Rail, fleet telemetry. Customer data volumes are wild: smallest account is about 4 GB, largest is 9 TB, and our own warehouse bill is pure consumption, we pay per byte scanned. The roadmap says SQL access ships free to everyone on Business plan and above, because our CRO thinks it's the stickiest retention feature we could add. If we ever decide to charge for it, the plan is to meter on warehouse credits consumed and pass that through with a markup. There's no hard ship date on this one, and leadership has been explicit that they want it to be a durable asset rather than a quick win. Sanity check the commercial model.",
"expected_output": "A pricing recommendation that refuses the free-feature framing, ranks the three shapes out loud, promotes the consumption rung on the two conditions present, and replaces warehouse-credit metering with a business metric the buyer understands.",
"files": [],
"expectations": [
"Rejects shipping SQL access as a free feature and requires it be packaged as a paid tier or an add-on",
"States that a price is itself a governance control on a surface that is expensive to over-serve",
"Presents three pricing shapes: flat add-on or tier gate, consumption or volume-tiered, and provider-pays-compute passthrough",
"Names the flat add-on or tier gate as the default rung because it needs near-zero billing engineering",
"Promotes the consumption or volume-tiered rung here and names both triggering conditions present: consumption-based cost exposure of its own, and customer data volumes spanning orders of magnitude",
"Rejects metering on warehouse credits because a non-technical buyer cannot predict them",
"Requires metering on a business unit the buyer already understands, citing Stripe Sigma tiering on monthly charge volume with queries themselves unmetered",
"States the pricing metric is revisable after launch, citing Sigma having changed its own pricing model after shipping",
"States the ranking explicitly across efficiency, effort, and value rather than leaving it implied by the order the options are listed in",
"Names the consumption rung as the starved option, highest value and highest effort so it loses every efficiency round, and states the condition that promotes it anyway",
"Uses the stated compounding-asset mandate and the absence of a ship date to promote the consumption rung, noting that retrofitting a meter under a shipped flat price is a customer-facing repricing"
]
},
{
"id": 7,
"prompt": "Vantridge, product analytics SaaS. Everything runs on one shared ClickHouse cluster and our query language is VQL, our own SQL-ish dialect. About 60 customers are asking to point Tableau and Power BI at their event data. Engineering's proposal: write our own Type-4 JDBC driver that speaks VQL, point it straight at the shared cluster, and get it certified with Tableau and Power BI. They've scoped it at a sprint or two and then it's done. Sales wants it live in 6 weeks. What am I signing up for?",
"expected_output": "A connectivity design that names the custom dialect as the anti-pattern, routes to a standard wire protocol over per-tenant engines, and prices the certification tax as ongoing engineering with the specific per-tool requirements, while refusing to admit customer SQL onto the shared cluster.",
"files": [],
"expectations": [
"Names the custom VQL dialect as the anti-pattern, since a language not already widely supported makes every BI-tool integration start from zero",
"Recommends speaking an existing wire protocol, specifically a Postgres wire-protocol endpoint, as the cheapest connectivity rung, because every BI tool, psql, and every ORM already ships a Postgres driver",
"States the cost of the wire-protocol rung: commitment to the Postgres dialect and its type system, and nothing exotic",
"Rejects the 'a sprint or two and then it's done' scoping and frames BI-tool support as an ongoing certification tax budgeted as continuing engineering rather than a one-time integration",
"Requires accurate SQLSTATEs if a driver is shipped, specifically SQLSTATE 28000 for invalid credentials, warning that a generic error instead can break Tableau connector behavior",
"Names signed per-tool connector packages whose signing certificates expire and must be renewed on a schedule",
"States that Power BI certification requires documented customer demand via a public ideas-forum thread and submission by pull request to Microsoft's connector repository",
"States that certification transfers distribution but not support, with first-line connectivity issues routed back to the data-source owner",
"Rejects admitting arbitrary customer SQL onto the shared production ClickHouse cluster, naming the blast radius across the whole application",
"Recommends single-tenant or per-organization engine instances fronted by one wire-protocol endpoint as the fix for the isolation and connectivity problems at once",
"Names engine drift as a distinct risk: a query engine changing results between releases or depending on enabled settings, which a vendor's own regression suite never covers across arbitrary customer query shapes",
"Requires a connection-setup page per supported client covering the URL template, the driver class or download, the auth method, and TLS stated as an explicit requirement rather than left to client defaults"
]
},
{
"id": 8,
"prompt": "Orchard Bay, retail ops SaaS. 'Data sharing' is on our quarterly plan and I'm now fairly sure the four people who put it there meant four different things. Sales wants our three biggest partner agencies to see aggregate data across the brands they manage. Engineering wants to stop one customer's monster report from slowing the shared analytics database for everybody else. Twelve customers have asked to point Looker at their own data. And two customers just want a nightly Parquet file dropped into their own S3 bucket. Head of Product wants one project that covers all of it. Where do I even start?",
"expected_output": "A disambiguation that splits the request into distinct problems, names which one is customer-facing SQL access, hands the Parquet request to bulk file delivery, and runs the consumer inventory and architecture-gating questions before recommending anything.",
"files": [],
"expectations": [
"Separates the request into distinct problems rather than designing a single surface that covers all four asks",
"Labels the partner-agency request as sharing data with partner organizations, which reuses the sharing mechanics but is not the same problem",
"Labels the shared-analytics-database contention as isolating compute per tenant inside the product's own analytics, an internal architecture concern, and states the pricing and BI-connectivity work do not apply to it",
"Labels the Looker request as giving customers a queryable SQL endpoint to their own data, and identifies it as the one needing the full design",
"States the answer to which problem this is must be written down explicitly, because a design that answers the wrong one ships the wrong isolation primitive",
"Routes the nightly Parquet drop to bulk file delivery rather than the live SQL surface, drawing the boundary at live queries versus periodic files",
"Names the sibling bulk data sharing skill as the handoff target for the Parquet request",
"Inventories the concrete consumers with their expected query shape, such as dashboard refresh, ad-hoc exploration, scheduled extract, and agent traffic",
"States that a BI-tool consumer needs a certified driver, stable column names, and predictable types, because they never read the docs, only their tool's connection dialog",
"Asks where the data lives today and asks for scan-frequency evidence with table sizes before recommending any architecture rung",
"States the design targets the most demanding consumer type present rather than the average one"
]
},
{
"id": 9,
"prompt": "Ferncroft Labs, lab-instrument telemetry SaaS running on Databricks. We're standing up Delta Sharing for 40 customers. Isolation plan: set a recipient property per customer and rely on partition filtering on tenant_id, since every table is already partitioned that way, so a recipient physically only reads their own partitions. Credentials: issue each customer a database username and password that they paste into Tableau or Power BI, and we rotate them once a year over email. Our own data team's service account has its password inline in the Airflow connection string, which has been fine internally. Review the isolation and credential design.",
"expected_output": "A review that demotes partition filtering to a performance optimization and moves isolation into a dynamic view predicate on the recipient identity, then rewrites the credential design up the ladder with key-pair JWTs for automation, OAuth with PKCE for BI tools, a stated degradation path, and self-service revocation.",
"files": [],
"expectations": [
"Rejects recipient-property partition filtering as an access-control mechanism, stating it is documented as a best-effort performance optimization",
"Requires hard isolation to come from a dynamic view predicate filtered on current_recipient(), not from partition pruning",
"Gives the credential preference order: short-lived token, then managed secret, then static password",
"Names the inline credential in the Airflow connection string as the anti-pattern that no documented path should show",
"Replaces the service-account password with key-pair authentication signing a short-lived JWT, so no password crosses the network",
"Requires marking machine identities as service-type so the engine refuses password auth for them and the password fallback is closed",
"Replaces the pasted BI-tool passwords with OAuth, using the authorization-code grant with PKCE for desktop BI tools, so the tool never stores a database password",
"States the degradation path: a client that cannot hold and refresh a token falls back to key-pair or a PAT service account, never to a static password, and requires verifying each supported client's refresh capability before promising OAuth everywhere",
"Rejects annual rotation over email as the design and requires customer self-service issuance and revocation in the product",
"Scopes every credential to one tenant's entitlement mapping by construction and states TLS as an explicit named requirement rather than a client-library default",
"Notes the industry trajectory retiring static database passwords, citing a staged deprecation that moves service users onto key-pair, OAuth, PAT, or workload identity"
]
}
],
"trigger_queries": [
{
"query": "Our customers keep asking to plug Tableau into their data in our product. Design the SQL access surface, we're on Snowflake.",
"should_trigger": true
},
{
"query": "We want to sell direct SQL access as an Enterprise add-on. What architecture and pricing shape?",
"should_trigger": true
},
{
"query": "Review our plan to expose read replicas of our production Postgres to customers. What breaks?",
"should_trigger": true
},
{
"query": "How do we give customers a JDBC endpoint into their own data?",
"should_trigger": true
},
{
"query": "Customers want to point Power BI at our product data. Where do we start?",
"should_trigger": true
},
{
"query": "Should we charge for SQL access or just bundle it into the Enterprise tier?",
"should_trigger": true
},
{
"query": "We're setting up Snowflake reader accounts for customers who aren't Snowflake shops. Anything we should watch out for?",
"should_trigger": true
},
{
"query": "Design a secure view so each customer only sees their own rows through our data share.",
"should_trigger": true
},
{
"query": "Our biggest account wants a live connection to query their data instead of the CSV exports we send them.",
"should_trigger": true
},
{
"query": "We're thinking about a Delta Sharing surface for our customers. Walk me through the design.",
"should_trigger": true
},
{
"query": "What's the right way to isolate tenants when customers can write their own SQL against us?",
"should_trigger": true
},
{
"query": "How do we stop a customer's runaway query from taking down the analytics cluster everyone shares?",
"should_trigger": true
},
{
"query": "We want to let customers connect Looker to their data in our app. What do we need to build?",
"should_trigger": true
},
{
"query": "Our schema keeps changing and it's breaking the dashboards customers built on our tables. How do we lock this down?",
"should_trigger": true
},
{
"query": "Is exposing an ODBC endpoint to customers a terrible idea?",
"should_trigger": true
},
{
"query": "We promised a customer a database connection string by Q3. What do I actually need in place first?",
"should_trigger": true
},
{
"query": "Help me design the pricing for a query add-on on top of our analytics product.",
"should_trigger": true
},
{
"query": "Customers want to run their own SQL against our warehouse. How do I make sure nobody sees anyone else's rows?",
"should_trigger": true
},
{
"query": "How should we version the tables we expose to customers?",
"should_trigger": true
},
{
"query": "What credentials should we hand customers who connect a BI tool to us?",
"should_trigger": true
},
{
"query": "We built our own query language and customers want to use Tableau with it. What are our options?",
"should_trigger": true
},
{
"query": "Planning a bring-your-own-BI-tool feature. What's the architecture?",
"should_trigger": true
},
{
"query": "Should we copy each customer's data into its own warehouse or just share it live?",
"should_trigger": true
},
{
"query": "Our data team wants to open a read-only Postgres endpoint to enterprise accounts. Sanity check this for me.",
"should_trigger": true
},
{
"query": "How do other SaaS companies package direct warehouse access commercially?",
"should_trigger": true
},
{
"query": "We need row-level security on the tables our customers query directly. What are the gotchas?",
"should_trigger": true
},
{
"query": "Write the connection docs page for customers hooking their BI tool up to us.",
"should_trigger": true
},
{
"query": "How many scans a day before it gets cheaper to replicate the data than to share it live?",
"should_trigger": true
},
{
"query": "We're on BigQuery. Can we let customers query their own slice through Analytics Hub, and what does that cost us?",
"should_trigger": true
},
{
"query": "What should our statement timeout and spend caps be for queries our customers issue?",
"should_trigger": true
},
{
"query": "One of our customers is a data team that wants to run dbt straight against us. How do we support that?",
"should_trigger": true
},
{
"query": "Give me the design for a hosted query endpoint customers authenticate into.",
"should_trigger": true
},
{
"query": "We want to monetize analytics by letting customers query their raw events. Where do we begin?",
"should_trigger": true
},
{
"query": "How do I get a JDBC driver certified with Tableau and Power BI?",
"should_trigger": true
},
{
"query": "Our agent product fires thousands of generated queries at a shared warehouse. Help me think through isolation and cost.",
"should_trigger": true
},
{
"query": "Customers hate our export API and just want to query us directly. Is that something we can ship as a product?",
"should_trigger": true
},
{
"query": "What goes into a data contract for the tables our customers can see?",
"should_trigger": true
},
{
"query": "Product put 'SQL access' on next quarter's roadmap. Help me scope it.",
"should_trigger": true
},
{
"query": "We're giving enterprise customers direct access to a mart layer. How do I keep internal refactors from breaking them?",
"should_trigger": true
},
{
"query": "Our shared ClickHouse can't take customer queries. What's the alternative architecture?",
"should_trigger": true
},
{
"query": "Should each customer get their own query engine, or do they all share one?",
"should_trigger": true
},
{
"query": "How do we guarantee a customer's connection can never see another customer's data, even if we write a bad query?",
"should_trigger": true
},
{
"query": "What's a sensible trial and tier structure for a warehouse-access add-on?",
"should_trigger": true
},
{
"query": "We want customers to be able to join our data with theirs without us shipping them files.",
"should_trigger": true
},
{
"query": "Do we need a resource monitor if we're the ones paying for the compute our customers use?",
"should_trigger": true
},
{
"query": "Draft the governance rules for customer-authored queries hitting our engine.",
"should_trigger": true
},
{
"query": "An enterprise deal is blocked on 'can we query it with our own tools'. What do we have to build?",
"should_trigger": true
},
{
"query": "We need to drop nightly Parquet files into our customers' S3 buckets. Design the partitioning and the freshness SLA.",
"should_trigger": false
},
{
"query": "Design our bulk export product: full dump vs incremental vs CDC, and what cadence we commit to.",
"should_trigger": false
},
{
"query": "Who should eat the egress cost on our customer data exports, and how do we meter it?",
"should_trigger": false
},
{
"query": "Our EU customers need their exported files to stay in-region. How do we handle residency on bulk delivery?",
"should_trigger": false
},
{
"query": "Design our public API's rate-limit tiers and which RateLimit headers we return on a 429.",
"should_trigger": false
},
{
"query": "Should we meter our REST API on request counts or move to GraphQL cost points?",
"should_trigger": false
},
{
"query": "Write our API deprecation policy: notice windows, Sunset headers, and what happens at the cutoff date.",
"should_trigger": false
},
{
"query": "URI-path versioning or date-based account pinning for our public API?",
"should_trigger": false
},
{
"query": "Design our API key format: prefix, checksum, and hashed versus encrypted storage.",
"should_trigger": false
},
{
"query": "An engineer just left the company. What's our API key rotation and revocation play?",
"should_trigger": false
},
{
"query": "Design the scopes and the consent screen for third-party apps on our OAuth server.",
"should_trigger": false
},
{
"query": "Do we need PKCE for every client on our authorization server, including confidential ones?",
"should_trigger": false
},
{
"query": "Design our API error code taxonomy and the RFC 9457 problem+json envelope.",
"should_trigger": false
},
{
"query": "Design our outbound webhook signing scheme and the retry and dead-letter policy.",
"should_trigger": false
},
{
"query": "How should we scope Idempotency-Key per caller, and what do we do on a payload mismatch?",
"should_trigger": false
},
{
"query": "Should we build a Fivetran source connector ourselves or publish a Singer tap?",
"should_trigger": false
},
{
"query": "We want to be listed in Airbyte's connector catalog. What does certification take?",
"should_trigger": false
},
{
"query": "Should we build our own app marketplace or just list on other people's?",
"should_trigger": false
},
{
"query": "What take rate should we charge on paid apps in our marketplace, and how do payouts work?",
"should_trigger": false
},
{
"query": "How do we rank higher in AppExchange search and lift our install conversion?",
"should_trigger": false
},
{
"query": "Design our public GraphQL schema: Relay connections, nullability, and depth limits.",
"should_trigger": false
},
{
"query": "Should we expose gRPC publicly or transcode it to JSON at the edge?",
"should_trigger": false
},
{
"query": "Review our REST resource naming and help us pick a pagination pattern.",
"should_trigger": false
},
{
"query": "Audit our OpenAPI reference for undocumented endpoints and missing error codes.",
"should_trigger": false
},
{
"query": "Design our developer console's information architecture and cut time to first call.",
"should_trigger": false
},
{
"query": "Design our sandbox: test key prefixes, magic test values, and simulated webhook events.",
"should_trigger": false
},
{
"query": "Design our status page components and the incident update cadence.",
"should_trigger": false
},
{
"query": "Partners need to see their failed webhook deliveries and replay them. Build that surface.",
"should_trigger": false
},
{
"query": "Which language SDKs do we ship first, and generated or handwritten?",
"should_trigger": false
},
{
"query": "Design the tool surface of our MCP server for AI agents.",
"should_trigger": false
},
{
"query": "Design our partner developer signup and sandbox provisioning journey.",
"should_trigger": false
},
{
"query": "Design the review pipeline and permission audit for third-party apps on our marketplace.",
"should_trigger": false
},
{
"query": "Write the screenshot and description requirements every listing on our marketplace has to meet.",
"should_trigger": false
},
{
"query": "Pick our marketplace launch cohort and plan the reveal.",
"should_trigger": false
},
{
"query": "Which technology partners should we integrate with first, and how deep does each one go?",
"should_trigger": false
},
{
"query": "We already ship REST and webhooks. Which integration surface belongs on the roadmap next?",
"should_trigger": false
},
{
"query": "How do we hire an API product manager for our platform team?",
"should_trigger": false
},
{
"query": "I have a platform engineer interview next week. How do I prep?",
"should_trigger": false
},
{
"query": "Optimize this slow Postgres report query, the planner keeps choosing a seq scan.",
"should_trigger": false
},
{
"query": "Review the schema design for our internal orders table.",
"should_trigger": false
},
{
"query": "Write a zero-downtime migration adding a column to our users table.",
"should_trigger": false
},
{
"query": "Our Power BI DAX measures are slow. Help me fix the model.",
"should_trigger": false
},
{
"query": "Help me write a SOQL query that pulls opportunities by owner.",
"should_trigger": false
},
{
"query": "Set up Prisma against a Postgres database for our Next.js app.",
"should_trigger": false
},
{
"query": "Our internal BigQuery bill is up 40%. Audit which pipelines are scanning the most.",
"should_trigger": false
},
{
"query": "Set up Salesforce sharing rules so the EMEA team only sees EMEA accounts.",
"should_trigger": false
},
{
"query": "We need a read replica so our internal reporting stops hammering the primary.",
"should_trigger": false
}
]
}
references/bi-connectivity-and-credentials.md›
# BI Connectivity and Credential Issuance
The detail behind steps 5 and 7: what each BI tool's certification actually requires, why the wire-protocol shortcut works, what a connection-setup page must contain, and the credential ladder with its degradation path.
## The certification tax, per tool
Supporting the major BI tools means four ongoing engineering costs:
- (a) A spec-compliant driver with correct SQLSTATEs and type coercion.
- (b) Building and signing per-tool connector packages.
- (c) Renewing signing certificates on a schedule.
- (d) Absorbing first-line connectivity support - both major vendors push that back to the data-source owner by policy.
- **Tableau** requires a Type-4 JDBC 4.0+ driver (pure Java, no native code) implementing `getDriverVersion`/`getDriverName` and returning _accurate_ SQLSTATEs - Tableau warns that returning a generic error instead of SQLSTATE 28000 for invalid credentials can break connector behavior.
- Connectors ship as signed `.taco` files; an expired signing certificate makes Tableau reject the connector outright.
- Connection behavior is tuned via TDC customization XML whose vendor/driver name fields must exactly match what the driver reports.
- Tableau limits its own support to "reasonable levels" of troubleshooting and explicitly won't create or customize a connector for a specific driver - connectivity support stays with you.
- **Power BI** certified connectors are built on the Power Query SDK. Certification requires:
- A public, code-complete product working at anticipated usage levels.
- _Documented customer demand_ (a public ideas-forum thread).
- Submission via pull request to Microsoft's connector repository.
Once certified, Microsoft distributes the connector but routes customer issues with it back to the partner developer - certification transfers distribution, not support. Microsoft also encourages self-signed custom connectors as a lighter-weight path than full certification.
- A schema change can break a customer's dashboard even when the data contract is honored, if it also breaks the connector package's customization file or a certified connector's expected column typing - the certification artifacts are a second stability surface coexisting with step 4's contract.
## The wire-protocol shortcut
Speaking an existing wire protocol sidesteps most of the above: every BI tool, `psql`, and every ORM already ships a Postgres driver, so a Postgres wire-protocol endpoint gets instant client compatibility with no `.taco` signing and no certification cycle. PostHog fronts its per-organization engines with exactly this. Mature wire-protocol server libraries exist in several ecosystems for emulating a Postgres endpoint over your own engine.
The cost is dialect commitment: you support the Postgres dialect and its type system, and nothing exotic. The inverse case is the anti-pattern - a custom query dialect (PostHog's own HogQL, by their own account "not a language that's already widely supported") restarts every BI integration from zero.
## The connection-setup page
Publish one per supported client; an undocumented connection string is a support ticket per customer. Each engine has its own URL template and driver class (`jdbc:postgresql://<host>:<port>/<db>` / `org.postgresql.Driver`, and equivalents per dialect), so document the exact template for the dialect you expose. Contents:
- URL template with every placeholder named, and the driver class or a bundled-driver download link. Default to bundling/shipping the driver; treat customer-supplied driver JARs as an escape hatch only (version pins), since driver mismatch is a recurring support burden.
- TLS stated as an explicit named requirement, never left to client-library defaults.
- The auth method for that client and where the credential comes from (see the ladder below).
- A distinct read-only endpoint name where the architecture has one - reader endpoints are a separate connection target from any writer, and the docs must make the distinction visible.
## The credential ladder
Preference order: **short-lived token > managed secret > static password**; inline credentials in a connection string never appear in any documented path. The industry is retiring static database passwords outright - Snowflake's dated milestone schedule (2025-2026) moves service users through three stages:
1. Moves human password users onto MFA.
2. Blocks new legacy service users.
3. Forces all service users onto key-pair, OAuth, PAT, or workload identity.
Design issuance on that trajectory:
- **Service accounts / automation**: key-pair auth where an RSA keypair signs a JWT that expires in about a minute - no password crosses the network, and a stolen token has a tiny replay window. Mark machine identities as service-type so the engine refuses password auth for them entirely.
- **BI tools**: external OAuth so the tool never stores a database password; desktop BI integrations use the authorization-code grant with PKCE (RFC 7636).
- **CLI / REST access where a redirect flow doesn't fit**: personal access tokens with a policy-bound maximum expiry.
- **Degradation path**: token-refresh support varies by client - a BI tool that can't store and refresh tokens falls back to a key-pair or PAT service account rather than to a static password. Verify each supported client's refresh capability before promising OAuth-only.
Scope every credential to one tenant's entitlement (the mapping table from the isolation design), give customers self-service issuance and revocation in the product, and rotate on the same dual-credential overlap discipline used for API keys - the credential sibling skill owns those mechanics in detail.
references/rls-failure-mode-catalog.md›
# Row-Level-Security Failure-Mode Catalog
The concrete implementation layer under step 3's "enforce isolation in the engine". Postgres-family mechanics first, then the cloud-warehouse equivalents, then the cross-engine anti-pattern checklist. Work through the failure modes _before_ writing policies - each one is cheap to prevent and expensive to discover in production.
## The core principle
RLS is the last line of defense, not the only one. Policies decide which _rows_ a role may see after the engine has already allowed the SQL operation - RLS does not itself grant SELECT/INSERT/UPDATE/DELETE, so matching `GRANT`s are still required. Decide the operation surface and column exposure per table first; RLS answers "which rows", privileges and column grants answer "which operations and fields".
For an _external_ SQL handle specifically, engine-level enforcement is mandatory rather than best-practice: the tenant holds a live query connection, so there is no application layer left to intercept a query that forgot its filter.
## Postgres RLS failure modes, ranked by severity
1. **Infinite policy recursion (critical - crashes the server).** A helper function called from a policy on table A queries table B, whose own policy calls back into a function touching table A; the recursion exhausts memory. Fix: any helper function invoked from inside a policy must be `SECURITY DEFINER` with `SET search_path = pg_catalog, public, pg_temp` pinned, so it bypasses RLS on the tables it reads and breaks the cycle.
2. **Missing `WITH CHECK`.** `USING` filters reads; `WITH CHECK` validates writes. A policy with only `USING` lets a tenant insert or update rows it cannot read back - for example rows attributed to a _different_ tenant. Every write-path policy needs both clauses.
3. **Permissive policies compound with OR.** Multiple policies on one table combine with OR, so a single `USING (true)` policy silently defeats every stricter policy beside it. Audit all of a table's policies together, never one at a time.
4. **View bypass.** Views run with their creator's privileges by default, so an owner-privileged view over an RLS table leaks every row regardless of caller. Fix: `WITH (security_invoker = true)` (PostgreSQL 15+) so the view respects the caller's own RLS.
Two configuration traps that sit beside the four:
- **Enable is not enough - FORCE it.** Without `ALTER TABLE ... FORCE ROW LEVEL SECURITY`, the table's owner role bypasses RLS entirely, defeating isolation for anything that runs elevated (admin tooling, migrations, background jobs) against tenant tables.
- **Inject tenant identity server-side.** Extract the tenant ID from a verified session token and set it in the database session context before any query runs on that connection; the policy reads the session-context value. A policy that reads a client-supplied parameter is tenant-ID tampering waiting to happen.
## Performance rules
A slow policy gets "temporarily" disabled under load pressure - which is how a correctness control becomes an outage story. Keep policies fast by design:
- Index every column a policy predicate references (`tenant_id` first of all).
- Wrap per-row function calls in a subquery: `USING (tenant_id = (SELECT auth_tenant_id()))` evaluates once per query; the bare call form evaluates once per row and is measurably slower at scale.
- Denormalize the tenant identifier onto child tables rather than resolving it through a join at query time.
- Use `SECURITY DEFINER` helper functions for cross-table membership checks, avoiding RLS-on-RLS chains - for speed, not only for recursion safety.
Protected fields (`tenant_id`, `owner_id`, role, billing status) need a `BEFORE UPDATE` trigger comparing `OLD`/`NEW` - RLS predicates cannot see the previous row value, so RLS alone cannot stop a tenant rewriting its own `tenant_id`. And expose narrow views or functions projecting only safe columns instead of widening a base table's grants to serve a "public" read case.
## Cloud-warehouse equivalents
A reader coming from Postgres will assume `USING`/`WITH CHECK` policies are the only primitive. The warehouses solve the same problem with a different one - and it has its own trap:
- **Snowflake**: a secure view joins the private base table to a private entitlement table on `sd.access_id = sa.access_id AND sa.snowflake_account = current_account()`; only the secure view enters the share. **`CURRENT_ROLE()` and `CURRENT_USER()` return `NULL` inside a secure view shared to another account** - `CURRENT_ACCOUNT()` is the only safe identity primitive across a share, and the same NULL trap applies to row-access policies (`IS_DATABASE_ROLE_IN_SESSION()` is the row-access-policy equivalent). Snowflake's `SIMULATED_DATA_SHARING_CONSUMER` session parameter exists precisely to test a share's isolation as if querying from the consumer side before granting access - use it as the probe mechanism for the isolation gate.
- **Databricks Delta Sharing**: a dynamic view filtered on `current_recipient('property')`, with properties set per recipient on the share. Recipient-property _partition filtering_ is documented as a best-effort optimization, not an access-control feature - hard isolation comes only from the view predicate.
- The lesson is identical across engines: Postgres RLS says "never rely on the WHERE clause alone", and the warehouse version is "never rely on partition pruning or session-role functions across a share".
## Multi-tenant anti-pattern checklist
Adapted from OWASP's multi-tenant guidance; each line is an audit finding for a customer-facing surface:
- Sequential or guessable tenant/resource IDs (enables cross-tenant enumeration).
- Any query path that skips the tenant filter - including "internal" or admin callers - without an explicit, audited override path.
- Tenant data stored without a `tenant_id` column, even on tables that feel single-tenant today.
- Credentials shared across tenants.
- "Internal service" treated as exempt from tenant validation.
## Known scaling trap: schema-per-tenant
Schema-per-tenant looks like easy isolation and stands up quickly, but connection pooling and DDL migrations commonly break down past a few hundred tenants, forcing a disruptive mid-flight re-architecture. Treat it as a failure mode to warn about, not a default to recommend at any real scale - the architecture menu in SKILL.md step 2 covers the rungs that do scale.
references/vendor-case-studies.md›
# Vendor Case Studies and Pricing Precedents
Named implementations, one candid post-mortem, and the shipped pricing shapes. Figures may be dated; verify current pricing and product status before quoting any of them to a customer. Vendor-vs-vendor comparison claims are marketing, not neutral fact - weight the mechanics, not the adjectives.
## Architecture trade-off summary
| Dimension | Zero-copy sharing | Replicated copy / read endpoint | Per-tenant compute isolation |
| ------------------- | -------------------------------------- | ------------------------------- | ---------------------------------- |
| Storage cost | Lowest (no copies) | Highest (N copies) | Shared storage, dedicated compute |
| Query cost | Per-query scan (+ cross-region egress) | Prepaid at refresh | Per-tenant metered compute |
| Freshness | Live | Stale to last refresh | Live |
| Noisy-neighbor risk | Depends on consumer compute | Isolated by copy | Eliminated by design |
| Governance | Centralized at source | Duplicated PII surface | Centralized data, isolated compute |
The tipping rule between the first two columns is scan frequency (Alex Merced's heuristic, one practitioner's rule of thumb: ~20 scans/day on a ~200 GB table makes live re-scanning cost more per month than a replica costs per year). Even Databricks' own docs concede replication is sometimes the cost-efficient choice.
## Platform mechanics
- **Snowflake Secure Data Sharing**: shares are named objects wrapping tables, secure views, and secure UDFs; sharing happens through the metadata layer, so no data is copied and shared data occupies no consumer storage.
- Full-account consumers pay their own compute.
- Non-Snowflake consumers get a **reader account** - and reader-account warehouses can consume unlimited credits billed to the _provider_ unless a resource monitor caps them. That default is the sharpest cost exposure of the whole pattern.
- **Databricks Delta Sharing**: an open REST protocol, not proprietary - Databricks-to-Databricks mode plus open sharing for non-Databricks clients (Power BI, Tableau, Spark, pandas) via pre-signed URLs. Governance centralizes in Unity Catalog; auth supports bearer tokens or OIDC federation (no shared secret at all). Provider pays cross-region egress on consumer reads, which scales with consumer query volume; backing the share with an egress-free object store eliminates that line item.
- **BigQuery sharing (Analytics Hub)**: publishers create exchanges holding listings; subscribers get an opaque, read-only linked dataset inside their own project and VPC perimeter. A restricted-egress mode blocks subscribers from exporting shared data out of that dataset entirely. Providers meter usage through the `SCHEMATA_LINK` `INFORMATION_SCHEMA` view - a concrete usage-metering hook.
- **MotherDuck Hypertenancy** (the hosted per-tenant-compute reference): every user, service account, or agent gets a dedicated single-node engine instance with its own CPU/memory/spill that spins up on first query and scales to zero - per-tenant isolation and per-user billing by construction, with read-scaling replica pools for read-heavy tenants. Named production adopter: Together AI chose it as an analytics serving layer explicitly because agent-driven queries on a compute-heavy shared warehouse "would create a serious cost problem" (their Director of Data Engineering, quoted by MotherDuck - vendor-published, weight accordingly).
- **Iceberg REST Catalog** (the vendor-neutral layer under several of the above): an OpenAPI-specified metadata API any compliant client can read; supports credential vending and OAuth2. Caveat: the spec is intentionally silent on performance, so two compliant catalogs can differ by orders of magnitude in response time.
Named adopter of consumer-pays sharing: **HubSpot** ships a native Snowflake Data Share gated to its top data tier, read-only, with the customer explicitly responsible for all Snowflake compute costs incurred reading it.
## PostHog - the strongest documented failure case
PostHog's 2026 engineering post-mortem on rebuilding its data warehouse is first-party, dated, and unusually candid; treat it as the primary failure-mode citation.
- On exposing the shared multi-tenant ClickHouse cluster via SQL: "The prospect of an unbounded number of queries hitting our very not elastic CH cluster is basically untenable and would eventually cause problems across our entire application."
- On isolation: "We simply could not expose direct connections to the Clickhouse cluster without deepening the challenges of multitenancy. On top of that, our query language HogQL is not a language that's already widely supported." Two distinct lessons in one quote: shared-cluster blast radius, and the custom-dialect connectivity tax.
- A third, subtler failure mode - engine drift breaking untested customer query shapes: "We've seen ClickHouse change query results between releases, or based on which settings were enabled. Our test suite catches this for our own queries, but we can't test every shape of customer data and query a warehouse needs to handle." A vendor's regression suite covers its own known queries, never the combinatorial space of arbitrary customer SQL.
- Their fix targets all three at once: single-tenant embedded engine instances per organization, fronted by a Postgres wire-protocol endpoint so standard BI tools and `psql` connect with no custom driver.
- A mundane ongoing cost from their handbook, independent of architecture: external SQL connections fail without specific client drivers installed - recurring first-line support, forever.
## Amplitude - a full product lifecycle as a maintenance-cost signal
Amplitude shipped direct customer SQL access as per-customer dedicated Redshift clusters ("Amplitude Query"), then rebuilt as "Warehouse-native Amplitude" translating chart analyses into SQL running inside the _customer's own_ warehouse - with query optimization at scale as the stated engineering challenge. Warehouse-native is now marked a legacy feature not offered to new customers in Amplitude's own docs.
Read the arc as evidence that a warehouse-native SQL surface costs more to _maintain_ than to build - but don't overstate a specific cause: the deprecation reason isn't independently documented beyond the legacy marking. Separately, Amplitude's additive-only event schema (tables and columns only ever added as new event types arrive) is the shipped existence proof for step 4's contract.
## Pricing precedents
| Vendor / product | Shape | Mechanics (point-in-time) |
| --------------------------------------- | --------------------------------- | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ |
| Stripe Sigma | Consumption / volume-tiered | Read-only SQL environment inside the Stripe Dashboard, tiered on monthly charge volume (roughly $10-15/mo up to 250 charges, ~$450/mo up to 25,000, per-charge overage above); queries themselves unlimited and unmetered; 30-day trial; annual-only above the entry tier. Repriced 2025-09-30 from a per-charge + flat infrastructure fee model - a live example of a vendor changing its SQL-access pricing metric after shipping. |
| Heap Connect | Flat tier gate | Direct SQL warehouse access gated behind higher plan tiers; free once the tier is bought - the lever is the gate, not usage. |
| Mixpanel Data Pipelines | Flat add-on | Sold as an add-on package on top of Growth/Enterprise plans. |
| Amplitude Query | Flat add-on (dedicated infra) | Historically bundled SQL access via a dedicated per-customer Redshift cluster - the true "external driver handle" precedent, later superseded (see lifecycle above). |
| Snowflake reader accounts / Marketplace | Provider-pays-compute passthrough | Provider pays reader-account compute and invoices separately; paid marketplace listings support per-query pricing and per-active-month pricing, with the payout rail handled by the marketplace. |
| HubSpot Data Share | Consumer-pays, tier-gated | Snowflake share at the top data tier; customer pays all Snowflake compute for reads. |
How to weight them: Stripe Sigma is a query-inside-the-vendor's-UI feature, not an external driver handle - the strongest evidence that customers _pay_ for SQL access, not of what external JDBC access looks like. Heap Connect and Amplitude Query hand out real connection strings - the strongest evidence for the external-driver shape. All figures came from vendor pricing pages and docs.
## Why this is a category - analyst framing
a16z's data-infrastructure architecture work describes application developers building on the clean, joined data already in the warehouse/lakehouse, with traditional enterprise systems being rebuilt "warehouse-native" and OLAP integration becoming a critical component of application development. Use it as the "why now" narrative; it is analyst framing, not evidence for any implementation detail. Snowflake's own marketing pitches the same monetization angle from the vendor side - embedded live analytics as a new value stream - which is a vendor's sales claim, cited here only to show the platform vendors are actively courting this pattern.
SKILL.md›
---
name: sql-jdbc-access-design
description: Design customer-facing SQL access to a product's data - a JDBC/ODBC endpoint, warehouse share, or hosted query surface. Covers the architecture gate by scan frequency (zero-copy share, replicated copy, per-tenant compute isolation), engine-level tenant isolation with secure views and row-level security, an additive-only schema-stability contract enforced in CI, BI-tool connectivity and driver certification, query governance and cost caps, short-lived credential issuance, and the pricing shape. Use whenever the user mentions JDBC, ODBC, SQL access for customers, connecting Tableau or Power BI to product data, or pricing a SQL add-on - even if they never say "SQL access". Do NOT use for bulk file delivery - use samber/developer-platform-skills@bulk-data-sharing-design instead.
license: MIT
metadata:
author: Samuel Berthe
version: "1.0.1"
---
# SQL / JDBC Access Design
You are a data-platform product designer. Design how a SaaS product exposes its own hosted data to its customers through live SQL - a warehouse share, a read endpoint, or a JDBC/ODBC handle a BI tool connects to - so the surface is isolated, stable, governed, and priced before the first customer connects.
The stakes are asymmetric: an API bug returns a wrong response, but an isolation bug on a live SQL handle returns another customer's rows, and a runaway customer query lands on your infrastructure bill.
The demand side, in a16z's framing: applications are increasingly rebuilt warehouse-native, so governed SQL access is becoming a product category, not a niche feature - analyst positioning for the "why now", not evidence for any implementation choice below.
**Memory (advised):** When memory lives in a file, consider using `developer-platform-context.md`; if a different memory system is in use, rely on that instead. The file is an advisory reference, not a mandatory requirement. Separate task info in different sections. Remove finished tasks. Add a date to a task; no date for general project context. Some interview responses may differ between 2 tasks.
## Clarifying questions
Ask these before designing anything; each answer changes a later step. Batch them - this is a tactical design task, not a strategy interview.
1. Which problem is this actually? These get conflated under "data sharing", but only one is this skill's full scope:
- (a) sharing data with partner _organizations_ - reuses the sharing mechanics here.
- (b) isolating compute per tenant _inside_ your own product's analytics - an internal architecture concern that this skill's isolation menu touches but pricing and BI sections don't.
- (c) giving _your customers_ a queryable SQL endpoint to their own data - this skill's full scope.
2. Where does the data live today: a cloud warehouse (Snowflake, Databricks, BigQuery), an OLTP database, a self-managed OLAP cluster? (decides which architecture rungs are nearly free)
3. Scan-frequency evidence: will a typical customer run ad-hoc queries a few times a week, or park a dashboard on it that re-scans many times a day? How large are the tables scanned? (this gates the architecture - see step 2)
4. Tenancy: how many customer accounts, what size spread, and is the store pooled or already siloed per tenant?
5. Which SQL consumers do customers actually demand: Tableau/Power BI/Looker, `psql` and notebooks, or an embedded app or agent issuing queries? (drives the BI-connectivity budget)
6. Schema exposure: raw operational tables, or a curated mart layer? (raw exposure makes every internal refactor a customer-breaking change - see step 4)
7. Pricing intent: free feature, gated to a higher tier, or a metered add-on - and who should pay the query compute? (see step 8; also ask the standard re-ranking trio - ship date, one-off vs compounding, effort ceiling)
If your harness has persistent memory, store the design's settled decisions (architecture rung with its promotion condition, isolation mechanism and identity function, schema-stability contract and what counts as breaking, credential ladder position, governance caps, pricing shape) so later runs - a new consumer type, a schema change, a repricing - start from the design instead of re-deriving it.
## Consumer type and scan frequency
Two splits do the work:
- **Consumer type:**
- A _BI analyst_ behind Tableau or Power BI needs a certified driver, stable column names, and predictable types - they never see your docs, only their tool's connection dialog.
- A _data engineer_ with `psql`, dbt, or a notebook tolerates rough edges but pushes the largest queries.
- An _embedded app or agent_ issues high-frequency machine-generated queries that no human reviews - the strongest case for hard cost caps, since nobody notices a pathological query pattern until the bill.
- **Scan frequency.** The same table read twice a week and the same table under a forty-viewer morning dashboard want different architectures (step 2). Frequency, not company size, is the economic variable.
Design for the most demanding consumer type present, and gate the architecture on measured or honestly-estimated scan frequency - never on the org chart of who bought the product.
## Workflow
1. Confirm the problem and inventory the consumers.
2. Gate the architecture on scan economics.
3. Enforce tenant isolation in the engine.
4. Publish a schema-stability contract.
5. Design BI connectivity and driver distribution.
6. Configure query governance and cost caps.
7. Issue credentials.
8. Choose the pricing shape.
Each step has a section below, in order. Steps 3 and 6 must both pass their gates before any customer gets a handle; everything else can stage.
## 1. Confirm the problem and inventory the consumers
- Settle question 1 explicitly and write the answer down. "Data sharing", "multi-tenant isolation", and "customer SQL access" name overlapping but distinct architectures, and a design that answers the wrong one ships the wrong isolation primitive.
- List the concrete consumers (question 5) with their expected query shape: dashboard refresh, ad-hoc exploration, scheduled extract, agent traffic. This list is the input to every ranked menu below.
- State the boundary for the reader: this surface serves _live queries_. If the consumer actually wants periodic files or their own warehouse fed, hand off to the bulk-delivery sibling skill (see References) before designing anything here.
## 2. Gate the architecture on scan economics
Three architectures, ranked. Scan frequency and where the data already lives decide which one wins - not taste.
- efficiency: `zero-copy share > replicated copy or read endpoint > per-tenant compute isolation`
- effort: `per-tenant compute isolation > replicated copy or read endpoint > zero-copy share`
- value: `per-tenant compute isolation > zero-copy share > replicated copy or read endpoint`
- **Default rung: zero-copy warehouse sharing** - when the data already lives in a shared cloud warehouse and customers scan infrequently. The share is a metadata grant, not a copy: lowest build cost, always-fresh data, governance stays centralized at the source. The cost model is the catch: live access is paid per query scan (plus cross-region egress), so the economics hold only while scans stay infrequent.
- **Step to a replicated copy or dedicated read endpoint** when scan frequency inverts the arithmetic.
- One lakehouse practitioner's heuristic (Alex Merced - treat it as one practitioner's rule of thumb, not a benchmark): a copy is paid once per refresh, live access once per query.
- At roughly 20 scans/day on a ~200 GB table, the egress of live re-scanning costs more per month than a replicated copy costs per year.
- A dashboard forty people load every morning is past this line. Two analysts querying twice a day are not.
- The price of this rung: staleness to the last refresh, and a second copy of the data (including its PII) to govern.
- Never point the replica at the live operational schema directly - see step 4.
- **Promote to per-tenant compute isolation** - each tenant gets its own query engine (a per-tenant instance over shared storage, fronted by one endpoint). This is the starved option:
- Highest value: noisy neighbors eliminated by construction, a runaway query's blast radius capped at one tenant, clean per-tenant cost attribution.
- Highest effort: an engine fleet to build and operate, so it loses every efficiency round.
- Two conditions promote it anyway: arbitrary customer-authored SQL at dashboard-or-agent frequency across many tenants, or a shared cluster whose worst-case query already threatens the product itself.
- PostHog hit both, judged unbounded customer queries against its shared cluster "basically untenable", and rebuilt on per-organization engines behind a Postgres wire-protocol endpoint (see [references/vendor-case-studies.md](references/vendor-case-studies.md)).
This ranking is a default, not a law. Re-rank against the interview:
- Already on Snowflake/Databricks/BigQuery makes zero-copy nearly free (which is why it's the default at all).
- An OLTP-only stack with no warehouse deletes the zero-copy rung entirely rather than demoting it.
- A hard ship date promotes whichever rung your platform gives you today.
- Agent consumers or a compounding-asset mandate promote the isolation rung despite its effort.
## 3. Enforce tenant isolation in the engine
Isolation must live in the query engine, never in application code. An app-layer `WHERE tenant_id = ?` filter is a named failure class for this surface: with an external SQL handle there is no application layer left to intercept the query, so one missed filter - ever, anywhere - leaks another customer's rows. Engine-level policies make a query that omits the filter return nothing instead.
- On a warehouse share, expose only secure/dynamic views that join a private base table to a private entitlement table filtered on the _caller's identity as the engine sees it_ - Snowflake's `current_account()`, Databricks Delta Sharing's `current_recipient()`. The base and mapping tables never enter the share.
- **The NULL-context trap**: `CURRENT_ROLE()` and `CURRENT_USER()` return `NULL` inside a secure view once it crosses an account-to-account share. A policy written against them silently matches nothing - or worse, everything, depending on the predicate's shape. Only the share-aware identity functions are safe across a share boundary.
- Treat recipient-property _partition filtering_ as a performance optimization, never as access control - Delta Sharing documents it as best-effort. Hard isolation comes from the dynamic-view predicate alone.
- On Postgres-family engines, use row-level security with `FORCE ROW LEVEL SECURITY`, and inject tenant identity into the session context server-side from a verified token - never from client-supplied input. RLS has four well-documented failure modes (policy recursion, missing `WITH CHECK`, permissive-policy OR-composition, owner-privileged view bypass) plus performance rules; work through [references/rls-failure-mode-catalog.md](references/rls-failure-mode-catalog.md) before writing any policy.
- Verify isolation from the consumer's side, not the provider's: run probe queries as another tenant (a second test account, or the warehouse's simulated-consumer session mode) and require zero foreign rows. This is a gate (see Measurement), re-run on every policy change.
## 4. Publish a schema-stability contract
The moment a customer's dashboard queries your tables, your schema is an API: columns are fields, tables are endpoints, their dashboards are clients. The asymmetry versus API versioning is that schema changes often happen _accidentally_ - a source-system update, a readability rename - and propagate downstream with no deprecation header to warn anyone.
- Never expose the live operational schema. Serve a curated mart layer replicated from the system of record, so internal schema churn stops at the replication boundary instead of landing in customer dashboards.
- Adopt the additive-only rule: add columns and tables freely; never rename, retype, or drop in place. Evolve via expand/contract:
1. Add the new column.
2. Deprecate the old one in the docs.
3. Migrate consumers.
4. Remove only when nothing depends on it.
Amplitude's event schema is a shipped existence proof: it only ever adds tables and columns as new event types arrive, staying backward-compatible by construction.
- Enforce the contract in CI, not by convention. A contract that only lives in a doc is a hope:
- dbt model contracts (1.5+) fail the run on a breaking column change.
- The open-source `datacontract` CLI validates a contract against a PR before merge.
- Write the contract in four parts, because a BI dashboard, a dbt job, and an embedded app each depend on a different subset:
- Schema: columns, types, nullability.
- Quality: completeness thresholds.
- Freshness: refresh SLA.
- Availability: uptime/latency.
- When a breaking change is genuinely unavoidable, it exits through deprecation machinery with notice periods - the versioning sibling skill governs that; this contract defines what counts as breaking.
## 5. Design BI connectivity and driver distribution
BI-tool support is a certification tax, not a one-time integration - budget it as ongoing engineering, and let question 5 decide how much of it you buy.
- The cheapest rung, when your engine allows it: speak an existing wire protocol. A Postgres wire-protocol endpoint gets every BI tool, `psql`, and every ORM for free, because they all ship a Postgres driver - this is exactly why PostHog fronted its per-tenant engines with one. It costs you dialect fidelity: you support the Postgres dialect and nothing exotic.
- A custom dialect is the anti-pattern this rung exists to avoid: PostHog names its own HogQL as a reason direct exposure was a non-starter - "not a language that's already widely supported" means every BI tool integration starts from zero.
- Shipping your own driver means:
- A spec-compliant Type-4 JDBC driver with _accurate_ SQLSTATEs (Tableau warns that a generic error instead of SQLSTATE 28000 for bad credentials breaks connector behavior).
- Signed per-tool connector packages with certificates that expire and must be renewed.
- Per-tool certification programs whose policies route first-line connectivity support back to you, not to the BI vendor.
Details and the certification requirements per tool: [references/bi-connectivity-and-credentials.md](references/bi-connectivity-and-credentials.md).
- Publish a connection-setup page per supported client. An undocumented connection string is a support ticket per customer:
- URL template.
- Driver class or download.
- TLS requirement stated explicitly rather than left to client defaults.
- Auth method.
## 6. Configure query governance and cost caps
None of the warehouse cost controls are on by default; every one must be configured before GA, per external warehouse or engine. This differs from API rate limiting in enforcement primitive: you are capping compute seconds and spend on a long-running engine, not counting requests at a gateway. Design it here, and keep request-style limits (connection counts, queries/minute at the endpoint) consistent with the API sibling skill's policy.
- Per-query circuit breaker: a statement timeout so one runaway query dies instead of running all night, plus a queued-statement timeout so queries don't wait indefinitely for a slot.
- Concurrency cap per tenant or workload, so one customer's parallel dashboard refresh can't occupy a shared engine.
- A spend cap distinct from the timeout: a resource monitor (or your engine's equivalent budget alarm) that suspends the warehouse when its credit budget is hit. The timeout bounds one query; the monitor bounds the month.
- **The reader-account trap**: when you provision consumer accounts whose compute bills to _you_ (Snowflake reader accounts, or any provider-pays posture from step 8), their warehouses can consume unlimited credits charged to your account by default. A resource monitor on every such account is the single most important cost-control step of this surface, not optional hardening.
- The escalation signal: a tenant that regularly queues others or trips monitors moves to isolated or larger compute; never raise the shared cap for everyone to accommodate one tenant.
## 7. Issue credentials
The industry is retiring static database passwords - Snowflake enforces this through a staged deprecation schedule that moves all service users onto key-pair, OAuth, PAT, or workload identity - so design credential issuance on that trajectory rather than grandfathering passwords in.
Preference order: short-lived token > managed secret > static password; inline credentials in a connection string are the anti-pattern no documented path should show.
- Service accounts and automation: key-pair auth signing short-lived JWTs, so no password ever crosses the network; mark machine identities as service-type to close the password fallback.
- BI tools: OAuth (authorization-code with PKCE for desktop tools) so the BI tool never stores a database password. Caveat: token-refresh support varies by client - a BI tool that can't hold a refreshable token degrades to a key-pair or PAT service account, so the ordering must degrade gracefully rather than assume OAuth everywhere.
- Scope every credential to the tenant's own data by construction (the entitlement mapping from step 3), enforce TLS as a stated requirement, and give customers self-service revocation. Key formats, rotation UX, and secret hygiene follow the credential sibling skill; specifics for this surface live in [references/bi-connectivity-and-credentials.md](references/bi-connectivity-and-credentials.md).
## 8. Choose the pricing shape
Package SQL access as a paid tier or add-on, never a free feature. The shipped precedents (Stripe Sigma, Heap Connect, Mixpanel Data Pipelines) show customers pay for exactly this capability, and a price is itself a governance control on a surface that is expensive to over-serve. Three shapes, ranked:
- efficiency: `flat add-on or tier gate > consumption / volume-tiered > provider-pays-compute passthrough`
- effort: `consumption / volume-tiered > provider-pays-compute passthrough > flat add-on or tier gate`
- value: `consumption / volume-tiered > provider-pays-compute passthrough > flat add-on or tier gate`
- **Default rung: the flat add-on or tier gate** (Heap Connect, Mixpanel's shape) - SQL access unlocks at a higher plan or as a fixed-price add-on. Near-zero billing engineering; the gate itself throttles casual abuse. Its risk is the unbounded heavy user whose compute costs exceed the flat price - acceptable only with step 6's caps in place.
- **Promote to consumption / volume-tiered** when your own cost exposure is consumption-based or customer data volumes span orders of magnitude. This is the starved option:
- Highest value: revenue tracks cost, so no customer is quietly unprofitable.
- Highest effort: metering, tier tables, overage logic, so it loses the efficiency round until one of those two conditions holds.
- Meter on a business unit the buyer already understands (Stripe Sigma tiers on monthly charge volume with unmetered queries), never on warehouse credits a non-technical buyer can't predict.
- Sigma also shows the metric is revisable: it changed its own pricing model after shipping.
- **Provider-pays-compute passthrough** is less a free choice than the posture that arrives with reader accounts and marketplace listings: you pay the compute, then invoice cost plus margin. Take it only with a resource monitor on every account (step 6) - and note the consumer-pays alternative exists at the same rung: HubSpot's Snowflake share bills compute to the customer's own account, gated to its top tier.
This ranking is a default, not a law - re-rank against question 7 and the interview trio:
- A compounding-asset mandate promotes the consumption rung early, since retrofitting a meter under a shipped flat price is a customer-facing repricing.
- A hard ship date holds the flat gate, which is the only rung that needs no billing engineering.
Pricing precedents with their point-in-time figures: [references/vendor-case-studies.md](references/vendor-case-studies.md) - verify current pricing before quoting any of them to a customer.
## Failure modes
Anti-pattern checklist - each is a direct audit finding:
- Tenant isolation implemented as an application-layer `WHERE` clause instead of engine policy.
- A shared-view policy written against `CURRENT_ROLE()`/`CURRENT_USER()` (NULL across shares) instead of the share-aware identity functions.
- Partition filtering trusted as access control.
- Customer SQL admitted onto the shared production cluster - the PostHog "untenable" scenario.
- The live operational schema exposed directly, so internal refactors break customer dashboards.
- A rename or retype shipped in place instead of expand/contract, with no CI contract to catch it.
- A reader account (or any provider-pays compute) with no resource monitor.
- A custom query dialect where a standard wire protocol was available.
- Schema-per-tenant chosen for isolation, collapsing under connection pooling and DDL migrations past a few hundred tenants.
- Isolation verified only from the provider's side, never probed as a consumer.
- RLS-specific traps (recursion, missing `WITH CHECK`, OR-composition, view bypass): the full catalog with fixes is in [references/rls-failure-mode-catalog.md](references/rls-failure-mode-catalog.md).
## Measurement
- Isolation gate: probe queries executed as another tenant (simulated-consumer session or a second test account) return zero foreign rows, across every shared view and policy - binary, before GA and after every policy or schema change.
- Schema gate: zero breaking changes reach the customer-facing schema past the CI contract check; a breaking change that ships is a gate failure even if no customer noticed.
- Cost gate: every external compute handle has a statement timeout, a concurrency cap, and a spend cap before the first customer connects - audited as a checklist, pass/fail per handle.
- Trends to baseline from the first month, not thresholds: connectivity support tickets per new connection, per-tenant query cost against price paid, freshness-SLA adherence. A customer-facing data surface carries a categorically higher reliability bar than an internal dashboard - a failure here is a support ticket and a churn risk, so trend regressions get triaged like product incidents.
Iterate until all three gates pass; they are gates, not aspirations. The trends steer pricing and architecture revisits afterwards.
## Invocation examples
- "Our customers keep asking to plug Tableau into their data in our product - design the SQL access surface, we're on Snowflake."
- "We want to sell direct SQL access as an Enterprise add-on. What architecture and pricing shape, given most customers would park dashboards on it?"
- "Review our plan to expose read replicas of our production Postgres to customers - what breaks?"
## References
- `samber/developer-platform-skills@etl-connector-strategy` - being a source connector on other people's ETL platforms; this skill is the customer querying you directly.
- `samber/developer-platform-skills@api-integration-surface-strategy` - the umbrella decision of whether SQL access should be offered at all, among the other integration surfaces.
- `samber/developer-platform-skills@api-auth-key-management` - key formats, rotation UX, and secret hygiene for API credentials; step 7 applies the same discipline to database handles.
- `samber/developer-platform-skills@api-versioning-policy` - deprecation machinery and notice periods; step 4's contract defines what counts as breaking for the schema, that skill governs how a breaking change is announced and retired.
- `samber/developer-platform-skills@api-rate-limit-policy` - request-counting limits at the API gateway; step 6 caps compute and spend on a query engine instead, and the two published policies must not contradict each other.