Ask HN: Handle customer DB access in SaaS

5 points by cyptus 2 years ago | 11 comments
when providing SaaS how do you handle (big) customers that requests access to their data for BI applications?

most BI solutions like power bi struggles hard to obtain data from HTTP-APIs as they are paged, aggregated in other ways, authenticated with OAuth and so on. integrations are very easy if access to a sql layer would be given instead. But of course direct database access is also a struggle if using a multi tenant environment which would need at least some kind of row-level-security and a own read-replica. Also the customer needs to know every detail of the database schema which makes internal changes harder again and is an unnecessary detail.

Is there any other way then filling a second database event based with the correct „business-based“ schema (like a cache) and provide direct access to this? Is there any option to provide an „sql“ api to the application architecture?

  • mooreds 2 years ago
    In rough order of effort (and inverse order of intrusiveness to your environment):

    You could use a view (or views) and a read-only user. The view could limit access to a single tenant and also abstract away changes to the schema if needed.

    If you want more separation, you could use a read replica, again with a view.

    You could also build an of ETL workflow to extract the tenants data, transform it if needed, and push it somewhere else.

    Finally, make sure they pay for this. I'd scope out what they need and quote a price before doing anything except exploratory work.

    We have an internal feature (we've yet to build) to offer a nightly database export of customers' data. We'll just send it to an s3 bucket they provide and let them take the data how they want. But this is a feature that will be restricted to our enterprise plans, most likely.

    • cyptus 2 years ago
      thank you, for sure this will be an enterprise feature
    • pkrotich 2 years ago
      I would not add such access to your turnkey SAAS instrastructure. Perhaps you can offer a read-replica as an add-on but the headache will be massive when it comes to CI/CD with little ROI for most of your customers.

      This is an opportunity, I think, for you to sell an Enterprise Appliance version of your application at 1000X the price. Such whales (customers willing to pay for custom BI) can host it internally, in a private-cloud or as a manged private-cluster (copy). If you go add-on replica route - then perhaps replicating data to Analytic Database with nosql type storage format will be ideal in solving schema problems and decoupling the access.

      • cyptus 2 years ago
        thank you! these customers are not ready for an self-hosted app with this pricetag, i am searching for kind of an way in between these solutions. i think we are on the right way now.
      • bombcar 2 years ago
        If they're that big can you provision their "own" copy of your infrastructure or at least the database?

        And then foist handling database changes off on them.

        • cyptus 2 years ago
          Not that big as the costs for an own database or complete copy would suffice, including administrative expense
          • bombcar 2 years ago
            Offer it as a feature only available if they pay $(3-5x estimated costs).

            If they bite, wahooooo!

            • cyptus 2 years ago
              which makes the cheaper approach by filling a second database by events and providing access even better, just want to check if i didn’t miss any simple option as for sure these kind of requests are quite common in b2b
              • Kepouick 2 years ago
                Indeed this looks a lot like a custom request
          • _448 2 years ago
            CouchDB would be ideal for this use case. Export your data to CouchDB on a per-tennant basis and use inbuilt access control.