---
id: agencias/portal-do-cliente
industry: Marketing agencies
usage: specific
effort: medium
grain: One contract per screen (e.g. day x client x platform x ad set x ad)
---

# Client portal

The agency's own dashboard receives from Nekt one ready table per screen, isolated by client and with no raw personal data, and it does not break when someone touches a column.

## Executive summary

**For:** Agencies that give clients their own portal or app instead of Looker, spreadsheets or a reporting tool.

**What it solves**

- Each portal screen reads a ready table with an agreed name, columns and key.
- No client sees another client's data, even when the source is shared.
- Phone numbers, emails and tax IDs reach the app pseudonymized, never raw.

**Why it matters:** The portal becomes the product the client opens every week. If a column changes, the screen goes blank with no error at all. If a filter goes missing, the client sees reviews or leads from another business, and that is a data leak, not a visual bug.

**Business questions it answers**

- Is client X's portal up to date as of yesterday?
- Which table feeds the ads screen of the portal?
- Is any client receiving data from another account?
- Which portal screens still depend on a manual load?
- Does the portal number match the report we sent?

**What you need**

- The final media and funnel tables already modeled in Nekt
- A configured destination (the app's Postgres, Supabase or an HTTP endpoint) with its own landing schema
- The registry linking each client to their accounts and their layer

**What you get**

- One delivery table per screen, with a declared grain and upsert key
- Loads triggered when the source finishes, with the last update date on every row
- Client isolation and pseudonymization done before the data leaves Nekt
- One table per client when each client has its own AI agent

## Detailed

### The problem

The client app does not query Nekt in real time: Nekt pushes ready data into the app's database. Without a contract per screen, any column change leaves the screen empty, an upsert with an incomplete key overwrites rows, and a missing filter on a shared source hands the client another business's data. None of this raises an error: the destination keeps running green.

### Business rules

1. **The delivery table is a contract.** Each screen reads a consumption table with a fixed name (for example, a mart_ prefix) and explicit, typed columns. The name and the columns are the contract with the app. New columns are added; existing columns are never renamed or retyped; a change of grain becomes a new table. Source normalization lives in stg_ tables, with no business rules. (Avoid: Renaming a column in the table the app reads and finding out from the client's complaint.)
2. **The upsert key is the whole grain.** Declare the upsert key with every column of the grain. On the ads screen, the key is date, client, platform, ad set and ad: the ad set is included because in Google the same ad can sit in more than one ad group. Run a uniqueness check on the key before each load. (Avoid: Upsert by date and ad, which overwrites the same ad in another group.)
3. **Never write to the app's public schema.** The destination writes to a landing schema (staging or landing). The app's triggers and access rules (RLS) handle deduplication, derivation and exposure from there. Writing straight to the public schema bypasses that logic and leaves raw data visible to the front end. (Avoid: Pointing the destination at the public schema of the app database.)
4. **Client isolation inside the transformation.** A shared source (a Google Business Profile account with many locations, a Business Manager with many ad accounts) needs a filter by the client's official identifier (location_id, account_id from the registry) inside the delivery transformation itself. A forgotten filter raises no error: it delivers dozens of businesses' data to one client. When each client has its own AI agent, write one table per client in that client's folder, so the agent only sees its own dataset. (Avoid: Trusting the app to filter by client after it receives the whole account's table.)
5. **Personal data leaves pseudonymized.** Pseudonymization happens in Nekt, not in the app. A phone number becomes a hash of the normalized number (digits only) plus the last 4 digits; email and tax ID become hashes; addresses and fields the dashboard does not use are not sent. If a screen needs the raw value, the discussion is about app permissions, not about the query. (Avoid: Sending the full phone number and hiding it on screen.)
6. **The client comes from the registry, not the layer name.** Link client and layer through an identity column in the client registry (for example, client_slug or the Nekt layer recorded in the portal), with at most one client per layer. Layer names cannot be renamed and drift from the client's commercial name. (Avoid: Matching the portal client to the Nekt layer by name.)
7. **Load on event, not on the clock.** The delivery transformation fires when the run of the source it reads finishes, so it never runs before extraction is done. A fixed schedule only where the destination has its own window. A manual trigger is not a live pipeline and must be flagged as such. (Avoid: Scheduling the load at 7 a.m. and hoping the source has finished.)
8. **Freshness inside the table.** Every delivery table carries the load timestamp and the latest data date. The copy of the portal's client registry in Nekt can lag up to 24 hours: before calling a mismatch between portal and Nekt, check the source's last run. (Avoid: Showing the number without saying how recent it is.)
9. **Same metric, same name on every platform.** Metric columns use the same names as the portfolio media table (spend, clicks, leads, conversions), so the app can reuse its mapping. Each platform fills its own columns and leaves the others null, never zero. Values are already in reais, converted before delivery. (Avoid: One column per platform with different names, or zero where the metric does not exist.)
10. **Thumbnails with links that do not expire.** The ad image comes from adimages.permalink_url in Meta, described as a permanent link. Other thumbnail links expire. Flag on each row whether the thumbnail is permanent, so the app knows when to fetch again. (Avoid: Saving the temporary thumbnail and watching the image break a few days later.)

### From warehouse to screen

Layers that separate business rules from what the app reads.

- **stg_<source>** → Normalization of one source, one grain per row, no business rules
- **mart_<screen>** → The exact shape of the screen: the contract with the app
- **filter and pseudonymization** → Only the screen's client, no raw personal data
- **destination in staging or landing** → Upsert by the grain key (`date, client, platform, adset_id, ad_id`)
- **app triggers and RLS** → The app deduplicates and exposes
- **public schema** → Never write here directly

### Definitions

| Metric | Definition |
|---|---|
| Load contract | delivery table name, typed column list, grain and upsert key of a screen, agreed with whoever builds the app |
| Upsert key | set of columns that identifies a row of the grain; no combination may repeat in the delivery table |
| Portal client | client identifier in the portal registry, linked to a single Nekt layer |
| Client-safe row | row that only holds data for the screen's client and no raw personal data |
| Pseudonymized phone | hash of the digits-only number, plus the last 4 digits |
| Freshness | last load timestamp and latest data date, per delivery table |
| Acceptable lag | interval between source runs plus an agreed margin (e.g. 24 hours for a daily source) |

### Pitfalls

- **Blank screen after a tweak.** A column was renamed in the delivery table. The destination kept delivering and the app started reading a null field, with no error at all. How to avoid: Treat the table as a contract: add columns, never rename.
- **Other businesses' reviews on the dashboard.** The agency's Google Business Profile account covered dozens of locations. Without the location_id filter, one client's dashboard would show every location's reviews. How to avoid: Filter by the client's official id inside the delivery transformation.
- **Ad overwritten by the upsert.** In Google, the same ad appeared in two ad groups. With the key missing the group, the second row erased the first. How to avoid: Include the ad set or ad group in the upsert key.
- **Portal and Nekt with different portfolios.** Edits made in the portal after the daily extraction only showed up in Nekt the next day, and client count and MRR diverged for a day. How to avoid: Check the source's last run before calling a mismatch.
- **Dashboard stalled by a source in another layer.** One client's dashboard depended on a connector registered in the agency's own layer. When it failed, the investigation only looked at the client's layer. How to avoid: Document the source layer of each delivery table.
- **Two destinations for the same screen.** Two active destinations delivered the same data to the same app, one newer than the other. Changing the wrong one had no effect. How to avoid: One destination per delivery table, with the old one switched off.

### Acceptance test

- [ ] For one client and one closed month, spend summed in the delivery table equals the portfolio media table (zero difference).
- [ ] The upsert key does not repeat in any row of the delivery table.
- [ ] Each destination receives exactly one distinct client, or only the clients authorized for that app.
- [ ] No delivered phone number has more than 4 readable digits.
- [ ] The last load date of each table is within its source's acceptable lag.

### Context document

```text
Client portal
- The portal does not read Nekt in real time. Nekt delivers consumption tables (mart_<screen>) into the landing schema of the app database, by upsert.
- Each delivery table is a contract: fixed name, columns, grain and key. Do not rename columns; a grain change is a new table.
- Upsert key for the ads screen: date, client, platform, adset_id, ad_id.
- The destination never writes to the app's public schema.
- Every delivery table is filtered by the client's official id (account_id, location_id) inside the transformation. A shared source without a filter is a leak.
- Phone numbers leave as a hash of the normalized number plus the last 4 digits. Email and tax ID leave as hashes. Addresses do not leave.
- The client is linked to the layer by the registry's identity column, never by name.
- To answer whether the portal is up to date, read the last load date and the latest data date of the delivery table.
- One metric name across platforms; null where the platform does not measure it.
Client exceptions:
- <client>: destination <destination>, schema <schema>, screens <list of mart_ tables>.
- <client>: reviews filtered by location_id <id> from the agency's shared account.
```

### Variations

- **HTTP endpoint:** App without a reachable database: send the table in batches (e.g. 1,000 rows per call), with the token kept in Nekt's secrets vault, never in the code.
- **Old tool's template:** To reuse a ready Looker dashboard, deliver the table with the same field names as the connector that used to feed the template.
- **The client's business data:** The same pattern works for screens beyond marketing, such as reviews, support or renewals, always with one table per screen.