LinkHub — Bio Pages, Branded Links & Dynamic QR Codes

LinkHub — Bio Pages, Branded Links & Dynamic QR Codes #

Product Specification — Final

Overview #

LinkHub is a single workspace that unifies three things creators and marketing teams currently buy separately: a link-in-bio page, branded short links on their own domain, and dynamic QR codes whose destination can be changed after the code has been printed. The product's thesis is that these are not three products — they are three delivery surfaces for the same underlying object (a destination, owned by a workspace, measured by one analytics pipeline). Every tool that treats them separately fragments the numbers, the branding and the team permissions. LinkHub does not.

The audience is four overlapping groups. Independent creators need one page that holds their whole presence and analytics they can actually read. Marketing teams need branded links, UTM discipline, scheduling and A/B testing without a growth engineer. Agencies need many client brands in one login, with per-client workspaces, per-resource access grants and clean separation of analytics. Small businesses need a QR code they can print on a menu, a box or a shopfront window and repoint next season without reprinting. The plan tiers in Section 22 are drawn along those lines: Free proves the product, Pro serves the individual professional, Business serves the team and the agency.

Three surfaces are unified into one workspace. Bio pages are server-rendered public pages built from a block catalog (Section 10) in a drag-free-if-you-need-it editor (Section 9). Branded short links (Section 12) resolve on a LinkHub-owned default host or on the customer's own verified domain (Section 13). Dynamic QR codes (Section 14) are short links with a rendered, print-grade symbol and a permanence guarantee attached. All three write into the same analytics ingestion pipeline (Section 17) and are read back through the same dashboards, reports and exports (Section 18). One audience definition, one attribution model, one export.

The architectural spine is deliberately small and deliberately boring. Four deployables: a Next.js application serving the marketing site, the authenticated dashboard and the server-rendered public bio pages; a Hono redirect resolver that carries the highest traffic and the tightest latency budget; a separate Hono service for the public REST API so that API traffic can be scaled and rate-limited independently of the human dashboard; and a BullMQ worker fleet for everything asynchronous — analytics ingest, rollups, webhook delivery, ESP sync, domain verification, TLS renewal, QR rendering, exports and retention purges. PostgreSQL is the system of record for every durable fact in the product. Redis is a cache and a buffer — resolved-redirect cache, negative cache, rate-limit counters, session cache, and the click event stream — and is never authoritative. Section 4 defines the stack and its versions; Section 6 defines the schema; Sections 11 and 17 define the two hot paths.

The public delivery path is optimised to a degree that will look excessive until you read the budgets in Section 11. A bio page must reach first contentful paint in under 0.8 s and largest contentful paint in under 1.2 s on a mid-range Android handset over a throttled 4G connection, in under 40 KB of gzipped HTML, with zero bytes of render-blocking JavaScript. A redirect must complete server-side processing in under 50 ms at the 95th percentile. These are not aspirations printed in a design doc; they are enforced in continuous integration by a Lighthouse budget gate and a bundle-size gate that fail the pull request, and by load tests that fail the build if the redirect path misses its target under sustained load.

Three product promises are non-negotiable and shape decisions throughout the document. First: a printed QR code never stops resolving. Not on downgrade, not on non-payment, not on workspace deletion, not on account deletion. The slug reservation is permanent, resolution never returns 404 or 410, and the fallback chain in Section 14 always terminates in something a human can read. Physical media cannot be recalled, so the software must behave as though it never can be. Second: the public page is fast on a mid-range phone over 4G, and it works with JavaScript disabled. Every link on a bio page is a server-rendered anchor element. JavaScript is progressive enhancement — the analytics beacon, media embeds, the share sheet — and its absence degrades nothing that matters. Third: analytics are cookie-free by default. Visitor identity is a salted, daily-rotating hash; the raw IP address is never written to any durable store; geographic resolution stops at country and region. No consent banner is required for first-party measurement, and the reasoning for that position is documented rather than assumed (Section 23).

Read the document in order the first time. Sections 1 through 5 establish the decisions, the stack and the conventions that everything else assumes. Section 6 is the schema and is the reference you will return to most. Sections 7 through 22 are the product itself, roughly in build order. Sections 23 through 27 are the cross-cutting concerns — security and privacy, accessibility, operations, testing, deployment — that must be built into features as they are written rather than retrofitted. Section 28 is the execution plan; Section 29 is written directly to the engineer or agent doing the building; Section 30 is the reference material you will look things up in. Each concern has exactly one canonical home. Where a topic appears in more than one section, one section owns it and the others cite it by number; Section 29.4 is the lookup table from concern to owning section.

Every decision required to begin building has been made. Plan limits, role permissions, error codes, retention windows, cache keys, redirect status codes, hashing parameters, rate limits, performance budgets, environment variables and rollout order are all specified with concrete values. Where a genuine choice existed and either option would have worked, one option has been picked and the rationale recorded in the decision log in Section 30.10. There are no open questions, no placeholders and nothing deferred to a later conversation. If you find yourself needing to ask, Section 29.5 tells you exactly how to resolve it and where to record what you decided.

Table of Contents #

• 1. Before You Start — Customization Decisions
• 2. Project Overview & Vision
• 3. Personas, Roles & Permission Model
• 4. Technology Stack & Architecture
• 5. Conventions & Standards
• 6. Data Model & Database Schema
• 7. Authentication, Sessions & Account Management
• 8. Workspaces, Members, Invitations & Audit Log
• 9. Bio Page Builder — Editor Experience
• 10. Bio Page Block Catalog
• 11. Public Delivery Path — Rendering & Performance
• 12. Branded Short Links
• 13. Custom Domains & TLS Provisioning
• 14. Dynamic QR Codes
• 15. UTM Builder, Scheduling, Expiry & Targeting Rules
• 16. A/B Testing
• 17. Analytics Ingestion Pipeline
• 18. Analytics Dashboards, Reporting & Export
• 19. Integrations & Pixels
• 20. Email Capture & Lead Management
• 21. Public REST API v1
• 22. Billing, Plans & Entitlements
• 23. Security, Privacy & Compliance
• 24. Accessibility
• 25. Observability, Operations & Runbooks
• 26. Testing Strategy & Quality Gates
• 27. Deployment, Environments & Configuration
• 28. Milestones & Execution Plan
• 29. Executor Instructions
• 30. Appendices

1. Before You Start — Customization Decisions #

Every decision in this section already has a working default, and those defaults are internally consistent with one another and with the rest of this document. A team can clone the repository, follow Section 27, and build the entire product without changing a single value below. This section exists because some of these values are commercial or organisational choices that a specific team may want to make differently, and it is cheaper to change them deliberately on day one than to discover them scattered across the codebase in month three. Treat the table as a pre-build checklist: read it, change nothing or change a few rows on purpose, record what you changed in DECISIONS.md at the repository root, and start building.

1.1 The decision table #

Blast radius is rated as Low (config or token change, no code edits), Medium (config plus localised code changes in one or two areas), or High (schema, contract, or architectural consequences that ripple across sections).

Every "Lives in" cell names the section that owns the value. If two sections mention a value, the one named here is the one to edit; the other refers to it.

1.2 How to change a default safely #

1. Change the value in its single canonical location. Every row above names a section; each of those sections names one file or one configuration key. There are no duplicated constants — if you find yourself editing the same number twice, the second one is a bug.
2. Record the change in DECISIONS.md at the repository root using the ADR format in Section 5.10.2: what you changed, why, and which row number in this table it corresponds to.
3. Re-run the quality gates in Section 26.13. Contrast changes are caught by the accessibility gate (Section 26.6), budget changes by the performance gate (Section 26.7), entitlement changes by the entitlement test suite (Section 26.5.1), and API-shape changes by the contract tests (Section 26.10).
4. If the change touches a row marked High, write an ADR before the code change, not after.

1.3 Decisions that are not customizable #

The following are invariants, not defaults. They are load-bearing for correctness, legal posture or the product promise, and other sections are written assuming they hold. Changing one is a fork of the product, not a configuration change.

2. Project Overview & Vision #

2.1 What LinkHub is #

LinkHub is a hosted platform where one workspace owns three linked surfaces: a bio page (a fast, public, mobile-first landing page addressed by handle or custom domain), branded short links (arbitrary destinations behind a short slug on a LinkHub host or the customer's own domain), and dynamic QR codes (printable symbols whose destination is editable after printing and whose resolution is permanent). All three emit into one analytics pipeline, so a workspace sees a single unified view of clicks, scans and page views across every surface, sliced by the same dimensions, filtered by the same date range, exported in the same format, and governed by the same per-workspace roles, per-resource grants and plan entitlements.

2.2 The problem #

The person who needs these three capabilities almost always needs all three, and today they buy them separately.

A typical creator or small marketing team runs a link-in-bio tool for the profile page, a URL shortener for campaign links, and a QR generator for print and packaging. The consequences are concrete:

1. Three analytics silos. The bio page tool knows page views. The shortener knows clicks. The QR tool knows scans. Nobody can answer "which channel drove traffic to the product page last month" without exporting three CSVs and joining them by hand — and the joins are unreliable because each tool defines a unique visitor differently.
2. Three brand surfaces to keep consistent. A custom domain configured in one tool does not help the other two. Colours, logo and typography are re-entered three times and drift immediately.
3. Three permission models, or none. Shorteners and QR generators typically ship a single login. An agency running eleven client brands ends up sharing one password, and there is no record of who changed a destination.
4. A printed QR code that dies when billing lapses. This is the failure that removes trust from the entire category. A small business prints ten thousand boxes with a dynamic QR code on them, a card expires, the account downgrades or is cancelled, and every box becomes a 404. The physical artefact outlives the subscription by years, but the redirect does not.

LinkHub removes all four. The fourth one is removed absolutely: a QR slug is reserved permanently and resolution never fails, regardless of plan, payment state, workspace deletion or account deletion. The complete fallback chain is specified in Section 14.8.2 and its legal carve-out in Section 23.15.

2.3 Product principles #

Each principle below has a direct, testable consequence for how the system is built. They are ordered by how often they will be invoked when resolving a design argument.

2.3.1 A printed code is a promise #

Consequence for the build. QR slug reservations live in a table with no soft-delete column and no purge path. Resolution has a four-rung fallback chain that cannot terminate in an error. The QR fallback chain carries a 95% line-coverage gate (Section 26.12). Billing state may change branding and may disable editing; it may never stop resolution.

2.3.2 The public path is the product's shop window #

Consequence for the build. The redirect resolver is its own deployable (apps/edge) with its own latency budget of p95 < 50 ms server-side, its own scaling policy, its own blue/green deployment strategy, and no dependency on the dashboard being up. Public bio pages have a hard performance budget enforced in CI: ≤ 40 KB gzipped HTML, ≤ 14 KB critical inline CSS, zero blocking JavaScript, LCP < 1.2 s on a mid-range Android device over 4G. Section 11.2 owns these budgets and Section 26.7 owns their enforcement.

2.3.3 Analytics must not be able to break delivery #

Consequence for the build. Event capture is fire-and-forget into a Redis Stream. The redirect response is never blocked on analytics. If the stream write fails, the redirect still succeeds and a counter increments for alerting. There is no code path in which an analytics failure produces a user-visible error. Section 17.2 specifies the capture contract.

2.3.4 Privacy is a design constraint, not a settings page #

Consequence for the build. There is no visitor cookie by default. Visitor identity is a salted rotating hash whose salt changes daily and is never logged. Raw IP addresses exist only in process memory long enough to derive that hash and a country/region lookup, and are then discarded — they are never written to a log line, a database column, or an object in storage. The raw user-agent string is treated the same way: parsed in memory, reduced to a family label and a daily-salted hash, then discarded. The consent banner only appears when the workspace has enabled a third-party pixel or the visitor is in a gated region. Section 23.12 owns the model and its DPIA summary.

2.3.5 Every capability is governed by one permission model #

Consequence for the build. There is exactly one permission matrix (Section 3.3) and exactly one evaluation algorithm (Section 3.5). No feature ships with a bespoke access check. Adding an action to the product means adding a row to the matrix, and the matrix is executable: the entitlement and permission tests enumerate it directly, and carry a 95% line-coverage gate (Section 26.12).

2.3.6 Defaults are decisions #

Consequence for the build. No configuration flag exists without a shipped default that produces a working, safe, accessible result. A workspace that changes nothing gets a page that passes contrast, a QR code that scans, links that resolve, and analytics that are lawful in the EEA. Where a user can make a choice that breaks something — a low-contrast theme, a logo overlay that defeats the decoder — the system blocks it and offers a one-click correction rather than silently shipping a broken artefact.

2.3.7 The dashboard is for editing, not for delivering #

Consequence for the build. Nothing on the public path reads from the dashboard's session store, its query layer, or its rendering pipeline. A total outage of the authenticated dashboard leaves every bio page, short link and QR code resolving normally. The degradation ladder in Section 4.9 is written against this separation.

2.4 Target personas at a glance #

Full detail, including failure modes and device context, is in Section 3.1.

2.5 Primary user journeys #

Each journey is written as the surfaces touched in order. "Surface" means the deployable and route group that serves the step.

2.5.1 Creator publishes a first bio page #

2.5.2 Marketer runs a branded campaign with UTMs #

2.5.3 Agency onboards a multi-brand client roster #

2.5.4 Small business prints a QR code on packaging and re-points it later #

2.6 Competitive positioning #

The claim is not that LinkHub has more features than any single category tool. It is that three specific capabilities only exist when the three surfaces share one data model.

The single-sentence positioning: LinkHub is the only one of the three where changing a destination is a first-class, audited, permanent-by-design operation across every surface at once.

2.7 Non-goals #

The following are explicitly out of scope for this build. Each is listed with the reason and its roadmap status. "Not roadmap" means the decision is that LinkHub does not do this, not that it has been deferred.

2.8 Success metrics #

Every metric below has a defined measurement source so it can be instrumented in the same release as the feature it measures. Instrumentation is specified in Section 25.3.

3. Personas, Roles & Permission Model #

3.1 The four personas in depth #

3.1.1 Maya — independent creator #

3.1.2 Daniel — in-house marketer #

3.1.3 Priya — agency operator #

3.1.4 Tom — small business owner #

3.2 The role model #

Roles are per workspace, never global. A user account has no product-wide role. Membership is a row linking a user to a workspace with exactly one role, and a user may hold a different role in every workspace they belong to. There is no "super admin" role in the product; internal staff access is a separate, audited operator path described in Section 25.12.3 and is not modelled as a workspace role.

Rules that hold without exception:

1. Exactly one Owner exists per workspace at all times. See Section 3.6.
2. Role is assigned at invitation time and changed only by an Owner, or by an Admin for the Editor and Viewer roles (an Admin cannot create another Admin — see footnote C3 in Section 3.3.1).
3. A user's role in workspace A has no bearing on their capabilities in workspace B.
4. Removing a membership revokes access immediately: the next request re-evaluates and fails at the membership check in Section 3.5. Sessions are not workspace-scoped, so no session invalidation is required, but any cached entitlement and capability entry for that user and workspace is deleted on the same transaction.
5. Content is owned by the workspace, never by the member who created it. Removing a member never deletes, hides or reassigns content.

3.3 The permission matrix #

This is the canonical permission matrix. Every other section in this document refers to it rather than restating access rules. The matrix is also the specification for the test suite in Section 26.5.2: each row is enumerated as a test case for all four roles.

3.3.1 Legend and conditional footnotes #

3.3.2 Workspace and brand #

workspace.create is account-scoped: there is no workspace context to evaluate it in, so it is decided by the account-scoped branch of Section 3.5 against the plan's workspace cap (1 on Free and Pro, 10 on Business, Section 22.1.2), evaluated against the plan of the billing account that will own the new workspace, not the workspace the user happens to be viewing.

3.3.3 Members, invitations and grants #

3.3.4 Billing and plan #

Billing is an Owner-only domain. Admin has no read access, by design: an agency Admin manages content and people, and the commercial relationship is not theirs. Billing is also dashboard-session-only: no API key of any scope can reach any billing action, so a leaked key can never touch payment state (Sections 3.5 step 2, 21.2.5).

A read-only surface showing quota consumption without prices is available to Admin, Editor and Viewer so they understand why a create action was blocked; it is a different action (workspace.view_quota, allowed to all four roles) and exposes no commercial data.

3.3.5 Custom domains #

Removing a domain does not delete the links bound to it. Section 13.9 specifies the required re-homing step and the branded landing page served in the interim.

3.3.6 Bio pages and blocks #

3.3.7 Branded short links #

A link pinned to a QR code is never archived and never soft-deleted by any of rows 74–75, and it does not count toward the link cap. The QR permanence rule outranks archival; Sections 12.9, 22.2 and 22.5 specify the behaviour and Section 3.5 step 8 refuses the mutation with 409 resource_locked.

3.3.8 Dynamic QR codes #

There is no action anywhere in this product that releases, recycles or reassigns a QR slug. Section 14.8 owns this rule.

3.3.9 Experiments (A/B testing) #

The experiment states these rows refer to are draft, running, paused, concluded, promoted and archived; Section 16.9 owns the lifecycle.

3.3.10 Analytics and reporting #

3.3.11 Leads and email capture #

Leads are personal data. Viewer is denied entirely; export requires step-up authentication and is written to the audit log with the row count. Leads are dashboard-only. There is no lead scope in the API key catalogue (Section 21.2.4) and no lead endpoint on the public API, because an API key cannot satisfy the step-up requirement that row 109 imposes, and weakening row 109 to make an API surface possible would be a privacy regression.

3.3.12 Integrations, pixels and webhooks #

Webhook endpoints are HTTPS-only, with no plaintext-HTTP exception; Section 19.7 owns the transport and SSRF rules that row 116 validates against.

3.3.13 API keys #

An API key never carries more authority than the role of the member who created it, and never carries Owner-only capabilities: there are no billing scopes, no member-management scopes, no audit scope, no lead scope and no workspace-deletion scopes. Section 21.2.4 is the single catalogue of API key scopes; this section does not restate it and does not state its size, because a count restated in two places is a count that will disagree with itself. A key is additionally bound to exactly one workspace at creation and can never address another (Section 3.5, step 2).

3.3.14 Audit log, data export and account data #

Rows 126–128 are dashboard-only for the same reason as leads: the audit log is not reachable by any API key scope (Section 21.2.4).

Deleting a workspace or an account never releases a QR slug reservation and never stops QR resolution (Sections 14.8, 23.15).

3.4 Per-resource grants (Business only) #

3.4.1 The scoping model #

A grant is a row associating a membership with a single resource. Grantable resource types are bio_page, link and qr_code. A membership with zero grants is unscoped and operates at full workspace scope for its role. A membership with one or more grants is scoped, and its authority is the intersection of its role capabilities and its granted resource set.

3.4.2 Intersection with role #

3.4.3 What a scoped member sees #

Invisibility, not merely denial, is the rule. A scoped member must not be able to enumerate resources they were not granted, and must not be able to confirm a resource exists by probing an identifier.

3.4.4 Resources created by a scoped member #

This rule is exact and has no exceptions:

1. A scoped Editor may create new bio pages, links, QR codes and experiments, subject to plan quota. What counts toward that quota is Section 22.2.5's list, not this section's — archived resources are excluded, and a link pinned to a QR code is excluded.
2. On successful creation, the system automatically creates a grant for that resource on the creating membership, in the same database transaction as the resource insert. Without this, the creator would immediately lose access to their own new resource.
3. The resource belongs to the workspace, not to the creator. created_by is recorded for audit purposes only and confers no ongoing authority.
4. If the creating membership is later removed, the auto-created grant is deleted with it. The resource is not deleted, not archived, not hidden and not reassigned. It remains a normal workspace resource, fully visible to Owners, Admins and unscoped Editors and Viewers.
5. If the creating membership is later un-scoped (all grants revoked), the member gains full workspace scope; the auto-created grant becomes irrelevant and is removed as part of the same operation.
6. Auto-created grants are recorded in the audit log with source = auto_on_create to distinguish them from grants an Admin issued deliberately.

3.5 Permission evaluation algorithm #

Every authorised operation in every deployable runs this function. There is no second implementation and no bypass. The order of checks is fixed, because it determines which error a caller sees and therefore what a caller can infer.

authorize(actor, workspaceId, action, resourceRef?) -> Allow | Deny(status, code, details?)

  # 1. Authentication
  if actor is null:
      return Deny(401, "unauthenticated")
  if actor.kind == "user" and actor.user.status == "suspended":
      return Deny(403, "account_suspended")

  # 2. API-key actors: workspace binding first, then the scope gate
  if actor.kind == "api_key":
      if actor.api_key.revoked_at is not null:
          return Deny(401, "api_key_revoked")

      # A key is bound to exactly ONE workspace at creation and can never
      # address another. This check runs BEFORE any capability evaluation, so
      # a key cannot even probe for the existence of another tenant's data.
      if workspaceId is null or actor.api_key.workspace_id != workspaceId:
          return Deny(404, "not_found")

      if action.required_scope not in actor.api_key.scopes:
          return Deny(403, "insufficient_scope",
                      details = [{ field: "scope", issue: "insufficient_scope",
                                   required_scope: action.required_scope }])
      if action.owner_only
         or action.domain in { billing, members, audit, gdpr, leads }:
          return Deny(403, "action_not_available_to_api_key")

      # The key inherits the role of the membership it was created under.
      membership = membershipOf(actor.api_key.created_by_membership_id)
  else:
      # 2a. Account-scoped actions are decided before any workspace exists.
      if action.scope == ACCOUNT:
          return authorizeAccountScoped(actor, action)
      membership = findMembership(actor.user.id, workspaceId)

  # 3. Workspace existence and membership
  #    A non-member and a non-existent workspace are indistinguishable, by design.
  workspace = loadWorkspace(workspaceId)
  if workspace is null or membership is null:
      return Deny(404, "not_found")
  if workspace.deleted_at is not null:
      if action != "workspace.restore" or membership.role != OWNER:
          return Deny(410, "workspace_deleted")
  if membership.status != "active":
      return Deny(403, "membership_inactive")

  # 4. Session assurance requirements
  if action.requires_verified_email and not actor.user.email_verified_at:
      return Deny(403, "email_verification_required")
  if workspace.settings.require_two_factor and not actor.session.two_factor_satisfied:
      return Deny(401, "totp_required")
  if action.requires_step_up and not actor.session.reauthenticated_within(5, MINUTES):
      return Deny(403, "reauthentication_required")

  # 5. Role capability — the matrix in Section 3.3
  capability = MATRIX[action][membership.role]
  if capability == DENY:
      return Deny(403, "insufficient_role")
  if capability == ADMIN_CANNOT_TOUCH_ADMIN         # condition C3
     and targetMembership(resourceRef).role in { OWNER, ADMIN }
     and membership.role == ADMIN:
      return Deny(403, "insufficient_role")

  # 6. Resource resolution and grant scoping
  if resourceRef is not null:
      resource = loadResource(resourceRef, workspaceId)
      if resource is null or resource.workspace_id != workspaceId:
          # Cross-tenant access is answered exactly as a missing row is.
          return Deny(404, "not_found")
      if membershipIsScoped(membership) and resource.type in GRANTABLE_TYPES:
          if not hasGrant(membership, resource):
              # 404, never 403 — a scoped member must not learn the resource exists.
              return Deny(404, "not_found")

  # 7. Plan entitlement
  entitlements = entitlementsFor(workspace)          # cached, Section 22.2.6
  if action.required_feature and not entitlements.has(action.required_feature):
      return Deny(403, "plan_feature_unavailable",
                  details = [{ field:   action.required_feature,
                               issue:   "feature_unavailable",
                               plan:    entitlements.plan }])
  if action.consumes_quota:
      current = quotaUsage(workspace, action.quota_key)   # Section 22.2.5 defines
      limit   = entitlements.limit(action.quota_key)      # what is counted
      if limit is not UNLIMITED and current >= limit:
          return Deny(403, "plan_limit_reached",
                      details = [{ field:   action.quota_key,
                                   issue:   "limit_reached",
                                   limit:   limit,
                                   current: current,
                                   plan:    entitlements.plan,
                                   kind:    action.quota_kind }])   # "count" | "period"

  # 8. Resource state
  if resourceRef is not null and action.mutates:
      if resource.deleted_at is not null:
          return Deny(410, "resource_gone")
      if resource.archived_at is not null:
          return Deny(409, "resource_archived")
      if resource.locked_by_downgrade or resource.pinned_to_qr_code:
          return Deny(409, "resource_locked")

  # 8a. Billing enforcement mode — Section 22.7.5 owns the schedule.
  #     writes_blocked is FALSE on days 0-7 of past_due and TRUE from day 8.
  #     This function never computes the dunning day itself.
  if action.mutates and entitlements.writes_blocked
     and not action.exempt_from_billing_block:
      return Deny(403, "billing_write_blocked",
                  details = [{ field:         "workspace",
                               issue:         "past_due",
                               grace_ends_at: entitlements.grace_ends_at,
                               invoice_url:   entitlements.open_invoice_url }])

  # 9. Confirmation token for destructive actions
  if action.requires_typed_confirmation and request.confirmation != expectedConfirmation(resource):
      return Deny(422, "confirmation_required")

  return Allow


authorizeAccountScoped(actor, action) -> Allow | Deny(status, code, details?)

  # Actions with no workspace context. `workspace.create` is the important one:
  # there is no workspace yet, so steps 3 and 7 above cannot run at all.
  if action.requires_verified_email and not actor.user.email_verified_at:
      return Deny(403, "email_verification_required")
  if action.requires_step_up and not actor.session.reauthenticated_within(5, MINUTES):
      return Deny(403, "reauthentication_required")

  if action == "workspace.create":
      account      = billingAccountFor(actor.user)         # Section 22.1
      entitlements = entitlementsForAccount(account)       # Section 22.2
      current      = countActiveWorkspacesOwnedBy(account)
      limit        = entitlements.limit("workspaces")
      if limit is not UNLIMITED and current >= limit:
          return Deny(403, "plan_limit_reached",
                      details = [{ field: "workspaces", issue: "limit_reached",
                                   limit: limit, current: current,
                                   plan:  entitlements.plan, kind: "count" }])
      return Allow

  # Rows 24 and 132 — the actor acting on their own account records.
  return Allow

3.5.1 Rules that constrain the algorithm #

3.5.2 Deny codes emitted here, and their registry status #

Every code below is emitted by this section — by authorize(), by the grant rules in 3.4, or by the ownership rules in 3.6. The Section 30.2 registry is generated from the union of every emitting section, so each of these must appear there with exactly the status shown. Codes that duplicated an existing code under a different name have been renamed to the surviving name rather than registered twice.

The following eleven codes are emitted by this section and Section 30.2 must carry each of them with exactly the status shown:

3.6 Owner transfer and the last-owner rule #

3.6.1 The last-owner rule #

A workspace always has exactly one Owner. The following operations are therefore blocked, each returning 409 with code last_owner_required:

1. Changing the Owner's role to Admin, Editor or Viewer.
2. Removing the Owner's membership.
3. The Owner using workspace.leave.
4. Deleting the user account of a sole Owner of any non-deleted workspace, until every such workspace is either transferred or deleted. The account-deletion flow lists the blocking workspaces explicitly and offers transfer or workspace deletion inline.

The only way an Owner stops being an Owner is by completing a transfer.

3.6.2 Transfer flow #

3.7 Worked examples #

Each example is resolved against the algorithm in Section 3.5, naming the step at which the decision is made.

3.7.1 Viewer tries to change a link destination #

Setup. Workspace on Business. User has role Viewer, no grants. Action link.update_destination. Resolution. Steps 1–4 pass. Step 5: matrix row 65 gives N for Viewer. → 403 insufficient_role. The link's existence is not concealed because a Viewer is entitled to read it; only the mutation is refused.

3.7.2 Scoped Editor fetches a link they were not granted #

Setup. Business workspace with 400 links. User is an Editor scoped to 2 bio pages and 3 links. They request GET /v1/links/{id} for a link outside their grants. Resolution. Steps 1–5 pass (matrix row 63 gives C2 for Editor). Step 6: the resource loads and belongs to the workspace, but hasGrant() is false. → 404 not_found. The same identifier returns 200 for an Admin. The list endpoint never contained it. Priya's requirement in Section 3.1.3 — invisible, not merely non-editable — is satisfied here.

3.7.3 Admin tries to view invoices #

Setup. Admin in a Pro workspace. Action billing.view_invoices. Resolution. Step 5: matrix row 27 gives N for Admin. → 403 insufficient_role. The billing navigation item is not rendered for Admins at all, because the UI calls the batch capability endpoint POST /v1/capabilities (Section 21.8), which runs this same function.

3.7.4 Editor on Free tries to create an A/B experiment #

Setup. Free workspace, unscoped Editor. Action experiment.create. Resolution. Steps 1–6 pass; the matrix gives C1 for Editor. Step 7: experiments_enabled is not in the Free entitlement set. → 403 plan_feature_unavailable with

"details": [ { "field": "experiments_enabled",
               "issue": "feature_unavailable",
               "plan": "free" } ]

The UI renders an upgrade prompt from the details payload rather than from a hard-coded string.

3.7.5 Editor on Pro tries to create an eleventh bio page #

Setup. Pro workspace with 10 bio pages counting toward the cap under Section 22.2.5. Unscoped Editor. Action bio_page.create. Resolution. Step 5 passes. Step 7: quota check — current = 10, limit = 10. → 403 plan_limit_reached with

"details": [ { "field": "bio_pages", "issue": "limit_reached",
               "limit": 10, "current": 10, "plan": "pro", "kind": "count" } ]

Note the Editor is blocked but cannot upgrade (row 30 is Owner-only); the UI therefore offers "ask your workspace owner to upgrade" and names the Owner, rather than a checkout button. If the workspace also holds four archived pages, they do not appear in current, because archived resources do not consume a cap (Section 22.2.5).

3.7.6 Sole Owner tries to leave the workspace #

Setup. Owner is the only member. Action workspace.leave. Resolution. Step 5: matrix row 11 gives C9 for Owner, and the last-owner condition fails. → 409 last_owner_required. The response details names the two valid paths: transfer ownership to an existing member, or delete the workspace. Because there is no other member, the UI surfaces the invite flow first.

3.7.7 A past-due workspace's QR code is scanned, and its owner tries to edit it #

Setup. Business workspace, day 3 of past_due, and separately the same workspace on day 10. A printed QR code is scanned by a customer; separately, the Owner opens the QR editor and changes the destination.

Scan resolution, on any day. The redirect path does not call authorize() (Section 3.5.1). The edge resolver reads the cached redirect payload, sees the billing state flag, applies LinkHub branding to the interstitial-free response, and issues a 302 to the active destination. Resolution is unaffected by billing state, permanently.

Edit resolution on day 3. Steps 1–8 pass. Step 8a reads entitlements.writes_blocked, which Section 22.7.5 sets to false for dunning days 0–7. → Allow. The destination change succeeds and is audited. The Owner sees a payment banner, not a block: a genuine card expiry is usually fixed within a week, and taking write access away on day one punishes the common case.

Edit resolution on day 10. Steps 1–8 pass identically. Step 8a: entitlements.writes_blocked is now true. → 403 billing_write_blocked, with details[0].grace_ends_at and details[0].invoice_url so the client can render the exact remedy. The Owner sees a "pay the outstanding invoice to resume editing" path; the Admin in the same workspace sees the same block with a message naming the Owner, because Admins cannot resolve billing themselves.

3.7.8 Scoped Editor creates a link, then is removed from the workspace #

Setup. Business workspace. Freelancer is an Editor scoped to 2 bio pages. They create a new short link.

On create. Steps 1–8 pass (row 64, C1, quota available). In the same transaction the link row is inserted and a grant is auto-created for the freelancer's membership with source = auto_on_create (Section 3.4.4, rule 2). The freelancer can now edit that link.

On removal. Priya removes the membership (row 17). In one transaction the membership is soft-deleted, all its grants including the auto-created one are deleted, and the cached entitlement and capability entries for that user and workspace are evicted. The link is untouched: it keeps resolving, it remains in the workspace's link list, created_by still records the freelancer for audit purposes, and any Admin or unscoped Editor can edit it. The freelancer's next request to that link fails at step 3 with 404 not_found, because they are no longer a member at all.

3.7.9 API key attempts a member invitation #

Setup. A Business workspace API key with scopes links:read, links:write. A script calls the member-invite endpoint on the workspace the key is bound to. Resolution. Step 2: the workspace binding matches, so evaluation continues; the action's domain is members, which is denied to API-key actors unconditionally. → 403 action_not_available_to_api_key. Even a key created by the Owner cannot invite members; there is no scope that grants it.

3.7.10 API key from workspace A addresses workspace B #

Setup. Two Business workspaces under different billing accounts. A key created in workspace A calls GET /v1/links with workspace B's identifier in the path. Resolution. Step 2, before the scope gate and before any capability evaluation: actor.api_key.workspace_id != workspaceId. → 404 not_found. The response is byte-identical to the response for a workspace identifier that has never existed, and identical to what a scoped member receives at step 6. The key's scopes are never consulted, so the caller learns nothing about workspace B — not that it exists, not who owns it, and not whether the key would have had the right scope. This is the tenancy boundary that Section 23.3 tests against.

3.7.11 Unverified user tries to publish a bio page #

Setup. New signup, Owner of their personal workspace, email not yet verified. Action bio_page.publish. Resolution. Steps 1–3 pass. Step 4: the action is marked requires_verified_email (matrix row 50, C4). → 403 email_verification_required. Every other builder action succeeds, so the user can build the entire page before verifying; only the moment of making something publicly reachable is gated. The builder shows a persistent "verify to publish" banner with a resend control from first load.

3.7.12 A user on Pro tries to create a second workspace #

Setup. Verified user, Owner of one workspace on Pro. Action workspace.create. There is no workspace context: the user is creating one. Resolution. The action is marked scope == ACCOUNT, so step 2a routes it to authorizeAccountScoped() before any membership or workspace lookup is attempted. The email check passes. The workspace cap for Pro is 1 (Section 22.1.2) and the billing account already owns 1. → 403 plan_limit_reached with details[0] = { field: "workspaces", issue: "limit_reached", limit: 1, current: 1, plan: "pro", kind: "count" }. On Business the same call returns Allow until the tenth workspace exists. Without this branch the action could not be evaluated at all, because steps 3 and 7 both require a workspace that does not yet exist.

4. Technology Stack & Architecture #

4.1 Stack #

4.1.1 Version policy #

These version lines are a known-good floor, not a lockfile. At build time, install the current stable release of each dependency (npm install <pkg>@latest, or your ecosystem's equivalent), confirm the major line still matches, and let the lockfile record the exact resolved versions.

Version lines are stated here and nowhere else in this document. Every other section refers to dependencies by name only.

4.1.2 Core dependencies #

4.1.3 Supporting libraries #

These are installed at their current stable release at build time and recorded in the lockfile; they carry no version floor because none of them constrains the architecture.

4.2 The four deployables #

All four are built as OCI container images from the same monorepo, share packages/core and packages/db, and are versioned together. They are deployed independently: a change touching only the builder does not redeploy the redirect resolver.

4.3 Monorepo layout #

pnpm workspaces for dependency management, Turborepo for task orchestration and caching.

linkhub/
├── apps/
│   ├── web/                    Next.js — marketing, dashboard, public bio pages
│   │   ├── app/                App Router route groups
│   │   │   ├── (marketing)/    Static: home, pricing, legal, changelog, docs index
│   │   │   ├── (auth)/         Sign in, sign up, verify, reset, 2FA challenge
│   │   │   ├── (dashboard)/    Authenticated workspace UI (see Sections 8–20)
│   │   │   ├── (public)/       Server-rendered bio pages, host- and handle-routed
│   │   │   └── api/            Route handlers: session-authenticated dashboard endpoints
│   │   ├── components/         App-specific components (shared ones live in packages/ui)
│   │   ├── lib/                App-local adapters: auth wiring, query client, formatters
│   │   └── public/             Static assets
│   ├── edge/                   Hono — redirect resolver
│   │   ├── src/routes/         Resolve, health, ACME HTTP-01 challenge
│   │   ├── src/resolve/        Cache read, DB fallback, targeting, fallback chain
│   │   └── src/capture/        Fire-and-forget event emission to the Redis Stream
│   ├── api/                    Hono — public REST API /v1
│   │   ├── src/routes/v1/      Resource routers: links, qr, pages, analytics
│   │   ├── src/middleware/     API-key auth, scope check, rate limit, idempotency
│   │   └── src/openapi/        OpenAPI document generated from shared Zod schemas
│   └── worker/                 BullMQ consumers and schedulers
│       ├── src/queues/         One module per queue (Section 4.8)
│       ├── src/schedulers/     Repeatable job registration
│       └── src/processors/     Job handlers, one file per job type
├── packages/
│   ├── db/                     Drizzle schema, migrations, seeds, typed client factory
│   │   ├── src/schema/         One file per domain: workspaces, links, qr, analytics…
│   │   ├── migrations/         Generated SQL, expand/contract, checked in
│   │   └── src/seed/           Deterministic development and test fixtures
│   ├── core/                   Domain logic — the only place business rules live
│   │   ├── src/schemas/        Zod schemas; single source of truth (Section 5.4)
│   │   ├── src/entitlements/   Plan catalogue, quota evaluation (Section 22.2)
│   │   ├── src/permissions/    The matrix and authorize() from Sections 3.3 and 3.5
│   │   ├── src/resolve/        Pure redirect-resolution and fallback-chain logic
│   │   ├── src/qr/             Encoding, styling, scannability validation
│   │   ├── src/analytics/      Dimension extraction, visitor hashing, bucketing
│   │   └── src/errors/         AppError hierarchy and code registry (Section 5.5)
│   ├── ui/                     Shared React components and design tokens
│   │   ├── src/primitives/     Button, Input, Dialog, Menu — accessible by construction
│   │   ├── src/blocks/         Bio page block renderers, shared by builder and public path
│   │   └── src/tokens/         Design tokens compiled to CSS custom properties
│   └── config/                 Shared ESLint, TypeScript, Tailwind and Vitest configuration
├── infra/                      Container definitions, compose files, deployment manifests
├── docs/                       ADRs, runbooks, the OpenAPI snapshot
├── turbo.json
├── pnpm-workspace.yaml
├── DECISIONS.md
└── README.md

4.3.1 Dependency rules between packages #

These are enforced by an ESLint import-boundary rule and fail the build when violated.

4.4 System architecture #

4.4.1 Short-link and QR redirect #

Both surfaces are the same resolver on the same path shape. A QR code's public URL is the bare https://{host}/{slug} — there is no /q/ path prefix anywhere in the product. Two consequences follow, and both are the reason for the decision: a printed symbol encodes fewer characters, so it carries fewer modules and stays scannable at a smaller physical size; and QR slugs and short-link slugs share exactly one namespace per host, which is what allows the permanent reservation table to protect a QR slug and the identical short-link slug together (Section 14).

  Visitor
    │  GET https://go.acme.com/spring-sale
    ▼
┌─────────┐   TLS termination, no caching of redirects
│   CDN   │   (Cache-Control: private, no-store is honoured; see Section 12.1.5)
└────┬────┘
     ▼
┌──────────────────────── apps/edge ────────────────────────┐
│ 1. Parse host + slug                                       │
│ 2. Pipelined Redis read:                                   │
│      MGET dom:host:{host}                                  │
│           rd:{host}:{slug}                                 │
│           rd:miss:{host}:{slug}                            │
│ 3. HIT  → go to 6                                          │
│    MISS-CACHED → serve branded fallback, stop              │
│    MISS → PostgreSQL read (single indexed query),          │
│           write-through rd: key (TTL 3600s)                │
│           or set rd:miss (TTL 60s) and serve fallback      │
│ 4. Evaluate state: active / scheduled / expired / paused / │
│    archived / downgrade-locked  (Sections 15.3, 22.5)      │
│ 5. Evaluate targeting + experiment assignment              │
│    (Sections 15.5, 16.2) — pure functions, no I/O          │
│ 6. Derive visitor_hash + country_code/region_code          │
│    in memory                                               │
│ 7. XADD clicks:raw   ← fire-and-forget, never awaited      │
│ 8. Respond 302 Location: <destination>                     │
│         Cache-Control: private, no-store                   │
└────────────────────────────────────────────────────────────┘
     │                                   │
     ▼ 302                               ▼ (async)
  Visitor                          Redis Stream clicks:raw

dom:host:{host} is the only host-routing key in the system; nothing reads any other host key, and Section 4.7.3 is the complete list of keys that exist. Steps 2 and 3 are the only I/O on the hot path, and step 3's PostgreSQL branch is taken on cache miss only. Step 7 is dispatched without await; a failure increments a counter and is otherwise invisible to the visitor.

When the resolved resource is QR-backed, step 8 becomes the four-rung fallback chain in Section 14.8.2 rather than an unconditional 302: rungs 1 and 2 are 302 with Cache-Control: private, no-store, rungs 3 and 4 are 200 with Cache-Control: public, max-age=60. No rung is ever 404, 410 or 5xx.

4.4.2 Bio page render #

  Visitor
    │  GET https://acme.link/  (or handle-routed host)
    ▼
┌─────────┐  Cached HTML, stale-while-revalidate.
│   CDN   │  Purged by tag on publish/unpublish/theme change (Section 4.7)
└────┬────┘
     │ MISS
     ▼
┌────────────────── apps/web  (public) ─────────────────────┐
│ 1. Resolve host → workspace + page (Redis dom:host:{host},│
│    then page:{host}:{handle}; PostgreSQL on miss)          │
│ 2. Load published page snapshot: blocks, theme, SEO        │
│ 3. Server-render React → HTML                              │
│      • critical CSS inlined  (≤ 14 KB)                     │
│      • zero blocking JS; links are plain <a href>          │
│      • images AVIF/WebP, LCP image eager + fetchpriority    │
│      • embeds render as click-to-load facades              │
│ 4. Derive visitor_hash + country_code/region_code in memory│
│ 5. XADD clicks:raw  (event_type = page_view) — not awaited │
│ 6. Respond 200 with cache headers + strict nonce CSP       │
└────────────────────────────────────────────────────────────┘
     │
     ▼ (progressive enhancement only, deferred)
  Analytics beacon, embed loaders, share sheet

The Content Security Policy applied at step 6 is Section 23.5's, in full; no other section defines a rival policy.

4.4.3 Authenticated dashboard mutation #

  Dashboard (React, TanStack Query)
    │  PATCH /api/links/{id}   { destination_url }
    ▼
┌───────────────── apps/web  (route handler) ───────────────┐
│ 1. Session cookie → session lookup (Redis sess:, then DB)  │
│ 2. Zod parse of the request body  (Section 5.4)            │
│ 3. authorize(actor, workspace, "link.update_destination")  │
│      → Section 3.5; any Deny returns the canonical         │
│        error envelope with its status and code             │
│ 4. Destination safety: scheme allow-list, DNS/SSRF check,  │
│    Safe Browsing lookup  (Section 23.6)                    │
│ 5. Transaction:                                            │
│      UPDATE links …                                        │
│      INSERT audit_log_entries (before/after)               │
│ 6. Post-commit, in order:                                  │
│      a. write-through  rd:{host}:{slug}                    │
│      b. DEL            rd:miss:{host}:{slug}               │
│      c. enqueue cache-refresh job (belt and braces)        │
│      d. enqueue webhook-dispatch job if configured         │
│ 7. Respond 200 { "data": { … }, "meta": { } }              │
└────────────────────────────────────────────────────────────┘
    │
    ▼ TanStack Query invalidates the link list and detail keys

The cache write in step 6a happens after the transaction commits, never inside it. A crash between commit and cache write leaves a stale cache entry for at most the 3600-second TTL, and the enqueued refresh job in 6c closes that window in seconds.

Where step 5 writes an audit entry for a QR destination change, that entry is written with retention_expires_at = NULL so that no purge run can ever remove it. Sections 6 and 8 carry both the column behaviour and the matching purge predicate.

4.4.4 Analytics event, capture to rollup #

apps/edge / apps/web(public)
    │ XADD clicks:raw  * { event_type, workspace_id, resource_type,
    │                      resource_id, occurred_at, visitor_hash,
    │                      country_code, region_code, device_type,
    │                      os_family, browser_family, user_agent_family,
    │                      ua_hash, referrer_host, utm_*, variant_id,
    │                      fallback_stage, is_bot }
    ▼
┌──────────── Redis Stream: clicks:raw ────────────┐
│ consumer group "ingest", MAXLEN ~ 1,000,000      │
│ database configured noeviction (Section 27.4.3)  │
└───────────────────────┬──────────────────────────┘
                        │ XREADGROUP  (batch ≤ 1000 or 2s window)
                        ▼
┌──────────── apps/worker — analytics-ingest ──────────────┐
│ 1. Validate + normalise each event (Zod)                  │
│ 2. Parse the user agent IN MEMORY → user_agent_family     │
│    + ua_hash; discard the raw string (Section 23.12)      │
│ 3. Bot classification → is_bot flag (never dropped)       │
│ 4. Bulk INSERT into click_events, carrying                │
│      stream_message_id + ingest_batch_id                  │
│      (daily range partition, PostgreSQL declarative)      │
│ 5. Aggregate the same batch in memory by                  │
│      (workspace, resource, bucket_start, dimension, value)│
│ 6. UPSERT analytics_rollup_hourly (increment counters)    │
│ 7. XACK the batch                                         │
└───────────────────────┬──────────────────────────────────┘
                        │
        ┌───────────────┴────────────────┐
        ▼                                ▼
┌── hourly → daily roll ──┐   ┌── nightly reconciliation ──┐
│ analytics-rollup queue  │   │ recompute last 3 days from │
│ folds hourly into daily │   │ raw click_events, correct  │
│ at each hour boundary   │   │ any drift, then purge per  │
└─────────────┬───────────┘   │ the retention table        │
              ▼               └────────────────────────────┘
      analytics_rollup_daily
              │
              ▼
   Dashboards + exports (Section 18) read rollups;
   drill-downs read raw click_events within plan retention

Column names shown here are the ones Section 6 defines; Section 6 is the sole schema authority and this diagram states no types, widths or constraints. The raw user-agent string is never a field on the stream payload's persisted form and is never written to any table.

4.5 Data flow: read and write paths #

PostgreSQL is the system of record. Redis is a cache and a buffer, never a source of truth. Every byte in Redis is either derivable from PostgreSQL or is in-flight data that has an explicit, documented loss window.

Write ordering is fixed everywhere: validate → authorize → transaction (data + audit) → commit → cache write-through → enqueue side effects. Nothing enqueues a job or writes a cache entry inside a database transaction, because a rolled-back transaction with a dispatched job produces a job referring to a row that does not exist.

4.6 Why the redirect path is a separate service #

The redirect budget is p50 < 20 ms, p95 < 50 ms, p99 < 120 ms of server-side processing (Section 11.8.2). That budget is the entire justification.

1. The budget is a whole-process property, not a route property. Garbage-collection pauses, module-loading cost, connection-pool contention and middleware stacks are shared across every route in a process. A redirect served by the same process as a React server render inherits that process's tail latency. Separating the resolver removes the largest contributors to p99 from the hot path.
2. The dependency graph must be small enough to audit. apps/edge imports three modules from packages/core (Section 4.3.1, rule 7) and no ORM on the hot path. A smaller graph means faster cold start, a smaller attack surface, and a deployable whose behaviour under load can actually be reasoned about.
3. Scaling signals differ by an order of magnitude. Redirect traffic is bursty and anonymous; dashboard traffic is steady and session-bound. Autoscaling one on the other's signal either over-provisions the dashboard or under-provisions the resolver during a burst.
4. Deployment risk differs. apps/edge deploys blue/green with health-gated cutover because a failed rollout means printed QR codes stop scanning. The dashboard deploys rolling, because a failed rollout means a retry. Coupling them forces the safest strategy on everything, which slows every release.
5. Failure isolation is the product promise. Section 2.3.7 requires that a total dashboard outage leaves every link and QR code resolving. That is only structurally true if the resolver shares no process, no memory and no request queue with the dashboard.

4.7 Caching strategy, the key set and the invalidation matrix #

This subsection is the single authority for both the Redis key namespace and cache invalidation. Where another section describes a cache effect, it references this subsection rather than restating a matrix of its own.

4.7.1 Cache layers #

Redirect payloads are never cached in the CDN or the browser. Cache-Control: private, no-store on every redirect response is what makes destination editing safe.

4.7.2 The canonical Redis key set #

These twelve keys are the complete set. A key that is not on this list does not exist, and adding one requires an ADR under Section 5.10.2 rule 6. Placeholders: {host} is a lower-cased punycode hostname, {slug} a link or QR slug, {handle} a bio page handle, {id}/{ws}/{user} are UUIDs, {date} is yyyymmdd, {token_hash} a SHA-256 digest, and {scope}/{key} identify a rate-limit bucket.

4.7.3 The invalidation matrix #

Every mutation below lists exactly the keys it must touch. {h} is the resource's host, {s} its slug, {w} the workspace id. This table is authoritative for the whole document.

Three rules govern the whole matrix:

1. Write-through, then delete the negative. Any mutation that makes a host/slug resolvable must write the positive key and delete the negative key, in that order. Reversing the order leaves a window in which the negative key is authoritative.
2. A QR key is never deleted and never negatively cached. Archive, delete, suspension and workspace deletion all rewrite the payload with the appropriate fallback_stage and rung. This is the cache-layer expression of the permanence rule in Section 14.8.
3. Purges are idempotent and bounded. Every purge job retries five times with exponential backoff and emits a metric on failure. Because payloads are revision-scoped and TTLs are short, an exhausted purge produces bounded staleness, never incorrect content.

4.8 Queue topology #

All queues are BullMQ queues backed by the same Redis instance, namespaced lh:. Queue names are kebab-case. Every job carries job_id set to its idempotency key, so a duplicate enqueue is a no-op rather than a duplicate execution.

Cross-cutting rules:

1. Every job handler is written to be safely re-runnable. Where a handler cannot be naturally idempotent, it takes an advisory lock on its idempotency key for the duration of the job.
2. Job payloads carry identifiers, never copies of mutable state. A handler re-reads from PostgreSQL. This prevents a delayed job from applying a stale value.
3. No job payload contains personal data beyond identifiers. Email addresses in email-send are resolved at execution time from the database, so a queued job is not a copy of a mailing list.
4. Queues have a per-queue concurrency limit rather than a global one, so one slow external dependency cannot consume the worker pool.
5. Dead-letter contents are retained 30 days, then purged.
6. Every worker asserts at startup that the Redis database backing clicks:raw is configured noeviction, and refuses to start otherwise. An eviction policy that can discard stream entries would silently lose analytics events, which is exactly the failure the bounded-loss model in Section 4.5 is written to exclude.

4.9 Failure modes and degradation ladder #

For each dependency: what breaks, what the user sees, and the invariant that must still hold.

4.9.1 Redis unavailable #

4.9.2 PostgreSQL unavailable #

4.9.3 Queue backlog growing #

4.9.4 Geo database unavailable or stale #

4.9.5 Object storage unavailable #

4.9.6 Stripe unavailable #

4.9.7 Degradation ladder summary #

Ordered from most to least degraded. The system always occupies the highest level it can.

At no level does a QR scan produce a 404, a 410, or a connection error that LinkHub can control.

4.10 Scale assumptions and capacity targets #

All arithmetic below is shown so it can be re-run with different inputs. These are the year-one planning targets that size the reference deployment in Section 27.2.

4.10.1 Planning inputs #

4.10.2 Redirect throughput #

290,000,000 events/month  (250M redirects + 40M page views)
÷ 2,592,000 seconds/month (30 days)
= 112 events/second average

Peak factor 10× (a single viral post or retail promotion)
= 1,120 events/second peak

Design headroom 4.5×
= 5,040 events/second  →  target 5,000 rps sustained

5,000 rps is the k6 load-test target in Section 26.7, chosen to match this arithmetic rather than being an arbitrary round number.

4.10.3 Edge instance sizing #

Measured CPU cost per cache-hit redirect     ≈ 0.8 ms of one vCPU
Theoretical per-vCPU capacity                 = 1000 / 0.8 = 1,250 rps
Target steady-state utilisation               = 60%
Effective per-vCPU capacity                   = 750 rps

5,000 rps ÷ 750 = 6.7 vCPU
Round up for N+1 availability across zones:
  4 instances × 2 vCPU = 8 vCPU  (each instance carries 1,250 rps at 5,000 rps total)

Autoscaling triggers on requests per second per instance with a target of 900, scaling out at 1,100 and in at 500, with a 5-minute cool-down on scale-in to absorb bursty traffic.

4.10.4 PostgreSQL storage for raw events #

Column names below are Section 6's; this is a byte-width estimate, not a schema definition.

Row width for click_events:
  tuple header + alignment                  24 B
  id (uuid, UUIDv7)                         16 B
  workspace_id, resource_id (uuid)          32 B
  occurred_at (timestamptz)                  8 B
  visitor_hash (16 B binary)                16 B
  ua_hash (16 B binary)                     16 B
  user_agent_family (avg)                   20 B
  country_code(2), region_code(≤6)          12 B
  device_type, os_family, browser_family    24 B
  referrer_host (avg)                       28 B
  utm_source/medium/campaign (avg)          48 B
  variant_id, resource_type, is_bot,
    fallback_stage, geo_resolved            20 B
  ─────────────────────────────────
  ≈ 264 B payload
  + 2 indexes at ~60 B each                120 B
  ─────────────────────────────────
  ≈ 384 B per event, round to 385 B

290,000,000 events/month × 385 B ≈ 111.7 GB per month of raw events

The raw user-agent string is not in this arithmetic because it is never stored (Section 23.12); only the 16-byte ua_hash and the short family label are, and both are included above.

Retention is per plan, so steady-state storage is a weighted sum:

Free      25% × 111.7 GB × 1 month   =  27.9 GB
Pro       60% × 111.7 GB × 3 months  = 201.1 GB
Business  15% × 111.7 GB × 24 months = 402.1 GB
──────────────────────────────────────────────
Steady-state raw event storage       ≈ 631 GB
Provision with 2× headroom           ≈ 1.3 TB

Daily partitions mean the purge worker drops whole partitions rather than issuing DELETE statements, so purge cost is independent of row count. At 24-month Business retention the table carries roughly 730 live partitions, which is well within PostgreSQL's practical planning limits when queries are partition-pruned by the mandatory date filter in Section 18.14.1.

4.10.5 Rollup storage #

Assumptions:
  Resources with at least one event on a given day        ≈ 250,000
  Median distinct dimension values per active resource/day ≈ 26
    (country_code 8, region_code 6, device_type 3, os_family 3,
     browser_family 3, referrer_host 2, utm_* 3, variant 1, is_bot 1
     — median case)
  Heavy tail: 2,000 resources × 180 rows                   =  360,000

Daily rollup rows/day  = (250,000 × 26) + 360,000 ≈ 6.86 M
Hourly rollup rows/day ≈ 2.4 × daily                ≈ 16.5 M
                          (more buckets, far fewer distinct values each)

Row width ≈ 120 B including indexes.

Hourly retained per plan, worst case 365 days:
  16.5 M × 365 × 120 B                         ≈ 723 GB
Daily retained per plan (Business indefinite):
  6.86 M/day × 365 × 120 B                     ≈ 300 GB/year

Hourly rollup retention follows the plan table in Section 6 — 30 days Free, 365 days Pro, indefinite Business — so the 365-day figure above is the Pro worst case and Business is sized from the same per-day rate. Daily rollups follow the plan retention table. Dashboards beyond 90 days read daily granularity regardless of what hourly data survives.

4.10.6 Redis working set #

Hot redirect payloads: 5% of 5,000,000 resolvable resources
  = 250,000 keys × 600 B payload            = 150 MB
Host routing: 30,000 hosts × 400 B          =  12 MB
Page render snapshots: 60,000 × 6 KB
  (10% hot: 6,000 × 6 KB)                   =  36 MB
Sessions: 200,000 active × 250 B            =  50 MB
Entitlements: 50,000 × 300 B                =  15 MB
Capability sets: 60,000 × 200 B             =  12 MB
Rate-limit counters                         =  20 MB
clicks:raw stream, MAXLEN 1,000,000 × 260 B = 260 MB
─────────────────────────────────────────────────────
Working set                                 ≈ 555 MB
Provision                                   = 2 GB  (≈ 3.6× headroom)

The stream database is provisioned separately from the cache database precisely because it is configured noeviction: a cache database may evict under pressure, and the stream database must never do so.

4.10.7 Connection budget #

PostgreSQL connections, via a transaction-mode pooler:
  apps/web    6 instances × pool 10  =  60
  apps/edge   4 instances × pool 5   =  20   (cache-miss path only)
  apps/api    3 instances × pool 10  =  30
  apps/worker 4 instances × pool 15  =  60
  migrations / operator access       =  10
  ───────────────────────────────────────
  Peak pooled client connections     = 180
  Pooler → PostgreSQL server connections ≈ 40 (transaction mode multiplexing)

The edge pool is deliberately small: on the hot path it does no database work at all, and a large pool would only mask a cache failure by amplifying database load during precisely the incident where the database is least able to absorb it.

4.10.8 Capacity targets summary #

5. Conventions & Standards #

5.1 TypeScript configuration #

A single base configuration lives in packages/config and every package extends it. Packages may narrow settings; they may never loosen them.

Additional rules:

1. any is banned by lint rule. Use unknown and narrow. The single permitted exception is inside a type-guard function body, which must carry a comment explaining the guard.
2. Non-null assertion (!) is banned by lint rule. Narrow explicitly or throw a domain error.
3. Type assertions (as) require a comment stating why the compiler cannot prove the claim. as unknown as T is banned outright.
4. Exhaustiveness is enforced with an assertNever(value: never): never helper at the end of every discriminated-union switch.
5. Enums are not used. Use as const objects with a derived union type, which erase cleanly and are structurally comparable across package boundaries.
6. Public exports from packages/* must have explicit return types. Inference is fine inside a module, never across one.

5.2 Naming conventions #

The boundary between camelCase in TypeScript and snake_case in JSON is crossed in exactly one place: a serialisation layer in packages/core that maps domain objects to API representations. No hand-written snake_case object literals appear in application code, and no camelCase field ever reaches a public API response.

5.3 Repository conventions #

5.4 Validation #

One Zod schema per concept is the single source of truth. It is defined once in packages/core/src/schemas/, consumed by the client form, the server route handler, the public API and the OpenAPI document. There is no second definition anywhere, and no hand-written TypeScript interface duplicating a validated shape.

5.4.1 The pattern #

// packages/core/src/schemas/link.ts

export const SLUG_PATTERN = /^[a-z0-9-]{1,64}$/

export const CreateLinkInputSchema = z.object({
  domain_id:       z.uuid(),
  slug:            z.string().regex(SLUG_PATTERN).optional(),   // absent → auto-generate
  destination_url: z.url().max(2048),
  title:           z.string().min(1).max(120).optional(),
  tags:            z.array(z.string().min(1).max(32)).max(20).default([]),
  publish:         z.boolean().default(true),                   // false → status 'draft'
})

// The type is DERIVED from the schema, never written by hand.
export type CreateLinkInput = z.infer<typeof CreateLinkInputSchema>

// Output schemas are separate: what we accept is not what we return.
// The status union is exactly the state machine Section 12.1.2 defines and
// Section 6 constrains; a POST with publish:false lands on 'draft'.
export const LinkSchema = z.object({
  id:              z.uuid(),
  workspace_id:    z.uuid(),
  domain_id:       z.uuid(),
  slug:            z.string(),
  short_url:       z.url(),
  destination_url: z.url(),
  title:           z.string().nullable(),
  tags:            z.array(z.string()),
  status:          z.enum(['draft', 'active', 'paused', 'scheduled', 'expired', 'archived']),
  created_at:      z.iso.datetime(),
  updated_at:      z.iso.datetime(),
})
export type Link = z.infer<typeof LinkSchema>

5.4.2 Rules #

5.5 Error handling #

5.5.1 The error class hierarchy #

Error
└── AppError                      abstract; carries { code, httpStatus, details?, cause? }
    ├── ValidationError           400  — a request failed schema parsing
    ├── AuthenticationError       401  — no valid actor, or an unmet assurance
    │     └── AssuranceError            totp_required
    ├── AuthorizationError        403  — actor known, action refused
    │     ├── RoleError                 insufficient_role
    │     ├── EntitlementError          plan_feature_unavailable | plan_limit_reached
    │     ├── ScopeError                insufficient_scope
    │     ├── BillingError              billing_write_blocked
    │     └── SessionAssuranceError     email_verification_required |
    │                                   reauthentication_required
    ├── NotFoundError             404  — resource, workspace, or intentionally concealed
    ├── ConflictError             409  — uniqueness, state, last-owner, locked resource
    ├── GoneError                 410  — soft-deleted or permanently removed
    ├── PayloadTooLargeError      413  — request_too_large
    ├── UnsupportedMediaTypeError 415  — unsupported_media_type
    ├── SemanticError             422  — well-formed but not actionable
    ├── RateLimitError            429  — carries retry_after_seconds
    └── InternalError             500  — unexpected; message is never surfaced to clients

5.5.2 Mapping rules #

5.5.3 Error payload shape #

The envelope is defined in Section 21.3 and is used identically by apps/web route handlers, apps/api and apps/edge. details is always an array, never a bare object, and every entry carries at least field and issue using the vocabulary in Section 21.3.2. Validation failures produce one entry per failed field; entitlement failures produce one entry describing the blocked capability.

{
  "error": {
    "code": "plan_limit_reached",
    "message": "This workspace has reached its bio page limit.",
    "details": [
      { "field": "bio_pages", "issue": "limit_reached",
        "limit": 10, "current": 10, "plan": "pro", "kind": "count" }
    ],
    "request_id": "req_01J8ZK3M9QW2X4Y6"
  }
}

kind is count for a numeric cap and period for a per-period allowance. The sibling code plan_feature_unavailable uses the same array shape with "issue": "feature_unavailable" and no limit, current or kind. Keeping one shape for every code means a client renders details[0] the same way regardless of which refusal it received, which is the entire reason for the constraint.

5.6 Logging #

5.6.1 Format and required fields #

All logs are single-line JSON to stdout. The container platform ships them; the application never writes log files.

5.6.2 Levels #

Per-request info lines are emitted once per request on completion, not per internal step. The redirect path logs at info sampled at 1% plus 100% of non-2xx/3xx outcomes; logging every redirect at 5,000 rps is neither useful nor affordable.

5.6.3 Redaction #

Redaction is configured once as a set of paths on the logger instance, so it applies to every call site mechanically. A field cannot be logged by accident.

A test in the logging package asserts that a log call containing each redacted key emits the redaction marker rather than the value. Adding a new sensitive field means adding a redaction path and a test case in the same commit.

6. Data Model & Database Schema #

This section is the canonical schema for the entire product. Every other section refers to the table and column names defined here. Where a behaviour is owned elsewhere (entitlement evaluation in Section 22, ingestion mechanics in Section 17, redirect resolution in Section 12), this section defines only the storage shape and its constraints.

6.1 Entity-relationship overview #

The model has one hard tenancy boundary — the workspace — and five content domains hanging off it (bio pages, links, QR codes, experiments, leads), plus four supporting domains (identity, billing, delivery/domains, analytics).

                       ┌───────────────┐
                       │     users     │
                       └───────┬───────┘
      user_identities ─────────┤
      sessions ────────────────┤
      totp_secrets ────────────┤
      recovery_codes ──────────┤
      *_tokens (verify/reset/  │
       magic-link/email-change)┤
                               │  (membership is the ONLY path from a user to data)
                       ┌───────▼─────────────┐
                       │ workspace_members   │──── resource_grants (Business, per-resource scoping)
                       └───────┬─────────────┘
                               │
   ┌───────────────────────────▼─────────────────────────────────────────┐
   │                            workspaces                               │
   │  (unit of tenancy, branding, billing, retention, audit)             │
   └──┬─────────┬─────────┬──────────┬─────────┬─────────┬───────┬───────┘
      │         │         │          │         │         │       │
      │         │         │          │         │         │       └── audit_log_entries (append-only)
      │         │         │          │         │         └────────── api_keys → api_key_usage
      │         │         │          │         └──────────────────── integrations → integration_credentials
      │         │         │          │                               integrations → webhook_deliveries → webhook_dead_letters
      │         │         │          └────────────────────────────── subscriptions → subscription_items
      │         │         │                                          subscriptions → invoices;  plans;  entitlement_overrides
      │         │         └───────────────────────────────────────── custom_domains → domain_verification_attempts
      │         │                                                                   → tls_certificates
      │         │
      │         └── bio_pages ── bio_page_versions
      │               │  └────── blocks ── block_versions
      │               │  └────── themes ── fonts
      │               │  └────── leads ── lead_sync_attempts ── lead_sync_targets
      │               └──────── uploaded_assets
      │
      ├── links ── link_versions
      │      ├──── link_destinations   (split testing: N weighted URLs)
      │      ├──── link_rules          (geo/device/time/language/referrer targeting)
      │      └──── utm_presets
      │
      ├── qr_codes ── qr_code_versions ── qr_render_artifacts
      │        └───── qr_slug_reservations   ◀── NEVER deleted, NEVER cascaded (see 6.10)
      │
      └── experiments ── experiment_variants
                    ├── experiment_assignments_rollup
                    └── experiment_results

  ANALYTICS (write-heavy, partitioned)
   click_events (PARTITION BY RANGE occurred_at, daily) ─┐
   page_view_events (PARTITION BY RANGE occurred_at)    ─┼→ analytics_unique_visitor_days
                                                         │       (PARTITION BY RANGE event_date)
                                                         ├→ analytics_rollup_hourly
                                                         ├→ analytics_rollup_daily
                                                         └→ analytics_rollup_only_events (late-event idempotency)
   bot_signatures · ua_parse_corpus (no visitor key, no workspace key)
   analytics_share_links (read-only shared dashboards)

  BILLING / ENTITLEMENT SUPPORT
   billing_accounts ── subscriptions ── invoices
   workspace_resource_counters (authoritative cap counters)

  SAFETY / COMPLIANCE / PLATFORM (workspace-scoped or global)
   consent_records · data_export_requests · data_deletion_requests
   safe_browsing_checks · abuse_reports · reserved_slugs
   idempotency_keys · rate_limit_violations · feature_flags · jobs_dead_letter · payment_events

6.1.1 Entity catalogue #

6.1.2 Structural decisions worth stating once #

1. System hostnames are rows in custom_domains with workspace_id IS NULL and is_system = true (linkhub.app, go.linkhub.app, lnkhb.co). Consequence: links.domain_id, qr_codes.domain_id and bio_pages.domain_id are all NOT NULL, and slug uniqueness is a plain two-column unique index rather than a COALESCE expression. This removes an entire class of null-handling bugs from the hottest lookup in the system.
2. Membership is the only path from a user to data. No content table has a user_id owner column that grants access; created_by_user_id is provenance only. Section 8.10 defines the enforcement pattern that makes this structural.
3. Versions are snapshots, not diffs. bio_page_versions, link_versions, qr_code_versions and block_versions each store a complete jsonb snapshot. Restoring a version never needs to replay history.
4. The permanent-QR rule is expressed in the schema, not only in code. qr_slug_reservations has no deleted_at column, no ON DELETE CASCADE pointing at it, and a BEFORE DELETE trigger that raises an exception. See 6.10.
5. Enumerations are text + CHECK, never PostgreSQL ENUM types. Adding a value to a CHECK constraint is a single non-blocking ALTER ... ADD CONSTRAINT ... NOT VALID + VALIDATE, which fits the expand/contract migration rule in 6.6; altering a native enum type does not compose with it.
6. The raw user-agent string is never persisted. No table in this schema has a column holding a raw User-Agent header. The worker parses the string in memory into user_agent_family (a short, non-identifying label such as Chrome on Android) and ua_hash (a daily-salted 16-byte digest used only to cluster bot signatures), and then discards it. Parser maintenance is served by ua_parse_corpus (6.3.71), which holds distinct strings with no visitor key, no workspace key and no timestamp finer than a day, and is therefore not linkable to a person.
7. QR slugs and short-link slugs share one namespace per host. A public QR URL is https://{host}/{slug} — there is no /q/ prefix — so qr_slug_reservations protects both surfaces with one row. Creating a short link whose (domain_id, slug) matches an existing reservation is refused, and creating a QR reservation whose (domain_id, slug) matches a live short link is refused. Both refusals return 409 qr_slug_reserved. The enforcement is specified in 6.3.39.

6.2 Conventions recap #

6.3 Full schema #

Column tables below omit id, created_at and updated_at per 6.2. "Null" is Y when the column is nullable.

This subsection is the whole schema. Every table the product has is defined here and nowhere else. If another section needs a column name, a type or a constraint, it cites the subsection number below rather than restating it, because two statements of one column is how they come to disagree.

The five tables that deviate from the standard columns, each for a stated reason:

Every other table carries id, created_at and updated_at exactly as 6.2 describes.

6.3.1 users #

Purpose. One human login identity across all workspaces.

Indexes. ux_users_email unique on (email) WHERE deleted_at IS NULL — login lookup and registration conflict detection, the single hottest identity query. ix_users_status_deletion on (status,deletion_requested_at) WHERE status = 'pending_deletion' — the nightly deletion worker scans only the tiny pending set. ix_users_locked_until on (locked_until) WHERE locked_until IS NOT NULL — lockout expiry sweep. Foreign keys. avatar_asset_id → uploaded_assets.id ON DELETE SET NULL. Checks. ck_users_password_pair: (password_hash IS NULL) = (password_algo IS NULL) — a hash without its algorithm is unverifiable. Access patterns. Point lookup by email (login, registration), point lookup by id (session resolution, cached in Redis). Never scanned in a request path.

6.3.2 user_identities #

Purpose. A linked external OAuth identity. Google is the only provider in scope; the table is provider-generic so adding one is data, not schema.

Indexes. ux_user_identities_provider_account unique on (provider,provider_account_id) — the OAuth callback lookup; also prevents one Google account being linked to two users. ix_user_identities_user on (user_id) — rendering the connected-accounts panel and the unlink safety check. Foreign keys. user_id → users.id ON DELETE CASCADE. Checks. ck_user_identities_key_version: (access_token_ciphertext IS NULL AND refresh_token_ciphertext IS NULL) OR key_version IS NOT NULL. Access patterns. Exactly one point lookup per OAuth callback; one small scan per user for the settings page.

6.3.3 sessions #

Purpose. A live authenticated browser session. The token itself is never stored.

Indexes. ux_sessions_token_hash unique on (token_hash) — the fallback path when the Redis session cache misses; must be a single index probe. ix_sessions_user_active on (user_id,last_seen_at DESC) WHERE revoked_at IS NULL — the session-list UI. ix_sessions_expiry on (absolute_expires_at) WHERE revoked_at IS NULL — the sweeper that hard-deletes expired rows. Foreign keys. user_id → users.id ON DELETE CASCADE. active_workspace_id → workspaces.id ON DELETE SET NULL — losing a workspace must not invalidate the session. Checks. ck_sessions_expiry_order: rolling_expires_at <= absolute_expires_at. Access patterns. One point lookup per uncached request; hard-deleted 7 days after absolute_expires_at by the retention worker.

6.3.4 email_verification_tokens #

Purpose. Single-use proof of control of an email address, used at registration and after an email change.

Indexes. ux_email_verification_tokens_hash unique on (token_hash). ix_email_verification_tokens_user on (user_id,created_at DESC) WHERE consumed_at IS NULL — resend throttle lookup. ix_email_verification_tokens_expiry on (expires_at) — purge sweep. Foreign keys. user_id → users.id ON DELETE CASCADE. Checks. ck_email_verification_tokens_window: expires_at > created_at. Access patterns. Point lookup by hash; hard-deleted 30 days after expires_at.

6.3.5 password_reset_tokens #

Purpose. Single-use authorisation to set a new password without the old one.

Indexes. ux_password_reset_tokens_hash unique on (token_hash). ix_password_reset_tokens_user_live on (user_id) WHERE consumed_at IS NULL AND invalidated_at IS NULL — used to invalidate prior tokens when a new one is issued. Foreign keys. user_id → users.id ON DELETE CASCADE. Access patterns. Point lookup by hash; hard-deleted 7 days after expires_at.

6.3.6 magic_link_tokens #

Purpose. Passwordless sign-in tokens.

Indexes. ux_magic_link_tokens_hash unique on (token_hash). ix_magic_link_tokens_email_recent on (email,created_at DESC) — request throttling. Foreign keys. user_id → users.id ON DELETE CASCADE. Access patterns. Point lookup by hash; hard-deleted 24 hours after expires_at.

6.3.7 email_change_requests #

Purpose. A two-sided email change: confirm at the new address, revoke from the old one.

Indexes. ux_email_change_requests_confirm unique on (confirm_token_hash), ux_email_change_requests_revoke unique on (revoke_token_hash). ux_email_change_requests_user_pending unique on (user_id) WHERE status = 'pending' — one in-flight change per user, enforced by the database rather than by a race-prone application check. Foreign keys. user_id → users.id ON DELETE CASCADE. Access patterns. Point lookup by either hash; hard-deleted 90 days after resolution (kept longer than other tokens because it is a security-incident artefact).

6.3.8 totp_secrets #

Purpose. The encrypted TOTP shared secret plus a replay guard.

Indexes. ux_totp_secrets_user unique on (user_id). Foreign keys. user_id → users.id ON DELETE CASCADE. Access patterns. One point lookup per 2FA challenge and one narrow update to last_used_step.

6.3.9 recovery_codes #

Purpose. Ten single-use codes issued alongside TOTP enrolment.

Indexes. ux_recovery_codes_hash unique on (code_hash). ix_recovery_codes_user_unused on (user_id) WHERE used_at IS NULL — the "N codes remaining" badge and the low-codes warning email. Foreign keys. user_id → users.id ON DELETE CASCADE. Checks. ux_recovery_codes_user_batch_position unique on (user_id,batch_id,position). Access patterns. Point lookup by hash on redemption; count by user for the UI.

6.3.10 auth_attempts #

Purpose. Durable authentication attempt log. Redis holds the fast counters; this table is the forensic record and the source of truth if Redis is cold.

Indexes. ix_auth_attempts_identifier_time on (identifier_hash,occurred_at DESC) — lockout evaluation. ix_auth_attempts_occurred on (occurred_at) — retention sweep. ix_auth_attempts_user_time on (user_id,occurred_at DESC) WHERE user_id IS NOT NULL — the "recent activity" security panel. Foreign keys. user_id → users.id ON DELETE SET NULL — the attempt history outlives an anonymized account, without its identity. Access patterns. Append-only writes; bounded range scans. Hard-deleted after 90 days.

6.3.11 workspaces #

Purpose. The unit of tenancy, branding, billing and retention. Every content row carries a workspace_id.

Indexes. ux_workspaces_slug unique on (slug) WHERE deleted_at IS NULL — dashboard routing and slug-availability checks. ix_workspaces_owner on (owner_user_id) — "workspaces I own" and owner-transfer validation. ix_workspaces_purge on (purge_after) WHERE purge_after IS NOT NULL — retention worker. ix_workspaces_plan on (current_plan_key,billing_status) — plan-cohort operations and dunning sweeps. Foreign keys. owner_user_id → users.id ON DELETE RESTRICT — a user with an owned workspace cannot be hard-deleted; account deletion must transfer or delete the workspace first (7.11). logo_asset_id → uploaded_assets.id ON DELETE SET NULL. default_link_domain_id/default_page_domain_id → custom_domains.id ON DELETE SET NULL. current_plan_key → plans.key ON DELETE RESTRICT. Checks. ck_workspaces_purge_pair: (deleted_at IS NULL) = (purge_after IS NULL). Access patterns. Point lookup by id (every authenticated request, cached), by slug (routing). Never scanned except by workers.

6.3.12 workspace_members #

Purpose. A user's role inside one workspace. The only edge that grants data access.

Indexes. ux_workspace_members_ws_user unique on (workspace_id,user_id) WHERE deleted_at IS NULL — prevents duplicate membership; also the authorisation lookup on every request. ux_workspace_members_single_owner unique on (workspace_id) WHERE role = 'owner' AND deleted_at IS NULL — the database, not the application, guarantees exactly one Owner. ix_workspace_members_user on (user_id) WHERE deleted_at IS NULL — the workspace switcher (8.3). ix_workspace_members_ws_role on (workspace_id,role) WHERE deleted_at IS NULL — member list and seat counting. Foreign keys. workspace_id → workspaces.id ON DELETE CASCADE. user_id → users.id ON DELETE CASCADE. invited_by_user_id, removed_by_user_id → users.id ON DELETE SET NULL. invitation_id → workspace_invitations.id ON DELETE SET NULL. Checks. ck_workspace_members_owner_unscoped: NOT (role = 'owner' AND is_scoped) — an Owner can never be scoped. Access patterns. The single hottest authorisation query: (workspace_id, user_id) → role, is_scoped, cached in the request context for the request's lifetime.

6.3.13 workspace_invitations #

Purpose. A pending emailed invitation carrying a signed single-use token.

Indexes. ux_workspace_invitations_token unique on (token_hash). ux_workspace_invitations_ws_email_pending unique on (workspace_id,email) WHERE status = 'pending' — one live invitation per address per workspace. ix_workspace_invitations_ws_status on (workspace_id,status,created_at DESC) — the pending-invitations list. ix_workspace_invitations_expiry on (expires_at) WHERE status = 'pending' — the expiry sweeper. Foreign keys. workspace_id → workspaces.id ON DELETE CASCADE. All user references ON DELETE SET NULL. Checks. ck_workspace_invitations_accept_pair: (status = 'accepted') = (accepted_at IS NOT NULL). Access patterns. Point lookup by hash on accept; small list per workspace.

6.3.14 resource_grants #

Purpose. Per-resource allow-list entries for a scoped member (Business only).

Indexes. ux_resource_grants_member_resource unique on (member_id,resource_type,resource_id) — idempotent grant creation. ix_resource_grants_member_type on (member_id,resource_type) — the scoped-list IN sub-query on every list view. ix_resource_grants_resource on (resource_type,resource_id) — "who has access to this resource" panel, and grant cleanup when the resource is purged. Foreign keys. workspace_id → workspaces.id ON DELETE CASCADE. member_id → workspace_members.id ON DELETE CASCADE. granted_by_user_id → users.id ON DELETE SET NULL. Checks. ck_resource_grants_ws_match is enforced by a composite FK: (workspace_id, member_id) → workspace_members(workspace_id, id) backed by ux_workspace_members_id_ws unique on (id,workspace_id). This makes a grant that crosses workspaces impossible at the storage layer. Access patterns. resource_id is deliberately not a real foreign key — it is polymorphic across three tables. Integrity is maintained by (a) the resource-purge job deleting matching grants in the same transaction, and (b) a nightly consistency job that deletes orphan grants and records a metric. Both are specified in 6.10.

6.3.15 audit_log_entries #

Purpose. Append-only record of security- and content-significant actions. The catalogue of events is in 8.9.