Consent, isolation & deletion

Consent

A join key, not a filter you remember to apply

Both shapes below return exactly the same five profiles today. Only one of them is still correct on the evening someone deletes a line while chasing an empty-audience bug.

SELECT t.profile_id
FROM profile_traits AS t
INNER JOIN consent_current AS c
  ON c.profile_id = t.profile_id
WHERE t.p_purchase_7d >= 0.62
  AND c.marketing = 1
  AND c.sale_share = 1
  AND c.captured_at >= now() - INTERVAL 24 MONTH

That is the structural move, and it is the whole section. Audience compilation joins the consent table, so membership is the result of a query that cannot execute without it. There is no code path where someone forgets, because there is no code path at all. The consent predicates themselves are appended by the compiler from what the destination declares it requires — never hand-typed into an audience definition.

And consent is not a boolean. It is a small record with a jurisdiction, a timestamp and a provenance, and every one of those fields exists because one day someone will ask you a question the boolean cannot answer.

interface ConsentState {
  analytics: boolean;
  marketing: boolean;
  saleShare: boolean;          // "sale or sharing", in the US state-law sense
  jurisdiction: string;        // "US-CA", "EU-DE"
  capturedAt: string;          // ISO 8601
  source: "banner" | "gpc" | "preference_center" | "import";
}

• The jurisdiction changes what the booleans mean. A yes under an opt-out regime that defaulted to yes is not the same claim as a yes under a regime that requires an affirmative act. Store the regime with the answer or you cannot later tell a default apart from a decision.
• The source is evidentiary weight. A banner click, a browser opt-out header and a spreadsheet import are not the same claim. Someone will eventually ask you to prove a given profile said yes, and “it was in the import” is a very different sentence to “they clicked, at this timestamp, on this page”.
• The capture time is an expiry clock. Several regimes expect re-consent after a period. A five-year-old yes is not a yes, and without a capture time you have no way to age one out.
• The whole record travels with the event. Consent is stamped at collection, not looked up later — so the state you enforce is the state at the moment the data was created.

SELECT t.profile_id
FROM profile_traits AS t
INNER JOIN consent_current AS c
  ON c.brand_id = t.brand_id AND c.profile_id = t.profile_id
WHERE t.brand_id = {brandId:String}
  AND t.env      = {env:String}
  AND t.days_since_last_order <= 30
  AND t.p_purchase_7d >= 0.62
  AND c.marketing  = 1        -- destination-required basis, injected by the compiler
  AND c.sale_share = 1
  AND c.captured_at >= now() - INTERVAL 24 MONTH

Signals

A browser opt-out is honoured at collection, not at activation

A universal opt-out signal arrives as a request header on the very first call. The right time to act on it is before the event exists downstream: the collector marks the event as not shareable and publishes that, so nothing later has to know the signal is a thing.

export function resolveConsent(req: FastifyRequest, claimed: ConsentState): ConsentState {
  // A universal opt-out signal, sent by the browser as a request header and
  // legally recognised in several US states. Honour it here, at the earliest
  // point where the data might still not exist.
  const optedOut = req.headers["sec-gpc"] === "1";
  if (!optedOut) return claimed;
  return {
    ...claimed,
    saleShare: false,
    marketing: claimed.jurisdiction.startsWith("EU-") ? false : claimed.marketing,
    source: "gpc",
    capturedAt: new Date().toISOString(),   // server-stamped, like every ts in the envelope
  };
}

The principle generalises well past this one header: enforce at the earliest point where the data still might not exist. Every layer you push enforcement downward is another copy you have to remember to clean.

Isolation

Brand and environment are partition keys, and the agency role is why

Every document, row and query carries a brand and an environment, and storage is namespaced by both. Post 1 defined that type and threaded it through; this post adds the layer underneath it, for the case where your application code is not the thing making the query.

• Partition by brand, always. Event tables partition on brand and month, trait tables on brand. Cross-brand reads are not merely filtered, they are physically separate files.
• A row policy sits below the application. Application filters protect you from application bugs. A row policy on the executing database role protects you from the SQL console, the reporting tool and the ad-hoc notebook.
• Roles are analyst, marketer, agency and admin. Only admin may write consent records; only marketer and admin may trigger a sync; agency is read-only and scoped to a single brand.
• The agency role is the honest reason. It is a login you do not administer, held by someone who does not work for you, who is also working for a competitor next Tuesday. Design for that user and the internal cases take care of themselves.

CREATE ROW POLICY agency_brand_scope ON activation.profile_traits
  FOR SELECT USING brand_id = currentSetting('SQL_brand_id')
                AND env      = currentSetting('SQL_env')
  TO agency, analyst, marketer;

-- admin is deliberately excluded: break-glass access is logged, not silently unfiltered.
CREATE SETTINGS PROFILE brand_bound SETTINGS
  SQL_brand_id = '' READONLY, SQL_env = '' READONLY;

The session settings those policies read are populated from signed token claims — never from a request parameter, a header the client controls, or anything a screen passed along.

import type { BrandScope } from "../contracts/scope.js";

export function scopeFromClaims(claims: JwtClaims): BrandScope & { role: Role } {
  const roles = claims.roles ?? [];
  if (roles.length === 0) throw new ForbiddenError("no roles in token");   // fail closed
  const role = pickHighest(roles);
  if (!claims.brand_id || !claims.env) throw new ForbiddenError("unscoped token");
  return { brandId: claims.brand_id, env: claims.env, role };
}

An empty role list is a rejection, not a default. The failure mode you are avoiding is the one where a misconfigured identity provider stops emitting a claim and every token quietly becomes an unfiltered one.

Deletion

A deletion that stops at your warehouse is not a deletion

A request or a consent revocation lands as a tombstone for one profile. Deleting the rows is the easy part and the least of it. Run the fan-out below with the ledger switched off — the case where every internal step succeeds and the deletion still did not happen.

• The warehouse first, lightly. Delete the profile’s event and trait rows. Lightweight deletes are asynchronous, so the receipt records the mutation id and the step is not done until that mutation reports finished.
• The identity graph needs a split, not a delete. Remove the profile’s nodes and recompute the connected component, per post 2 — otherwise a device node keeps two other people welded together on the strength of someone who is gone.
• Membership lives in the config store. Every membership document referencing the profile is pulled, across every audience, including paused ones.
• The sync ledger answers the question that matters. Which destinations has this profile ever been pushed to, and under which sync.

That last bullet is the point of the post. You can only remove a profile from a destination if you know it was ever sent there — which means a per-profile ledger row written at activation time, long before the first request, and if you skip it no amount of later engineering recovers the history. The ledger itself is post 5’s; here it is simply the table you read.

SELECT destination, audience_id, argMax(sync_id, ts) AS last_sync_id, max(ts) AS last_sent_at
FROM sync_ledger
WHERE brand_id = {brandId:String} AND profile_id = {profileId:String}
GROUP BY destination, audience_id

export async function runDeletion(scope: BrandScope, profileId: string): Promise<DsarResult> {
  const receipts: StepReceipt[] = [];
  for (const step of STEPS) {           // ordered: warehouse, graph, membership, ledger, destinations
    try {
      receipts.push(await step.run(scope, profileId));
    } catch (err) {
      // Do not swallow. The request stays open and visible until every step has a receipt.
      receipts.push({ step: step.id, status: "failed", error: String(err), at: nowIso() });
      await dsarStore.markIncomplete(scope, profileId, receipts);
      throw new DsarIncompleteError(profileId, step.id);
    }
  }
  await dsarStore.markComplete(scope, profileId, receipts);
  return { profileId, status: "complete", receipts };
}

When a destination’s removal call fails — a rate-limit response, an expired token, a maintenance window — you retry with backoff, and until it succeeds the request is not complete. That state has to be visible: an open-request gauge with an age, alerting past your regulatory clock, not a swallowed exception in a log nobody reads. The silent failure here is the nastiest in the series, because a half-finished deletion looks exactly like a finished one from the inside.

Audit

A trail, not a second copy of the data

One append-only line per activation sync and per deletion. It is deliberately boring.

{"ts":"2026-08-19T14:22:07Z","brand":"northwind","env":"prod","actor":"sync-engine",
 "audienceId":"aud_high_intent_30d","destination":"meta","syncId":"sync_7f31","adds":1842,
 "removes":210,"rejected":37,"matchRate":0.671,"consentBasis":["marketing","saleShare"],
 "holdoutBps":1000,"experimentId":"exp_prospecting_q3"}

• Counts, hashes and ids only. No addresses, no phone numbers, no member list. An audit log that contains the data is a second breach surface with worse retention rules than the first.
• The consent basis is the reconstruction key. It records which fields of the consent record authorised this sync, so a year later you can answer “under what basis did these eighteen hundred people go to a destination”.
• Deletion lines carry the same shape. Actor, profile id, and one receipt per step — enough to show a fan-out happened without restating what was deleted.
• Append-only, or it is not evidence. Write it to object storage with versioning and a retention lock; a log you can edit proves nothing.

This is the artefact that turns a described control into a demonstrated one: the sync line shows the consent basis was evaluated, and the deletion receipts show the fan-out reached every destination the ledger named. You are not building a compliance product. You are making the system’s own behaviour legible enough that an auditor’s question has an answer that is a query rather than a meeting.

git clone https://github.com/xaviramirezcom/open-activation-platform
cd open-activation-platform
git checkout v4-consent
pnpm install
pnpm activate up            # streaming + warehouse + config store, via docker compose
pnpm activate seed          # synthetic multi-brand traffic, orders and spend

pnpm activate sync --audience aud_high_intent_30d --dest meta --brand northwind
pnpm activate consent:revoke --profile prof_8812 --field saleShare --brand northwind
pnpm activate sync --audience aud_high_intent_30d --dest meta --brand northwind

pnpm activate dsar --profile prof_8812 --brand northwind --print-receipts

Explain it back