Rita's got two people, six pieces of software, and one question that keeps coming back. She doesn't want to leave Google Workspace. She wants a business layer that actually talks to it.
And this is the part where most people either give up and go full no-code, or they build something so clever it collapses in eight months.
Right. Daniel's framing this as the integration episode. How does a PostgreSQL-backed app on Cloud Run actually connect to Gmail, Drive, Calendar, Docs, Sheets and Slides without duplicating everything and without throwing away what already works?
The whole thing hinges on one principle, and it's worth saying before we touch a single API.
PostgreSQL owns the business relationships. Google stays authoritative for the documents, the messages and the events it hosts.
That's the line. A single system of record does not mean copying every piece of information into one database.
And everything from folder provisioning to the Slides merge to the calendar reminders falls out of holding that line or breaking it.
So do we start with the architecture?
We have to. Everything else is downstream of the auth model, and the auth model has one constraint that basically decides the whole Drive design.
Let's frame the actual problem first, because it's a mismatch of shapes. Rita's business data is relational. Clients, projects, sites, milestones, invoices. That's tables with foreign keys and it wants to live in Postgres.
And her documents, her messages and her appointments live in Google's apps, where her colleague already works and where the designers already know the interface.
Neither side should be forced to become the other. You don't want to build a document editor, and you don't want to cram a client project hierarchy into a Drive folder tree as the source of truth.
So the backend holds the relationships and reaches into Google for the content.
And the way it reaches in comes down to two auth models. The first is a service account, which Google describes as an account that belongs to your application instead of to an individual end user.
Meaning it's the app's own identity.
For server-to-server calls where no human is involved. That's sometimes called two-legged OAuth. The backend wants to create a folder, it authenticates as itself, done.
And the second model?
Domain-wide delegation. If the app needs to act on a user's actual data, a Workspace super-admin grants the service account the right to impersonate specific users. You set it up in the Admin console, under security, then API controls, then manage domain-wide delegation. You enter the service account's client ID and a comma-delimited list of scopes.
How long does that take to propagate?
Google's own wording is that it usually takes a few minutes, but in some cases it might take up to twenty-four hours.
That's a fun window to debug in.
It's the classic. You change a permission, nothing happens, you assume you did it wrong, you change three other things, and then the first one kicks in and you have no idea which fix worked.
So if I'm Rita, why not just hand the app the super-admin's own credentials and skip the delegation ceremony?
Because delegation is a much narrower thing than it sounds. This is the misconception worth killing early. Domain-wide delegation does not grant the service account blanket access to all user data.
It sounds like it does.
The wording from Google is that you're not granting direct access to all user data, you're authorizing the service account to impersonate specific users when making API calls. So for every request, the application has to specify which user it's acting as, and it's bounded by two things at once. That user's own permissions, and the scopes you authorized.
So it can only do what Rita could do, and only within the scopes she signed off on.
Which is the right shape. If Rita can't see a folder, neither can the app acting as her.
And the alternative, just signing JWTs by hand?
Google actively pushes you away from it. The mechanics of server-to-server auth require you to create and cryptographically sign JSON Web Tokens, and the documentation says mistakes can cause serious errors that may have a severe impact on the security of your application.
That's a polite way of saying you will leak something.
Use the client libraries. That's the guidance.
So we have two auth models. Now the constraint that actually decides the Drive design.
Service accounts can't own files in Drive. That's straight from Google's folder guide. Service accounts can't own any files. They have to upload into shared drives, or use OAuth 2.0 to upload on behalf of a human user.
So the app can't just make a folder and be the owner of it.
It cannot. And that forks the whole design in two.
Walk me through the fork.
Path one, shared drives. A shared drive is owned by the organization rather than an individual, which is exactly what you want for client work, because the files don't belong to whoever happened to create them.
And the catch?
Shared drives need a paid Workspace edition. Business Standard, Business Plus, Enterprise, Education. A personal Gmail account cannot create one. So if Rita's on a paid Workspace tier, this is the clean path.
And if she isn't?
Then you go the other way. The service account impersonates a human user through domain-wide delegation, and the files get created as if Rita or her colleague made them.
Which works, but now every file in the system belongs to a person.
And people leave, or change roles, or get suspended while they're on holiday. A file owned by an individual is a file with a person-shaped single point of failure attached to it.
That's the kind of thing you only notice the day it matters.
There's a third wrinkle. Folders aren't special objects. A folder is just a file with a specific MIME type. Application slash vnd dot google-apps dot folder.
The folder is a file.
It's a file with no content and a type that tells Drive to draw it as a folder. You create it with a files.create call, you give it a name, and to nest it you set the parents property to the parent folder's ID.
One parent per file?
One parent. So it's a tree, not a graph. Which for Rita's client and project layout is fine, but it does mean you can't have a folder that lives in two places.
So the provisioning pattern, concretely. Rita adds a new client in the backend.
The backend makes a files.create call for the client folder, reads the returned folder ID, then makes calls for the subfolders underneath it, setting parents each time. And for every folder it creates, it captures the ID and writes that ID into the client row in Postgres.
So the database stores the folder ID, not the folder.
The identifier and the relationship. The bytes stay in Drive. Nothing gets duplicated. And if you ever need to find the folder again, you look up the ID you already have rather than searching Drive by name.
Which matters, because folder names aren't unique.
They're not, and two clients can both have a folder called site plans. The name is for humans. The ID is for the database.
What about limits? Two people, so probably not a concern, but.
Enormous on paper. Five hundred million items per user, five hundred thousand items per folder, a hundred levels of nested folders in My Drive. For a two-person consultancy that's effectively unlimited. But the hundred-level cap is a good reason not to let the tree grow organically, because somewhere around level forty someone has built a maze.
Let's talk money, because the pitch of this whole thing is that it stays cheap.
Cloud Run plus Cloud SQL Postgres is described over and over as the boring default for small and mid-sized GCP workloads. Cheap, scales to zero, integrates cleanly with IAM.
And the Postgres tier pricing?
A db-f1-micro instance, which is point two of a vCPU and point six of a gig of memory, list price is about eleven dollars and thirty-two cents a month. If you jump to a two-vCPU, seven and a half gig instance, you're at about a hundred and twenty-two dollars and forty-nine cents a month.
So the small one costs less than lunch.
There's a practitioner report of a Cloud Run plus Cloud SQL tenant app running at eighteen to twenty-two dollars a month. That's a realistic small-firm bill, not a theoretical one.
Cloud Run's cold start is what, a couple hundred milliseconds?
Around two hundred milliseconds, and it scales to zero, so you pay nothing when nobody's working. Cloud SQL doesn't scale to zero, it's a provisioned instance, but that's the trade you make for having Postgres.
And the connection limit is where people get bitten.
It's fixed by tier. A two-vCPU custom instance gives you about a hundred connections. So you pool connections in the app, and if you're going to have a lot of concurrent workers, you put PgBouncer in front of it.
So the honest summary is that the entire business runs for the price of a couple of coffees a month, and the hard parts aren't financial.
They're architectural. The auth model, the file ownership constraint, and the maintainability question we'll get to.
One more piece of context before we move into the working day, because it changes the picture a little. The free API layer is becoming metered.
Google started adjusting Workspace API quotas on the first of May this year, starting with Gmail, Calendar and Drive, and introducing a standardized tiering model tied to the Workspace MCP server launch.
And the mechanics?
New projects created on or after that date get the new quotas. Projects that used the APIs between November last year and April this year keep the old quotas for at least sixty days. And later this year, with ninety days' notice, quota increase requests will require Cloud billing enabled, and usage above the standard daily thresholds will generate charges.
So what does that mean for Rita's volumes?
Nothing today. The standard Drive threshold is four hundred million quota units a day. She's nowhere near it. Google's own expectation is that less than one percent of active developers will need to move beyond the standard tier.
But the direction of travel is the point.
The direction of travel is that the API layer has a meter on it now. Not because Rita's about to hit it, but because the argument for keeping your business data portable gets stronger when the alternative has a bill attached.
Okay. Rita's day. New client, new project, and everything that follows.
Client record first. Name, contact, site address, budget band, the relationship stuff. That goes into Postgres, because that's the relational heart of the business.
And the folder tree gets provisioned.
Through files.create calls, one per folder, parents set as it descends, IDs captured as it goes. Client folder, then a project folder under it, then the standard subfolders. Drawings, site survey, correspondence, invoices.
And those IDs go into the client and project rows.
Foreign keys pointing at Google objects. That's the pattern for the entire episode. The row knows the ID, the ID resolves to a Drive folder, the folder holds the bytes.
Now the presentation, because this is the piece Rita actually asked about.
Google publishes the exact recipe. It's the Slides mail merge. You build a template deck with placeholder tags. Something like double brace customer name, double brace project address.
So the tags are sitting there in the slide text.
And then the workflow is three steps. Use the Drive API to copy the template. Then use the Slides API's presentations dot batch update call with replaceAllText requests to swap the tags for real values. Images use a separate request that auto-scales the image to the shape of the tag and preserves the aspect ratio.
That last part is the nice touch. The image quote fits the placeholder.
So the designers design a slide with a placeholder exactly the size and position they want, and the image lands in it correctly. No tuning pixel offsets in code.
And there's a warning attached?
There is, and it's in bold in Google's documentation. Once you've created a deck with tags in it, make a copy and use the Slides API to manipulate the copy. Do not use the Slides API to manipulate your primary template copy.
Because someone will, eventually.
Because if the API writes into the template, the tags get replaced, and the template is dead. The next client gets a deck with the previous client's name already baked in. Which is a spectacular way to send a proposal to the wrong person.
So the template is sacred and the copy is disposable.
And the reason this works for Rita specifically is in Google's own rationale. It's easy for designers to fine-tune a presentation's design using the Slides editor. Much easier than tuning parameters in your app to set the rendered slide design.
That's the argument. The designers keep editing in Slides, which they already know, and the app never tries to become a layout engine.
And after the merge, the copy is a completely normal deck. Rita's colleague opens it, moves things around, adds a plant schedule, and nothing about it is special.
What about when the merge fails partway? Half the deck has the new client's name and half still says the placeholder.
It doesn't fail that way. The batch update is all or nothing. If any request in the batch isn't valid, the entire request fails and nothing is applied.
So you either get a fully populated deck or you get an untouched copy.
Right. Which is a much better failure mode than a half-edited document someone might accidentally send.
Docs and Sheets follow the same shape?
Same copy-then-replace pattern. Build a template doc or sheet, copy it with the Drive API, then substitute via each product's own batch update call. The tagging convention is the same.
That's how Rita's project briefs and her plant schedules get generated. Template, copy, fill, edit.
Now the messages and the events, and this is where the "what does the database store" question gets answered properly.
Which is barely anything, in the best way.
The database stores the link and the business meaning. The Gmail message ID, the thread ID, the Calendar event ID, the calendar ID. Plus metadata. Which project, which client, which milestone, what status it's in.
And the message bodies and attachments stay in Gmail.
They stay in Gmail. The event details, attendees, the video link, the notes, they stay in Calendar. Postgres holds the pointer and the meaning, Google holds the content.
So when Rita opens a project, the backend says here are the six threads and the four meetings attached to this landscape project, and it's not holding copies of any of them.
And that's the principle working exactly as intended. A single system of record doesn't mean copying everything into one place. It means one place decides what relates to what.
Now the sync question, because this is where the naïve version falls over.
The naive version polls. Every five minutes, list the calendars, list the Drive folders, compare, update. And Google's guidance on this is unusually blunt.
Quote it.
An anti-pattern is to repeatedly poll every calendar of interest. This will very quickly use up all your quota.
So what do you do instead?
Push notifications. You register a webhook, a watch on files or on changes in Drive, and you get an empty POST callback with a header telling you what happened. Added, removed, updated, trashed, untrashed, change.
Empty as in no content?
Empty as in no metadata. The notification tells you something changed but not what. Your application then calls the Drive API using the resource ID or changes token from the notification to find out.
So it's a doorbell, not a summary.
It's a doorbell. And the channels expire. A files channel lasts at most one day, a changes channel at most a week, and there's no auto-renewal. You have to re-watch before it expires.
Which means there's a scheduled job whose entire job is to keep the doorbell alive.
A small one, but it has to exist. And it's exactly the piece that a scattered approach with a script here and a script there tends to miss, because nothing about it is visible until the notifications stop.
And the durable reconciliation mechanism?
The changes API. You call get start page token to get a starting point, save that token, and then list changes with it to enumerate everything that's happened since your last sync. That's how you catch anything the doorbell missed.
Belt and braces. The push notification tells you to look, the changes token tells you what you missed while you weren't looking.
Which matters because a webhook can fail. The callback URL goes down for an hour, the notifications queue up or get dropped, and you have no idea what you missed. The changes token is the answer to that.
Now the quota trap, because this one is nasty.
It is. With domain-wide delegation, by default the service account is charged against the per-minute, per-user, per-project quotas, and not the user you're impersonating.
Say that again, because it's counterintuitive.
The service account wears the quota, not the person. So if your app is acting on behalf of Rita and her colleague and every client they email, all of that traffic is being counted against one identity.
So it doesn't matter how many users you're serving, you've got one bucket.
One bucket. And a single service account can get rate-limited even while it's acting for many users. The fix is to pass the quota user parameter, either as a URL parameter or a header, on each request, which attributes the quota to the real user.
Is that documented, or is it tribal knowledge?
It's in Google's Calendar quota documentation. It's documented. It's just buried, and if you're writing a five-line Apps Script you'll never encounter it.
Whereas if you're building the integration layer once, you write the interceptor once and every call gets it.
That's the argument for the layer, honestly. Not that the layer is clever. That the layer is where the ugly details live, once.
What about handling the rate limit errors when they do happen?
Truncated exponential backoff. Wait two to the power of the attempt count plus a random number of milliseconds, capped at a maximum, which is typically thirty-two to sixty-four seconds. And the errors surface as a 403 with user rate limit exceeded, or a 429 with rate limit exceeded.
And the numbers, just so people know how much headroom there is.
Drive is a million quota units a minute per project and three hundred and twenty-five thousand a minute per user, with a daily billing threshold of four hundred million units. Per-method, a file get costs five units, a file list costs a hundred, a download costs two hundred, an update costs fifty.
So a list? A hundred units? Just to list.
Listing is expensive relative to getting. Which is another argument against the polling approach. You're spending a hundred units every time you ask what's in a folder.
And Gmail?
A million two hundred thousand units a minute per project, six thousand a minute per user, daily billing threshold of eighty million. A messages list is five to ten units, a get is twenty, a send is a hundred, a watch is a hundred.
Calendar is request-based rather than unit-based, right?
Ten thousand requests a minute per project, six hundred a minute per user for projects created after May this year, and a daily billing threshold of a million requests.
For Rita, all of this is headroom she'll never touch.
She'll never touch it. The point isn't the ceiling. The point is that all of this is the kind of thing that has to be handled in one place, and the maintainability argument is entirely about putting it in one place.
Make the case. Why a reusable integration layer instead of an Apps Script per task?
Because look at what we've listed. The service-account quota trap. The channel expiry re-watch job. The changes token reconciliation. The copy-then-replace discipline on templates. The backoff handling. Each of those is a thing that has to be right, and each of them is invisible until it's wrong.
In the scattered model, each script gets its own answer.
Each script gets its own answer, or more likely its own version of the same mistake. And then six months later you have eleven scripts, four of which are doing something subtly different, and nobody can tell which ones still run.
The way it goes wrong isn't the script that breaks. It's the script that quietly does the wrong thing and nobody notices.
The script that stops re-watching the Drive channel because the cron job died, and the sync has been stale for three weeks, and everyone assumes it's working because nothing errored.
We did cover the friction between Workspace apps and Google Cloud tools for a solo consultant, a while back. The tension hasn't changed. The tools want to be used the way their designers imagined, and the business wants them used its own way.
The fix is a translation layer, not a fight.
The translation layer. Which is the backend. You build the operations once. Provision a client. Create a project folder tree. Generate a proposal deck from a template. Link a thread to a milestone. Attach an event to a project. Reconcile Drive changes into Postgres.
Each of those is a function the app calls, not a script sitting in a spreadsheet.
The app doesn't know about JWT signing. It doesn't know about backoff. It calls the operation, the operation deals with Google, and the details live in one place where they can be fixed once.
Rita keeps her Workspace investment exactly as it is. The designers still work in Slides. The colleague still works in Gmail. And there's a structured layer underneath that actually knows what relates to what.
Which is what she asked for. Not a replacement. A spine.
One thing worth flagging before we get to Hilbert, and it's the honest open question. The May quota change means the free API layer is becoming metered, and the design fork on shared drives depends on which Workspace edition Rita's actually on.
If she's on Business Standard or above, the shared drive path is clean, the files belong to the organization, and there's no person-shaped single point of failure. If she's not, the app impersonates a human user and every file has an owner with a name.
Which one she picks changes how much of this she can hand to a system versus how much stays attached to a person.
It's a real fork. It's not a detail.
Okay. Let me make one prediction about how this is going to go, because I've watched you all episode.
Go on.
You're going to say the folder structure is the hard part.
The folder structure is the hard part.
You were never going to make it through without saying that.
The folder structure is fine. The problem is what people name them.
I've seen it. I knew a man in the licensing office, Eitan, who ran a shared drive for a department of twenty-three people. Every project folder was named the same way. Two letters, a hyphen, and a number.
Which two letters?
The initials of the person who was going to use it.
The folder is named after a person.
Every folder. And when the person left, the folder kept their initials, because nobody wanted to be the one who renamed it and broke everyone's shortcuts. By the time I got there, fourteen of the twenty-three folders were named for people who had not worked there in years.
The naming scheme was a personnel directory.
The two folders at the top, the ones everyone actually needed, were named after a dog. A retired dog. It had worked in the department, apparently.
The dog worked in the licensing office.
It came in. It was on the org chart. Nobody was going to be the one to take it off.
How long did this go on?
Eleven years. I was there for the last four of them. And underneath all of it, at the bottom of every project folder, was the one folder that mattered. It was called, in full, DO NOT MOVE.
What was in it?
The current version of everything.
Everything?
Every drawing that was still live. There were six hundred and forty of them. Because the way the system had grown, nobody trusted the version in the project folder, so the working copy went into DO NOT MOVE, and the project folder held the old one.
The folder called do not move was the only folder anyone moved things into.
Constantly.
Did you fix it?
I renamed it. First week. It's called CURRENT now.
And?
For about two years afterwards, people would open the drive, look for DO NOT MOVE, not find it, and email me to say the drive was broken.
What happened to the dog's folder?
Still there. That one I left alone.
The one thing you didn't touch was the folder named after a dog.
The dog had a plaque. You don't rename a folder that has a plaque.
The principle we've been describing all episode is that the database maps the structure and Drive just holds it. What Hilbert's describing is the version where the structure was never mapped anywhere, so the folder names became the only record of anything.
The names are the worst possible place to keep a record, because names are what humans edit on a Tuesday afternoon.
The names are what you change when you're annoyed.
That's the piece I'll take from it. If the structure only exists in names, then every rename is a data migration nobody ran.
That's not how I'd say it, but yes.
The folder IDs don't care what anything is called. Rename the client folder tomorrow and the row in Postgres still points at the same folder.
Which is why the ID goes in the row and the name doesn't.
Now you've got it.
The open question for Rita, and this is where the whole series is heading, is what happens when the free API layer stops being free and she's got a business spine that depends on it.
The thresholds are enormous, so it isn't a next-month problem. It's a next-few-years problem. And the answer is the same one that's been running under this whole episode. Keep the relationships in Postgres, let the external services be authoritative for what they host, and make sure you could move a piece of it without moving all of it.
Which is what Part Three is going to stress-test, because WhatsApp and meeting transcription and external CRMs and AI agents are all services that would very much like to be the only place your data lives.
The principle holds. Postgres owns what relates to what. Everything else owns what it made.
Thanks to Hilbert Flumingtop for producing, and for the eleven-year drive tour.
For more along these lines, there's episode thirty-five, The Privacy Gap; episode three forty-seven, Bridging the Workspace-GCP Divide; and episode seventeen fifty, Herman's Music Showcase. This has been My Weird Prompts.
If you want to send us a prompt, send us your own prompt on Telegram at t dot me slash MWP listener bot. And if you've been enjoying the show, a review helps people find it.
We'll be back soon.
See you tomorrow.
