identifiers and keys

why

conflating storage identity with human meaning couples public behavior to implementation: a record id in urls freezes storage mechanics into every bookmark; a slug derived from a display label changes identity whenever copy changes. keeping the four identities separate gives stable public addresses, scoped uniqueness matching the business fact, and a clean reconciliation path for external systems — each changeable without breaking the others.

rule

generated identifiers identify stored records; authored names and slugs identify human meaning. never manufacture a business key from a path or display label.

separate four identities

identity	purpose	source of truth
record id	uniquely address one stored row	maintained id library or database/platform generator.
authored key	identify a human-recognizable domain fact	exact authored value with a unique constraint.
scoped business identity	prove one fact exists once within an owner/scope	composite unique constraint over explicit columns.
external identity	reconcile a provider object	provider-qualified field plus an index when reconciliation reads it.

one row can carry all four. they answer different questions; substituting one for another creates unstable URLs, duplicate facts, or ambiguous reconciliation.

public-identifier boundary

never expose a raw record id in user-facing UI, normal URLs, visible copy, exports, or client state merely because it is available. a record id is an internal storage key; use the domain's exact stored slug, handle, or other deliberate public key.

the public key has its own owner, validation, uniqueness boundary, and lifecycle — not a display label transformed at request time.
a route resolves the public key at the boundary, authorizes access, then passes the record id internally.
rendered views receive public domain facts, not the storage identifier.

surface	default identifier	exception
user-facing route	canonical `slug`, `handle`, or domain-owned public key	an explicitly designed opaque public token when the domain has no stable human key.
visible UI, copy, and links	human-facing domain value	protected operator tooling that needs a diagnostic reference.
client payload and browser storage	public key or a server-issued scoped reference	raw record id only when a reviewed integration requires it and no public boundary can represent the operation.
server service and database relation	generated record id	none; this is its intended boundary.

when a raw id is genuinely required outside the server:

name the reason in the owning contract.
constrain the recipient and lifetime; never promote the exception into a general routing or display convention.
use a separate safe correlation identifier for user-visible support/recovery references — never a durable row id.

decision process

tempting shortcut	failure	selected rule
derive a slug from a display label or file path	a copy or rename silently changes identity	store the exact authored key.
use a global unique constraint for a per-owner fact	legitimate facts collide across scopes	constrain the owner plus local key.
show the generated record id in UI or normal routes	storage mechanics leak and future route design is constrained	resolve a domain-owned public key at the boundary.
treat a provider id as the row's primary identity	provider changes leak into internal references	retain a generated record id and store the external id explicitly.
generate identifiers with ad hoc string logic	format, entropy, and ordering guarantees drift	use the required maintained generator.

practice

use a maintained id library or platform API for required id formats.
store the authored slug exactly and enforce its uniqueness.
use composite unique constraints when a fact is unique only within a scope.
keep external-provider identifiers explicit and indexed where reconciliation needs them.

verification

for every new durable fact, write its record id, authored key, scope, external identity, and public surface separately — even when some are absent. confirm:

the schema enforces the intended unique boundary.
retries cannot create another row for the same scoped business fact.
routes, UI, client payloads, and links expose no record id before approving a migration.

good and bad

bad

// schema: no authored slug, only record id
create table workflows (
  id text primary key, -- used in public URLs
  title text not null,
  created_at text not null
);

// route: expose record id and derive slug from title
app.post('/api/workflows', async (req, res) => {
  const slug = req.body.title.toLowerCase().replaceAll(" ", "-");
  // ← slug is not stored, only derived

  const workflow = await db.insert(workflows).values({
    id: createWorkflowId(), // e.g., 'wf_k9x2m4p1'
    title: req.body.title,
  });

  // URL uses record id; publicly shared links have no stable identifier
  return res.json({ url: `/workflows/${workflow.id}` });
});

// later, user renames the workflow
app.patch('/api/workflows/:workflowId', async (req, res) => {
  if (req.body.title) {
    // ← the derived slug changes, breaking all bookmarked links
    const newSlug = req.body.title.toLowerCase().replaceAll(" ", "-");
    // but there's no slug field to update

    await db.update(workflows).set({ title: req.body.title }).where(eq(workflows.id, req.params.workflowId));
  }
  return res.json({ success: true });
});

three failures: (1) record id exposed in public URLs — changing databases or id format breaks all public links; (2) slug derived each time, so the same workflow has different URLs at different times (a bookmarked /workflows/my-workflow stops resolving when the title becomes my workflow!); (3) one URL per title variant breaks deduplication and caching. after a rename, old bookmarked links 404.

good

-- schema: authored slug is the public identity
create table workflows (
  id text primary key, -- internal use only
  workspace_id text not null,
  slug text not null, -- exact authored value, stored and unique per workspace
  title text not null,
  created_at text not null,
  unique (workspace_id, slug) -- slug is the business key
);

-- index for public lookups
create index workflows_by_slug on workflows(workspace_id, slug);

// route: validate and store the exact slug
app.post("/api/workflows", async (req, res) => {
  const slug = req.body.slug || slugify(req.body.title); // default to title, but user can customize

  // validate slug format (alphanumeric + hyphens)
  if (!/^[a-z0-9-]+$/.test(slug)) {
    return res.status(400).json({ error: "slug must be alphanumeric and hyphens only" });
  }

  // schema enforces uniqueness per workspace
  const workflow = await db
    .insert(workflows)
    .values({
      id: createWorkflowId(), // internal id, never exposed
      workspace_id: req.user.workspace_id,
      slug: slug, // exact authored value
      title: req.body.title,
    })
    .catch((err) => {
      if (err.code === "UNIQUE_VIOLATION") {
        return res.status(409).json({ error: "slug already in use" });
      }
      throw err;
    });

  // public URL uses slug, not record id
  return res.json({
    id: workflow.id, // keep internal id for client state only
    slug: workflow.slug,
    url: `/workflows/${workflow.slug}`,
  });
});

// later, user renames the title but keeps the slug
app.patch("/api/workflows/:workflowSlug", async (req, res) => {
  const workflow = await db
    .select()
    .from(workflows)
    .where(
      and(
        eq(workflows.workspace_id, req.user.workspace_id),
        eq(workflows.slug, req.params.workflowSlug),
      ),
    );

  if (!workflow) return res.status(404).json({ error: "not found" });

  // title can change without affecting the URL
  if (req.body.title) {
    await db.update(workflows).set({ title: req.body.title }).where(eq(workflows.id, workflow.id));
  }

  // slug can be explicitly changed only via a separate endpoint
  return res.json({ slug: workflow.slug, title: req.body.title });
});

the slug is now the public identity, not the record id:

record id stays internal and can change without breaking public links.
slug is stable — bookmarked /workflows/my-workflow keeps working after a title change.
exactly one URL per workflow: the exact stored slug.
slug collisions within a workspace are impossible (unique constraint).
a slug change is an explicit decision via a separate endpoint, never a side effect of a title rename.