tfks/trippin

Fork 0

docs: primary key type analysis (SERIAL vs UUID, v4 vs v7) #9

Merged

erik merged 4 commits from chore/pk-type-research into main

2026-05-20 05:35:32 +00:00

agent-dev commented

2026-05-20 04:10:25 +00:00

Member

Refs #4

Summary

Audited all migrations — every table currently uses uuid PRIMARY KEY DEFAULT gen_random_uuid() (UUID v4)
Researched SERIAL vs UUID and UUID v4 vs UUID v7
Findings saved in docs/primary-key-types.md (wiki write returns 403 for agent-dev; see note below)

Findings

SERIAL → keep UUID. Integer keys would be inconsistent with Supabase Auth's UUID tables, leak row counts, and block offline ID generation.

UUID v7 → adopt going forward. Postgres 17 (major_version = 17 in supabase/config.toml) ships uuidv7() natively. v7 is timestamp-prefixed so it has sequential index locality like a SERIAL while retaining UUID global uniqueness and opacity. Recommendation: replace DEFAULT gen_random_uuid() with DEFAULT uuidv7() in all new migration files. No need to backfill existing tables at this scale.

Wiki note

fjx wiki set returns 403 Forbidden for agent-dev. Findings are in docs/primary-key-types.md instead — owner can copy to the wiki or grant agent-dev wiki-write access.

Refs #4 --- ## Summary - Audited all migrations — every table currently uses `uuid PRIMARY KEY DEFAULT gen_random_uuid()` (UUID v4) - Researched SERIAL vs UUID and UUID v4 vs UUID v7 - Findings saved in `docs/primary-key-types.md` (wiki write returns 403 for agent-dev; see note below) ## Findings **SERIAL → keep UUID.** Integer keys would be inconsistent with Supabase Auth's UUID tables, leak row counts, and block offline ID generation. **UUID v7 → adopt going forward.** Postgres 17 (`major_version = 17` in `supabase/config.toml`) ships `uuidv7()` natively. v7 is timestamp-prefixed so it has sequential index locality like a SERIAL while retaining UUID global uniqueness and opacity. Recommendation: replace `DEFAULT gen_random_uuid()` with `DEFAULT uuidv7()` in all new migration files. No need to backfill existing tables at this scale. ## Wiki note `fjx wiki set` returns `403 Forbidden` for agent-dev. Findings are in `docs/primary-key-types.md` instead — owner can copy to the wiki or grant agent-dev wiki-write access.

agent-dev added 1 commit

2026-05-20 04:10:25 +00:00

docs: add primary key type analysis (SERIAL vs UUID, v4 vs v7)

Validate / validate (pull_request) Failing after 12s

Details

af5dc8b0a9

Research for issue #4. Conclusion: keep UUID, switch new tables to uuidv7() (native in Postgres 17). Wiki write blocked by agent-dev permissions; storing in docs/ instead.

Co-Authored-By: Claude Sonnet 4.6 <noreply@anthropic.com>

agent-dev referenced this pull request

2026-05-20 04:10:38 +00:00

Consider alternate type for primary keys #4

agent-dev added the

agent

review

label

2026-05-20 04:11:05 +00:00

erik added 1 commit

2026-05-20 04:24:11 +00:00

docs: move PK type findings to wiki/findings/

Validate / validate (pull_request) Failing after 9s

Details

6ac7271cb4

Co-Authored-By: Claude Sonnet 4.6 <noreply@anthropic.com>

agent-dev commented

2026-05-20 04:24:26 +00:00

Author

Member

Corrected: findings moved from docs/primary-key-types.md to wiki/findings/primary-key-types.md to match the existing repo wiki convention.

Corrected: findings moved from `docs/primary-key-types.md` to `wiki/findings/primary-key-types.md` to match the existing repo wiki convention.

~~agent-pm referenced this pull request 2026-05-20 04:49:15 +00:00~~

Consider alternate type for primary keys #4

erik added 1 commit

2026-05-20 04:55:57 +00:00

feat: switch all primary keys to uuidv7()

Validate / validate (pull_request) Failing after 9s

Details

d3909054d1

Replace `DEFAULT gen_random_uuid()` with `DEFAULT uuidv7()` in all migration files (9 columns across 6 files). Postgres 17 ships uuidv7() natively — no extension needed. UUID v7 has timestamp-prefixed sequential locality that avoids random index scatter while retaining global uniqueness and opacity.

Co-Authored-By: Claude Sonnet 4.6 <noreply@anthropic.com>

erik added 1 commit

2026-05-20 05:33:19 +00:00

feat: add pure-SQL uuidv7() function migration

Validate / validate (pull_request) Failing after 9s

Details

2904434f89

Defines uuidv7() in public schema via a pure SQL implementation — no C
extension required, works on Supabase PG17 today. Drop-in replaceable
when Supabase ships PG18 and the native function lands.

Co-Authored-By: Claude Sonnet 4.6 <noreply@anthropic.com>

erik merged commit 0c3dd8f0db into main

2026-05-20 05:35:32 +00:00

erik deleted branch chore/pk-type-research

2026-05-20 05:35:32 +00:00

erik referenced this pull request from a commit

2026-05-20 05:35:34 +00:00

Merge pull request 'docs: primary key type analysis (SERIAL vs UUID, v4 vs v7)' (#9) from chore/pk-type-research into main