DEV Community

Daniel Pertu
Daniel Pertu

Posted on

One text column holds YouTube IDs, Instagram handles and place IDs, and the absence of a colon is the type tag

#typescript #postgres #node #webdev

Nakodo looks at creators on YouTube, Instagram and TikTok, and at businesses from open map listings. Four kinds of thing, and one channels table:

export const channels = pgTable(
  "channels",
  {
    // YouTube channel ID (UC...); on other platforms the platform and the
    // lowercase handle, e.g. "tiktok:name"
    id: text().primaryKey(),
    platform: text({ enum: PLATFORMS }).notNull().default("youtube"),
Enter fullscreen mode Exit fullscreen mode

So a primary key can read as any of these:

UCsBjURrPoezykLs9EqgamOA
instagram:home.barista
tiktok:coffee.nerd
web:08f194ad1b1ec1d60386bb3bb6e1e0a7
Enter fullscreen mode Exit fullscreen mode

YouTube IDs have no prefix because they came first and because they already carry a shape nobody else uses. Everything added since names itself. The whole type test is one character:

export function platformOf(channelId: string): Platform {
  if (!channelId.includes(":")) return "youtube";
  const prefix = channelId.split(":", 1)[0];
  if (prefix === "web") return "web";
  return (SOCIAL_PLATFORMS as string[]).includes(prefix) ? (prefix as SocialPlatform) : "youtube";
}
Enter fullscreen mode Exit fullscreen mode

Ten call sites. The useful property is that any function holding an ID knows what it is holding, with no join, no second argument and no lookup. A URL builder, an avatar component, a job handler and an SQL fragment can all branch on the same string.

The last line of that function is also its weak spot. An unknown prefix falls back to "youtube", so youtube:abc or a typo like instgram:x is silently treated as a YouTube channel, and the next thing that happens is a YouTube API call for a channel that cannot exist. null would be more honest, at the cost of making every call site handle a case that only a bug can produce. I would still take the null now.

The platform is stored twice

The prefix is in the key, and there is also a platform column with an enum and a default. That is deliberate duplication, because the two copies answer different questions:

  • the key answers "what am I holding", in TypeScript, with no database round trip;
  • the column answers "which rows are these", in SQL, where like 'tiktok:%' cannot use a plain index and platform = 'tiktok' can. Ten query sites use the column, including inArray(channels.platform, platforms) for a campaign's chosen platforms and eq(channels.platform, "youtube") for the retention job that only YouTube data needs.

The two copies agree because exactly one piece of code inserts channels, and it derives one from the other:

.values([...byChannel].map(([id, c]) => ({ id, platform: platformOf(id), title: c.title })))
Enter fullscreen mode Exit fullscreen mode

One line, one invariant, and nothing in the database enforcing it. A generated column (platform text generated always as (...)) or a CHECK would make a disagreeing row impossible instead of merely unlikely; what we have makes it impossible as long as nobody adds a second insert. That is the honest state of it.

Writing links

Every profile link the app stores or renders comes out of one function, so there is one spelling of each:

export function socialProfileUrl(platform: SocialPlatform, handle: string): string {
  const h = handle.replace(/^@/, "").toLowerCase();
  return platform === "instagram" ? `https://www.instagram.com/${h}/` : `https://www.tiktok.com/@${h}`;
}
Enter fullscreen mode Exit fullscreen mode

Instagram with a trailing slash, TikTok with an @ and none, both lowercase, both without www. variation or query strings. Eight call sites, which is the number of places that would otherwise have invented their own format.

creatorUrl sits on top of it and has one branch I am not proud of:

if (platform === "web") return "#";
Enter fullscreen mode Exit fullscreen mode

A business found in a map listing has no profile page to link to. The caller renders a link anyway, so there is an href="#" in the signed-in app for exactly that case. It should be a <span> and the decision should be made by the component, not by a URL builder returning a URL that is not one.

Reading links

The harder direction is reading. A link to Instagram or TikTok might be an account, a post, a help page, a login page, or something that merely looks like one of those:

export function parseSocialUrl(raw: string): SocialLink | null {
  let u: URL;
  try {
    u = new URL(raw);
  } catch {
    return null;
  }
  const host = u.hostname.replace(/^(www|m|mobile)\./, "");
  const platform = SOCIAL_PLATFORMS.find((p) => SOCIAL_SITES[p] === host);
  if (!platform) return null;
  ...
}
Enter fullscreen mode Exit fullscreen mode

Three decisions worth pointing at.

The host is compared for equality after stripping exactly three prefixes, www., m. and mobile.. Not a suffix match, not endsWith, not a regex with a dot in it. instagram.com.evil.test and example.com/instagram.com/brewer are both nothing, and the platforms' own other subdomains (help., business.) are nothing too, because they host help pages and marketing, never accounts.

A handle has to look like a handle:

const HANDLE: Record<SocialPlatform, RegExp> = {
  instagram: /^[a-z0-9._]{1,30}$/,
  tiktok: /^[a-z0-9._]{2,24}$/,
};
Enter fullscreen mode Exit fullscreen mode

TikTok's minimum is 2, so tiktok.com/@a is not an account. Hyphens are not in either set, so @some-name is not either.

And the first path segment can be one of the site's own pages rather than anybody's account. Instagram has 24 of those in our list:

p reel reels tv stories explore accounts popular about legal developer direct
web directory topics locations challenge emails privacy terms session oauth api ar
Enter fullscreen mode Exit fullscreen mode

Four of them (p, reel, reels, tv) get a second flag, because a post link is not a dead end: it says a post exists without saying whose. So the result type has three fields instead of one:

type SocialLink = {
  platform: SocialPlatform;
  handle: string | null; // lowercase, no "@"; null when the page isn't an account's
  postId: string | null; // the TikTok video or photo the link is to
  post: boolean; // an Instagram post or reel, whose link doesn't name the account
};
Enter fullscreen mode Exit fullscreen mode

handle: null with post: true means "this is one of theirs, but you will have to get the account from somewhere else". null for the whole result means "not their site at all". Two different kinds of nothing, which a single return of null would have flattened.

The test that matters most is the round trip 

assert.equal(socialProfileUrl("instagram", "@Home.Barista"), "https://www.instagram.com/home.barista/");
assert.equal(creatorUrl({ id: "instagram:home.barista" }), "https://www.instagram.com/home.barista/");
// Reading a built link gives the handle back.
assert.equal(handleOf(socialProfileUrl("instagram", "home.barista")), "home.barista");
assert.equal(handleOf(socialProfileUrl("tiktok", "brewer")), "brewer");
Enter fullscreen mode Exit fullscreen mode

Writer and reader are two functions with no shared code, so they can drift. Asserting that one undoes the other is the cheapest guard against a trailing slash or a stray @ appearing on one side only. The same tests cover a percent-encoded @ (tiktok.com/%40brewer reads as brewer) and a malformed escape (@brewer%E0%A4%A returns nothing rather than throwing out of decodeURIComponent, which is why the decode helper has its own try-catch).

The small vocabulary that comes with it

Because the platform is always in hand, so is its language:

export function audienceWord(platform: Platform): "subscribers" | "followers" {
  return platform === "youtube" ? "subscribers" : "followers";
}
export function postWord(platform: Platform): "video" | "post" {
  return platform === "youtube" || platform === "tiktok" ? "video" : "post";
}
Enter fullscreen mode Exit fullscreen mode

Six and eight call sites. TikTok is a "video" platform and an Instagram upload is a "post", which is the kind of difference that would otherwise be hard-coded in fourteen places and get it wrong in two.

The public pages are hand-written rather than generated from these, and they still line up when you measure them: across the 76 pages in our sitemap, "subscribers" appears on none of the ten TikTok and Instagram niche pages, while all 13 of the YouTubers by niche pages talk about videos, and all five Instagrammers by niche pages talk about posts. The defined terms are on the glossary.

One text column, one colon, four kinds of row, and two findings I will fix in that order: platformOf returning null for an unknown prefix, and a database constraint so the key and the column cannot disagree.

Top comments (1)

Collapse
 
robertpaschal profile image
Odinaka Robert Nnamani •

Comparing hosts by equality after stripping www, m and mobile avoids lookalike domains.