Normalize schema

Fix the database schema to boyce codd normal form.

@coderabbitai plan schema normalization

Coding Plan

Summary

Bring the schema to BCNF in three intent-ordered phases: eliminate duplicated/derived columns, decompose the overloaded messages table, then break multivalued calendar attributes into junction tables.
Establish single sources of truth (dedicated system_prompts table, canonical PostGIS geometry, calendar_notes for notes) and remove computable columns (chats.path/share_path).
Pair every schema change in lib/db/schema.ts with a backfill-then-drop migration in drizzle/migrations/ and corresponding updates in the affected lib/actions/ functions.

Follow-up File-Level Change Summary (for implementing agents)

lib/db/schema.ts — drop users.system_prompt; add uniqueness to system_prompts.user_id; remove redundant geojson/data spatial columns from locations/visualizations; remove chats.path & chats.share_path; add chat_contexts table; add calendar_note_locations and calendar_note_user_tags tables; drop calendar_notes.location_tags & user_tags.
lib/actions/chat.ts — repoint saveSystemPrompt/getSystemPrompt to system_prompts; rewrite updateDrawingContext (and its reader) to use chat_contexts.
lib/actions/calendar.ts — remove calendar-note fan-out into messages; read/write tags via junction tables in saveNote/getNotes.
drizzle/migrations/ — add sequential migrations for each schema change (backfill before drop), clean up synthetic role:'data' rows, and reconcile the conflicting 0001 indices in meta/_journal.json.
Prompt for follow-up agent: verify no remaining consumers read chats.path/share_path, users.system_prompt, calendar role:'data' messages, or calendar_notes array columns before dropping them; confirm RLS policies for any new user-scoped tables mirror the existing calendar_notes policies.

Design Choices

Design Choice 1: Which spatial representation should be canonical in `locations`/`visualizations`?

Options Considered:

Keep PostGIS geometry as canonical; derive GeoJSON on read via ST_AsGeoJSON.
Keep geojson as canonical; derive geometry via a generated column/trigger.

Chosen Option: 1

Rationale: PostGIS geometry is the spatially-queryable, indexable source of truth, so retaining it and deriving GeoJSON on demand removes the redundancy with the least loss of capability.

Design Choice 2: How should the duplicated system prompt be consolidated?

Options Considered:

Make the dedicated system_prompts table the single source and drop the inline users.system_prompt column.
Keep the inline users.system_prompt column and drop the unused system_prompts table.

Chosen Option: 1

Rationale: A dedicated relation is the normalized representation, removes the dead/duplicate table ambiguity, and gives a clean home for system-prompt history if needed; action code already isolates these reads/writes in two functions, keeping migration risk low.

💡 User Tips

Regenerate the plan with different choices with @coderabbitai <feedback>.

Implementation Steps

Phase 1: Eliminate Duplicated and Derived Columns

Remove facts that are stored twice or are computable from a key, since these create the non-key → non-key (and redundant) dependencies that violate BCNF. This phase targets the system-prompt duplication, the dual spatial representation, and the derived chat path columns.

Task 1: Consolidate system-prompt storage into a single relation

Make system_prompts the authoritative store and drop the redundant inline column.

In lib/db/schema.ts, remove the system_prompt column from users; retain the system_prompts table (add a uniqueness constraint on user_id if a single active prompt per user is intended).
Update lib/actions/chat.ts (saveSystemPrompt, getSystemPrompt) to read/write the system_prompts table instead of users.system_prompt.
Add a migration in drizzle/migrations/ that backfills existing users.system_prompt values into system_prompts, then drops the users.system_prompt column.

Task 2: Remove redundant spatial representation in `locations` and `visualizations`

Keep one canonical geometry and derive the other format on read.

In lib/db/schema.ts, drop the redundant geojson (jsonb) column from locations and the redundant data/geometry overlap in visualizations, keeping PostGIS geometry as canonical (per Assumption 1).
Provide a migration that converts existing geojson into geometry (e.g., ST_GeomFromGeoJSON) for any populated rows before dropping the redundant column.
Since no action currently reads these tables, note for follow-up that any future reader should derive GeoJSON via ST_AsGeoJSON.

Task 3: Drop derived `chats.path` and `chats.share_path` columns

Eliminate columns that are functionally determined by id / shareable_link_id.

In lib/db/schema.ts, remove path and share_path from chats.
Add a migration dropping both columns.
Audit and update any consumer that reads these fields so the route/share URL is computed from id/shareable_link_id at the call site rather than stored.

🤖 Prompt for AI agents

This phase removes duplicated and derived columns from the schema to eliminate
BCNF violations. Update `lib/db/schema.ts` and create corresponding migrations
in `drizzle/migrations/` for each change, ensuring data is backfilled before
columns are dropped.

**Task 1.1 — Consolidate system-prompt into `system_prompts` table:**
- In `lib/db/schema.ts`, remove the `system_prompt` column from the `users`
table definition; add a uniqueness constraint on `user_id` in the
`system_prompts` table.
- In `lib/actions/chat.ts`, update `saveSystemPrompt` and `getSystemPrompt` to
read/write the `system_prompts` table instead of `users.system_prompt`.
- Add a migration that backfills existing `users.system_prompt` values into
`system_prompts`, then drops the `users.system_prompt` column.

**Task 1.2 — Remove redundant spatial columns from `locations` and
`visualizations`:**
- In `lib/db/schema.ts`, drop the redundant `geojson` (jsonb) column from
`locations` and the redundant `data`/geometry overlap in `visualizations`,
retaining PostGIS `geometry` as the canonical representation.
- Add a migration that converts existing `geojson` values into `geometry` using
`ST_GeomFromGeoJSON` for any populated rows before dropping the redundant
column.
- Add a note/comment that future readers should derive GeoJSON via
`ST_AsGeoJSON`.

**Task 1.3 — Drop derived `chats.path` and `chats.share_path` columns:**
- In `lib/db/schema.ts`, remove `path` and `share_path` from the `chats` table
definition.
- Add a migration dropping both columns.
- Audit all consumers of `chats.path` and `chats.share_path` and update them to
compute the route/share URL from `id`/`shareable_link_id` at the call site
instead.

Phase 2: Decompose the Overloaded `messages` Table

The messages table currently mixes genuine conversation rows with synthetic role:'data' rows whose content meaning depends on an embedded type discriminator — a determinant that is not a key. This phase separates those concerns so each table holds a single, well-keyed kind of fact.

Task 1: Remove calendar-note duplication from `messages`

Stop writing a verbatim JSON copy of each calendar note into messages.

In lib/actions/calendar.ts (saveNote), remove the fan-out that inserts a synthetic role:'data' calendar-note message; calendar_notes becomes the single source of truth.
Provide a data-cleanup migration/step that removes existing synthetic calendar-note rows from messages (identified by role:'data' + type:'calendar_note' payload).
Update any reader that expected calendar data inside the message stream to query calendar_notes directly (via getNotes).

Task 2: Move drawing/camera context into a dedicated table

Give structured map-context state its own normalized relation instead of overloading messages.

In lib/db/schema.ts, add a chat_contexts (or drawing_contexts) table keyed by chat_id (FK → chats.id, cascade) holding the drawing/camera state in structured columns/jsonb, with updated_at.
Update lib/actions/chat.ts (updateDrawingContext) to upsert into this new table rather than inserting a synthetic role:'data' message; update its reader counterpart accordingly.
Add a migration creating the new table (with RLS policies consistent with calendar_notes if those tables are user-scoped) and migrating/removing existing drawing-context role:'data' rows from messages.

🤖 Prompt for AI agents

This phase decomposes the overloaded `messages` table by removing synthetic
`role:'data'` rows and moving each type of structured data to its own dedicated
table. Changes span `lib/actions/calendar.ts`, `lib/actions/chat.ts`,
`lib/db/schema.ts`, and `drizzle/migrations/`.

**Task 2.1 — Remove calendar-note duplication from `messages`:**
- In `lib/actions/calendar.ts`, locate the `saveNote` function and remove the
fan-out logic that inserts a synthetic `role:'data'` calendar-note message into
`messages`; `calendar_notes` should be the sole write target.
- Add a data-cleanup migration that deletes existing synthetic calendar-note
rows from `messages`, identified by `role:'data'` and a `type:'calendar_note'`
discriminator in the payload.
- Audit any reader that consumed calendar data from the message stream and
redirect it to query `calendar_notes` directly using `getNotes`.

**Task 2.2 — Move drawing/camera context into a dedicated table:**
- In `lib/db/schema.ts`, define a new `chat_contexts` (or `drawing_contexts`)
table with: `chat_id` (FK → `chats.id`, cascade on delete), columns or a jsonb
field for drawing/camera state, and an `updated_at` timestamp.
- In `lib/actions/chat.ts`, rewrite `updateDrawingContext` to upsert into the
new `chat_contexts` table instead of inserting a synthetic `role:'data'`
message; update its corresponding reader function accordingly.
- Add a migration that creates the new table with RLS policies consistent with
existing user-scoped tables (e.g., mirroring `calendar_notes` policies), then
migrates existing drawing-context `role:'data'` rows from `messages` into the
new table before deleting them from `messages`.

Phase 3: Decompose Multivalued Calendar Attributes

calendar_notes.location_tags (jsonb) and user_tags (text[]) are repeating groups that violate 1NF (a prerequisite for BCNF). This phase replaces them with proper child relations.

Task 1: Extract calendar-note tags into junction tables

Replace array/jsonb columns with normalized link tables.

In lib/db/schema.ts, add calendar_note_locations (FK → calendar_notes.id and FK → locations.id) and calendar_note_user_tags (FK → calendar_notes.id plus a tag value or FK to a tag/user entity), each with a composite uniqueness constraint to prevent duplicates; drop location_tags and user_tags from calendar_notes.
Update lib/actions/calendar.ts (saveNote, getNotes) to write/read tags via the new junction tables instead of the array/jsonb columns.
Add a migration that backfills existing array/jsonb tag values into the junction tables and then drops the old columns.

Task 2: Reconcile migration history

Ensure the new migrations apply cleanly given the existing conflicting 0001 files.

In drizzle/migrations/, confirm the standalone untracked 0001_add_calendar_notes.sql does not collide with the journal-tracked sequence; add the new normalization migrations with correct sequential indices and update meta/_journal.json accordingly.

🤖 Prompt for AI agents

This phase replaces multivalued array/jsonb columns on `calendar_notes` with
normalized junction tables, and reconciles the migration history to ensure clean
sequential application. Changes touch `lib/db/schema.ts`,
`lib/actions/calendar.ts`, and `drizzle/migrations/`.

**Task 3.1 — Extract calendar-note tags into junction tables:**
- In `lib/db/schema.ts`:
- Add a `calendar_note_locations` table with FKs to `calendar_notes.id` and
`locations.id`, plus a composite uniqueness constraint.
- Add a `calendar_note_user_tags` table with a FK to `calendar_notes.id` and a
tag value column (or FK to a tag/user entity), plus a composite uniqueness
constraint.
  - Drop `location_tags` (jsonb) and `user_tags` (text[]) from `calendar_notes`.
- In `lib/actions/calendar.ts`, update `saveNote` to insert tags into the new
junction tables and update `getNotes` to read tags by joining/querying those
tables instead of the old array/jsonb columns.
- Add a migration that backfills existing `location_tags` and `user_tags` values
into the respective junction tables before dropping the old columns.

**Task 3.2 — Reconcile migration history:**
- In `drizzle/migrations/`, verify that the untracked standalone
`0001_add_calendar_notes.sql` does not collide with the journal-tracked
migration sequence.
- Add all new normalization migrations with correct sequential indices.
- Update `meta/_journal.json` to reflect the correct, conflict-free migration
order.

Research

The app uses Drizzle ORM over Supabase PostgreSQL (PostGIS + pgvector). The canonical schema lives in lib/db/schema.ts with migrations in drizzle/migrations/. Data access is concentrated in lib/actions/ (notably chat-db.ts, chat.ts, calendar.ts, users.ts, system-prompt-db.ts); there are no SQL joins — related data is fetched via separate queries. Exploration surfaced several normalization issues: a system-prompt value duplicated between users.system_prompt and an unused system_prompts table; locations/visualizations storing the same geometry twice (jsonb + PostGIS); the messages table overloaded with synthetic role:'data' rows that duplicate calendar notes and hold drawing/camera state; multivalued calendar_notes attributes (location_tags jsonb, user_tags array); and derived chats.path/chats.share_path columns.

🚀 Next Steps

🤖 All AI agent prompts combined

Task: 1

This phase removes duplicated and derived columns from the schema to eliminate
BCNF violations. Update `lib/db/schema.ts` and create corresponding migrations
in `drizzle/migrations/` for each change, ensuring data is backfilled before
columns are dropped.

**Task 1.1 — Consolidate system-prompt into `system_prompts` table:**
- In `lib/db/schema.ts`, remove the `system_prompt` column from the `users`
table definition; add a uniqueness constraint on `user_id` in the
`system_prompts` table.
- In `lib/actions/chat.ts`, update `saveSystemPrompt` and `getSystemPrompt` to
read/write the `system_prompts` table instead of `users.system_prompt`.
- Add a migration that backfills existing `users.system_prompt` values into
`system_prompts`, then drops the `users.system_prompt` column.

**Task 1.2 — Remove redundant spatial columns from `locations` and
`visualizations`:**
- In `lib/db/schema.ts`, drop the redundant `geojson` (jsonb) column from
`locations` and the redundant `data`/geometry overlap in `visualizations`,
retaining PostGIS `geometry` as the canonical representation.
- Add a migration that converts existing `geojson` values into `geometry` using
`ST_GeomFromGeoJSON` for any populated rows before dropping the redundant
column.
- Add a note/comment that future readers should derive GeoJSON via
`ST_AsGeoJSON`.

**Task 1.3 — Drop derived `chats.path` and `chats.share_path` columns:**
- In `lib/db/schema.ts`, remove `path` and `share_path` from the `chats` table
definition.
- Add a migration dropping both columns.
- Audit all consumers of `chats.path` and `chats.share_path` and update them to
compute the route/share URL from `id`/`shareable_link_id` at the call site
instead.
===============================================================================

Task: 2

This phase decomposes the overloaded `messages` table by removing synthetic
`role:'data'` rows and moving each type of structured data to its own dedicated
table. Changes span `lib/actions/calendar.ts`, `lib/actions/chat.ts`,
`lib/db/schema.ts`, and `drizzle/migrations/`.

**Task 2.1 — Remove calendar-note duplication from `messages`:**
- In `lib/actions/calendar.ts`, locate the `saveNote` function and remove the
fan-out logic that inserts a synthetic `role:'data'` calendar-note message into
`messages`; `calendar_notes` should be the sole write target.
- Add a data-cleanup migration that deletes existing synthetic calendar-note
rows from `messages`, identified by `role:'data'` and a `type:'calendar_note'`
discriminator in the payload.
- Audit any reader that consumed calendar data from the message stream and
redirect it to query `calendar_notes` directly using `getNotes`.

**Task 2.2 — Move drawing/camera context into a dedicated table:**
- In `lib/db/schema.ts`, define a new `chat_contexts` (or `drawing_contexts`)
table with: `chat_id` (FK → `chats.id`, cascade on delete), columns or a jsonb
field for drawing/camera state, and an `updated_at` timestamp.
- In `lib/actions/chat.ts`, rewrite `updateDrawingContext` to upsert into the
new `chat_contexts` table instead of inserting a synthetic `role:'data'`
message; update its corresponding reader function accordingly.
- Add a migration that creates the new table with RLS policies consistent with
existing user-scoped tables (e.g., mirroring `calendar_notes` policies), then
migrates existing drawing-context `role:'data'` rows from `messages` into the
new table before deleting them from `messages`.
===============================================================================

Task: 3

This phase replaces multivalued array/jsonb columns on `calendar_notes` with
normalized junction tables, and reconciles the migration history to ensure clean
sequential application. Changes touch `lib/db/schema.ts`,
`lib/actions/calendar.ts`, and `drizzle/migrations/`.

**Task 3.1 — Extract calendar-note tags into junction tables:**
- In `lib/db/schema.ts`:
- Add a `calendar_note_locations` table with FKs to `calendar_notes.id` and
`locations.id`, plus a composite uniqueness constraint.
- Add a `calendar_note_user_tags` table with a FK to `calendar_notes.id` and a
tag value column (or FK to a tag/user entity), plus a composite uniqueness
constraint.
  - Drop `location_tags` (jsonb) and `user_tags` (text[]) from `calendar_notes`.
- In `lib/actions/calendar.ts`, update `saveNote` to insert tags into the new
junction tables and update `getNotes` to read tags by joining/querying those
tables instead of the old array/jsonb columns.
- Add a migration that backfills existing `location_tags` and `user_tags` values
into the respective junction tables before dropping the old columns.

**Task 3.2 — Reconcile migration history:**
- In `drizzle/migrations/`, verify that the untracked standalone
`0001_add_calendar_notes.sql` does not collide with the journal-tracked
migration sequence.
- Add all new normalization migrations with correct sequential indices.
- Update `meta/_journal.json` to reflect the correct, conflict-free migration
order.

💡 Iterate on the plan with: `@coderabbitai` <feedback>

Example Feedback
- `@coderabbitai` You can skip phase 3. Add a simple unit test case for phase 2.
- `@coderabbitai` For design choice 1 go ahead with option 3 and replan.

💬 Have feedback or questions? Drop into our discord!

@coderabbitai
I have reviewed the tasks and the current state of the codebase. To ensure I have absolute certainty before proceeding with the plan, I have a few questions for you:

Task 1.1 — Consolidate system-prompt:

The system_prompts table already exists in lib/db/schema.ts with a userId. Should the uniqueness constraint on user_id make it a one-to-one relationship (i.e., one prompt per user)?
Are there any other actions besides lib/actions/chat.ts (e.g., lib/actions/system-prompt.ts) that I should ensure are updated?
Task 1.2 — Spatial columns: 3. For locations.geojson, should I assume all relevant spatial data is already in the geometry column, or should the migration prioritize extracting the geometry from the geojson column if both exist? 4. For visualizations.data, should I drop the entire column, or only remove the geometry data from within the JSON object while keeping the column for other metadata (like styling)? The task says "drop the redundant data/geometry overlap", which could mean the whole column is redundant if it only contains geometry, or just the geometry part of it.

Task 1.3 — Drop derived chat paths: 5. I've identified the route for path as /search/${id}. What is the expected route format for share_path? Is it /share/${shareableLinkId}?

Task 2.1 — Calendar-note duplication: 6. Beyond removing the fan-out in saveNote, are there specific UI components or background jobs you know of that rely on reading these "synthetic" data messages from the messages table? I want to make sure I redirect all readers.

Task 2.2 — Drawing/camera context: 7. Do you have a preference for the new table name: chat_contexts or drawing_contexts? 8. When migrating existing drawing-context rows from messages, what is the reliable way to distinguish them from other role: 'data' messages? (e.g. is it just any role: 'data' that doesn't have type: 'calendar_note').

Task 3.1 — Calendar-note tags: 9. For calendar_note_user_tags, should the tag column be a simple text field (storing the @mention handle), or should it link to a userId? 10. How is the current location_tags (jsonb) in calendar_notes structured? Is it an array of location IDs?

Task 3.2 — Migration reconciliation: 11. I see two migrations with the 0001 index. Should I re-sequence them (e.g. 0001, 0002, 0003...) or merge them?

🧩 Analysis chain

🏁 Script executed:

#!/bin/bash
set -euo pipefail

echo "== Locate relevant files =="
fd -i 'schema.ts|chat.ts|calendar.ts|system-prompt|users.ts|_journal.json|0001.*sql' .

echo
echo "== Outline key files =="
for f in \
  "$(fd -p '^lib/db/schema\.ts$' . | head -n1)" \
  "$(fd -p '^lib/actions/chat\.ts$' . | head -n1)" \
  "$(fd -p '^lib/actions/calendar\.ts$' . | head -n1)"
do
  if [ -n "${f:-}" ]; then
    echo "-- ast-grep outline: $f"
    ast-grep outline "$f" --view expanded || true
    echo
  fi
done

echo
echo "== system_prompt / system_prompts usages =="
rg -n -C3 --glob '!**/node_modules/**' '\bsystem_prompt\b|\bsystem_prompts\b' .

echo
echo "== share/path usages in chats and route patterns =="
rg -n -C3 --glob '!**/node_modules/**' '\bshare_path\b|\bpath\b|shareable_link_id|shareableLinkId|/search/\$\{|/share/\$\{|/share/|/search/' .

echo
echo "== geojson / geometry / visualizations.data usages =="
rg -n -C3 --glob '!**/node_modules/**' '\bgeojson\b|\bgeometry\b|\bvisualizations\b|\bdata\b' lib/db lib/actions .

echo
echo "== messages role:data / calendar_note / drawing / camera usages =="
rg -n -C4 --glob '!**/node_modules/**' "role:\s*'data'|role:\s*\"data\"|calendar_note|drawing|camera|context" lib .

echo
echo "== calendar note tag schema + usages =="
rg -n -C4 --glob '!**/node_modules/**' '\blocation_tags\b|\buser_tags\b|calendar_note_locations|calendar_note_user_tags' lib drizzle .

echo
echo "== migrations journal and 0001 files =="
journal="$(fd -p '^drizzle/meta/_journal\.json$' . | head -n1)"
if [ -n "${journal:-}" ]; then
  echo "-- $journal"
  cat "$journal"
fi
echo
fd -p '^drizzle/migrations/.*0001.*\.sql$' . -x sh -c 'echo "-- $1"; sed -n "1,220p" "$1"; echo' sh {}

echo
echo "== Narrow slices: schema.ts relevant sections =="
schema="$(fd -p '^lib/db/schema\.ts$' . | head -n1)"
if [ -n "${schema:-}" ]; then
  rg -n 'systemPrompts|system_prompt|geojson|visualizations|locations|calendar_notes|location_tags|user_tags|path|share_path|messages|role|data' "$schema"
  echo "-- slice 1"
  sed -n '1,260p' "$schema"
  echo "-- slice 2"
  sed -n '260,520p' "$schema"
fi

echo
echo "== Narrow slices: chat.ts relevant sections =="
chatf="$(fd -p '^lib/actions/chat\.ts$' . | head -n1)"
if [ -n "${chatf:-}" ]; then
  rg -n 'saveSystemPrompt|getSystemPrompt|updateDrawingContext|getLatestCameraState|messages|systemPrompts|users|share|path|context' "$chatf"
  sed -n '1,260p' "$chatf"
  echo "-- later slice"
  sed -n '260,520p' "$chatf"
fi

echo
echo "== Narrow slices: calendar.ts relevant sections =="
calf="$(fd -p '^lib/actions/calendar\.ts$' . | head -n1)"
if [ -n "${calf:-}" ]; then
  rg -n 'saveNote|getNotes|messages|calendar_note|location_tags|user_tags|locationTags|userTags' "$calf"
  sed -n '1,320p' "$calf"
fi

echo
echo "== Search for readers of role:data messages outside actions =="
rg -n -C3 --glob '!**/node_modules/**' "role:\s*'data'|role:\s*\"data\"|calendar_note|drawing_context|cameraState|camera_state|drawingContext|getLatestCameraState|getDrawing" app lib components hooks pages src .

echo
echo "== Search for route files related to share/search =="
fd -i 'share|search|chat|route.ts|page.tsx' app pages src

Length of output: 229730

🏁 Script executed:

#!/bin/bash
set -euo pipefail

echo "== Locate relevant files =="
fd -i 'schema.ts|chat.ts|calendar.ts|system-prompt|users.ts|_journal.json|0001.*sql' .

echo
echo "== Outline key files =="
for f in \
  "$(fd -p '^lib/db/schema\.ts$' . | head -n1)" \
  "$(fd -p '^lib/actions/chat\.ts$' . | head -n1)" \
  "$(fd -p '^lib/actions/calendar\.ts$' . | head -n1)"
do
  if [ -n "${f:-}" ]; then
    echo "-- ast-grep outline: $f"
    ast-grep outline "$f" --view expanded || true
    echo
  fi
done

echo
echo "== system_prompt / system_prompts usages =="
rg -n -C3 --glob '!**/node_modules/**' '\bsystem_prompt\b|\bsystem_prompts\b' .

echo
echo "== share/path usages in chats and route patterns =="
rg -n -C3 --glob '!**/node_modules/**' '\bshare_path\b|\bpath\b|shareable_link_id|shareableLinkId|/search/\$\{|/share/\$\{|/share/|/search/' .

echo
echo "== geojson / geometry / visualizations.data usages =="
rg -n -C3 --glob '!**/node_modules/**' '\bgeojson\b|\bgeometry\b|\bvisualizations\b|\bdata\b' lib/db lib/actions .

echo
echo "== messages role:data / calendar_note / drawing / camera usages =="
rg -n -C4 --glob '!**/node_modules/**' "role:\s*'data'|role:\s*\"data\"|calendar_note|drawing|camera|context" lib .

echo
echo "== calendar note tag schema + usages =="
rg -n -C4 --glob '!**/node_modules/**' '\blocation_tags\b|\buser_tags\b|calendar_note_locations|calendar_note_user_tags' lib drizzle .

echo
echo "== migrations journal and 0001 files =="
journal="$(fd -p '^drizzle/meta/_journal\.json$' . | head -n1)"
if [ -n "${journal:-}" ]; then
  echo "-- $journal"
  cat "$journal"
fi
echo
fd -p '^drizzle/migrations/.*0001.*\.sql$' . -x sh -c 'echo "-- $1"; sed -n "1,220p" "$1"; echo' sh {}

echo
echo "== Narrow slices: schema.ts relevant sections =="
schema="$(fd -p '^lib/db/schema\.ts$' . | head -n1)"
if [ -n "${schema:-}" ]; then
  rg -n 'systemPrompts|system_prompt|geojson|visualizations|locations|calendar_notes|location_tags|user_tags|path|share_path|messages|role|data' "$schema"
  echo "-- slice 1"
  sed -n '1,260p' "$schema"
  echo "-- slice 2"
  sed -n '260,520p' "$schema"
fi

echo
echo "== Narrow slices: chat.ts relevant sections =="
chatf="$(fd -p '^lib/actions/chat\.ts$' . | head -n1)"
if [ -n "${chatf:-}" ]; then
  rg -n 'saveSystemPrompt|getSystemPrompt|updateDrawingContext|getLatestCameraState|messages|systemPrompts|users|share|path|context' "$chatf"
  sed -n '1,260p' "$chatf"
  echo "-- later slice"
  sed -n '260,520p' "$chatf"
fi

echo
echo "== Narrow slices: calendar.ts relevant sections =="
calf="$(fd -p '^lib/actions/calendar\.ts$' . | head -n1)"
if [ -n "${calf:-}" ]; then
  rg -n 'saveNote|getNotes|messages|calendar_note|location_tags|user_tags|locationTags|userTags' "$calf"
  sed -n '1,320p' "$calf"
fi

echo
echo "== Search for readers of role:data messages outside actions =="
rg -n -C3 --glob '!**/node_modules/**' "role:\s*'data'|role:\s*\"data\"|calendar_note|drawing_context|cameraState|camera_state|drawingContext|getLatestCameraState|getDrawing" app lib components hooks pages src .

echo
echo "== Search for route files related to share/search =="
fd -i 'share|search|chat|route.ts|page.tsx' app pages src

Length of output: 229730

🏁 Script executed:

#!/bin/bash
set -euo pipefail

schema="$(fd -p '^lib/db/schema\.ts$' . | head -n1)"
chatf="$(fd -p '^lib/actions/chat\.ts$' . | head -n1)"
calf="$(fd -p '^lib/actions/calendar\.ts$' . | head -n1)"
journal="$(fd -p '^drizzle/migrations/meta/_journal\.json$' . | head -n1)"

echo "== Files =="
printf 'schema=%s\nchat=%s\ncalendar=%s\njournal=%s\n' "$schema" "$chatf" "$calf" "$journal"

echo
echo "== system_prompt / system_prompts definitions + usages =="
rg -n -C2 --glob '!**/node_modules/**' '\bsystem_prompt\b|\bsystemPrompts\b|\bsystem_prompts\b|saveSystemPrompt|getSystemPrompt' lib app components drizzle

echo
echo "== schema.ts: system prompts / chats / locations / visualizations sections =="
rg -n 'system_prompt|systemPrompts|system_prompts|share_path|path|shareable_link_id|locations|visualizations|geojson|geometry|data' "$schema" || true
sed -n '1,220p' "$schema"

echo
echo "== chat.ts relevant slice =="
rg -n 'saveSystemPrompt|getSystemPrompt|updateDrawingContext|getLatestCameraState|share|path|system_prompt|systemPrompts' "$chatf" || true
sed -n '150,320p' "$chatf"

echo
echo "== calendar.ts relevant slice =="
rg -n 'saveNote|getNotes|locationTags|userTags|location_tags|user_tags|calendar_note|createMessage' "$calf" || true
sed -n '1,220p' "$calf"

echo
echo "== share/search route and field usages =="
rg -n -C2 --glob '!**/node_modules/**' 'shareable_link_id|shareableLinkId|share_path|/share/|/search/|path:' app lib components

echo
echo "== locations / visualizations usage only =="
rg -n -C2 --glob '!**/node_modules/**' '\blocations\b|\bvisualizations\b|\bgeojson\b|\bgeometry\b' lib app components drizzle

echo
echo "== journal content and migration filenames =="
if [ -n "$journal" ]; then
  cat "$journal"
fi
echo
fd -p '^drizzle/migrations/.*\.sql$' drizzle/migrations | sort

echo
echo "== any direct readers of calendar_note synthetic messages =="
rg -n -C2 --glob '!**/node_modules/**' "calendar_note|role:\s*'data'|role:\s*\"data\"" app lib components

echo
echo "== any direct readers of drawing context messages =="
rg -n -C2 --glob '!**/node_modules/**' 'getLatestCameraState|drawing_context|updateDrawingContext|cameraState|drawnFeatures' app lib components

Length of output: 13413

🏁 Script executed:

#!/bin/bash
set -euo pipefail

schema="$(fd -p '^lib/db/schema\.ts$' . | head -n1)"
chatf="$(fd -p '^lib/actions/chat\.ts$' . | head -n1)"
calf="$(fd -p '^lib/actions/calendar\.ts$' . | head -n1)"
journal="$(fd -p '^drizzle/migrations/meta/_journal\.json$' . | head -n1)"

echo "== Files =="
printf 'schema=%s\nchat=%s\ncalendar=%s\njournal=%s\n' "$schema" "$chatf" "$calf" "$journal"

echo
echo "== system_prompt / system_prompts definitions + usages =="
rg -n -C2 --glob '!**/node_modules/**' '\bsystem_prompt\b|\bsystemPrompts\b|\bsystem_prompts\b|saveSystemPrompt|getSystemPrompt' lib app components drizzle

echo
echo "== schema.ts: system prompts / chats / locations / visualizations sections =="
rg -n 'system_prompt|systemPrompts|system_prompts|share_path|path|shareable_link_id|locations|visualizations|geojson|geometry|data' "$schema" || true
sed -n '1,220p' "$schema"

echo
echo "== chat.ts relevant slice =="
rg -n 'saveSystemPrompt|getSystemPrompt|updateDrawingContext|getLatestCameraState|share|path|system_prompt|systemPrompts' "$chatf" || true
sed -n '150,320p' "$chatf"

echo
echo "== calendar.ts relevant slice =="
rg -n 'saveNote|getNotes|locationTags|userTags|location_tags|user_tags|calendar_note|createMessage' "$calf" || true
sed -n '1,220p' "$calf"

echo
echo "== share/search route and field usages =="
rg -n -C2 --glob '!**/node_modules/**' 'shareable_link_id|shareableLinkId|share_path|/share/|/search/|path:' app lib components

echo
echo "== locations / visualizations usage only =="
rg -n -C2 --glob '!**/node_modules/**' '\blocations\b|\bvisualizations\b|\bgeojson\b|\bgeometry\b' lib app components drizzle

echo
echo "== journal content and migration filenames =="
if [ -n "$journal" ]; then
  cat "$journal"
fi
echo
fd -p '^drizzle/migrations/.*\.sql$' drizzle/migrations | sort

echo
echo "== any direct readers of calendar_note synthetic messages =="
rg -n -C2 --glob '!**/node_modules/**' "calendar_note|role:\s*'data'|role:\s*\"data\"" app lib components

echo
echo "== any direct readers of drawing context messages =="
rg -n -C2 --glob '!**/node_modules/**' 'getLatestCameraState|drawing_context|updateDrawingContext|cameraState|drawnFeatures' app lib components

Length of output: 13413

@ngoiyaeric I dug through the returned evidence. Here’s the tightest answer I can give, split into confirmed from repo vs recommended implementation choice where the repo output still doesn’t prove the exact shape.

system_prompts.user_id uniqueness
- Recommendation: yes, make it one row per user.
- Why: the current app treats the system prompt as singular:
  - lib/actions/chat.ts has singular saveSystemPrompt/getSystemPrompt (Line 213, Line 232).
  - components/settings/components/settings.tsx reads one existing prompt and writes one prompt (Line 91, Line 124).
  - app/actions.tsx also expects a single prompt value via getSystemPrompt (Line 492).
- So a unique constraint on system_prompts.user_id matches actual usage and the normalization goal.
Other actions/files to update besides lib/actions/chat.ts
- Confirmed consumers:
  - components/settings/components/settings.tsx imports getSystemPrompt / saveSystemPrompt from lib/actions/chat.ts.
  - app/actions.tsx imports getSystemPrompt from lib/actions/chat.ts.
- About lib/actions/system-prompt.ts:
  - From the returned grep, it appears to handle generation jobs (getSystemPromptGenerationJob), not persisted prompt storage.
  - I did not see evidence that it reads/writes users.system_prompt.
- So the main storage refactor target is still lib/actions/chat.ts, but make sure those two callers continue to work.
locations.geojson migration priority when both geojson and geometry exist
- Recommendation: treat geometry as canonical if it is present; only backfill from geojson when geometry is null/missing.
- I wouldn’t overwrite an existing geometry value from geojson unless you have evidence of drift and a deterministic conflict rule.
- Safest migration shape:
  - if geometry is null and geojson is present → derive geometry
  - if both exist → preserve geometry
- This matches the normalization plan’s “PostGIS geometry is canonical” direction.
visualizations.data: drop whole column or just geometry overlap?
- Not fully confirmed from returned output.
- My recommendation is:
  - do not drop the whole data column unless you verify it contains only redundant geometry.
  - If it also carries styling/metadata, remove only the geometry payload overlap and keep the rest.
- So: preserve metadata, eliminate duplication.
Expected share_path route
- I could confirm /search/${id} from app/search/[id]/page.tsx.
- I could not confirm a corresponding /share/[shareableLinkId] route from the returned output.
- So I would not hardcode /share/${shareableLinkId} yet as a certainty. If you do find an actual share route file, then compute from that route instead of storing share_path.
Known readers of synthetic calendar-note messages rows
- From the returned evidence, I found:
  - the writer in lib/actions/calendar.ts (role: 'data', type: 'calendar_note') at Line 96–98.
  - the calendar UI (components/calendar-notepad.tsx) already talks to getNotes / saveNote, i.e. it appears to use calendar_notes directly.
- I did not find a direct reader of those synthetic calendar_note message rows in the returned results.
- So my best answer is: no known UI/background consumer surfaced; removing the fan-out looks low-risk, but I’d still grep once more locally before deleting old rows.
Preferred new table name: chat_contexts vs drawing_contexts
- Preference: chat_contexts.
- Reason: the payload is broader than drawings alone—it includes drawnFeatures and cameraState (lib/actions/chat.ts, Line 186–197).
- chat_contexts better matches the stored concept.
How to distinguish drawing-context rows from other role: 'data' rows
- Confirmed: current drawing-context rows do not include a type discriminator.
  - lib/actions/chat.ts writes raw JSON.stringify(contextData) with drawnFeatures and cameraState.
- Confirmed: calendar rows do include type: 'calendar_note'.
- So the reliable discriminator for existing data is:
  - role = 'data'
  - payload JSON contains cameraState and/or drawnFeatures
  - and is not a type: 'calendar_note' payload
- I would not use “any role: 'data' that isn’t calendar” as the only rule. Use payload-shape checks.
calendar_note_user_tags: text tag vs userId FK
- Recommendation: use a simple text column first.
- Why:
  - current schema is text[] (lib/db/schema.ts, Line 99), not uuid[].
  - no returned evidence showed resolution to actual user IDs.
- So normalize the existing fact as stored today; don’t invent a stronger FK unless you confirm every tag maps to a user row.
Current structure of calendar_notes.location_tags

Not confirmed from the returned code.
What is confirmed:
- it is stored as raw jsonb (lib/db/schema.ts, Line 98).
I did not get evidence proving whether it is:
- an array of location IDs,
- an array of objects,
- or some other JSON shape.
So I would not assume array-of-IDs blindly. Inspect sample rows before writing the backfill. If you want the migration to be robust, make it tolerant of both:
- plain string/UUID IDs
- objects with an id field

Two 0001 migrations: re-sequence or merge?

Recommendation: neither blindly merge nor renumber historical applied migrations.
What the evidence shows:
- drizzle/migrations/0001_sync_schema_full.sql already includes calendar_notes.
- drizzle/migrations/0001_add_calendar_notes.sql is a duplicate/stray creation of calendar_notes plus RLS.
So the safer approach is:
- treat 0001_add_calendar_notes.sql as the conflicting standalone migration the plan called out,
- keep one canonical historical sequence,
- continue new work from the next unused sequential index in the journaled chain.
In practice: don’t merge history, and don’t renumber already-applied history just for neatness. Resolve the duplicate so only one 0001 is part of the effective sequence.

Short version / implementation defaults I’d use

1.1 Make system_prompts.user_id unique; migrate lib/actions/chat.ts; verify components/settings/components/settings.tsx and app/actions.tsx.
1.2 Keep geometry canonical; backfill from geojson only when geometry is null; keep visualizations.data unless it is geometry-only.
1.3 Confirm share route before assuming /share/${shareableLinkId}.
2.1 I didn’t find known readers of synthetic calendar-note messages.
2.2 Use chat_contexts; identify old rows by JSON shape (cameraState / drawnFeatures).
3.1 Use text for calendar_note_user_tags; inspect real location_tags data before finalizing the backfill.
3.2 Resolve the duplicate 0001; keep the canonical migration history and add new migrations after the last real index.

If you want, I can next turn this into a revised implementation checklist with these ambiguities resolved so you can execute phase-by-phase without re-deciding anything.

Navigation Menu

Repository navigation

Normalize schema #677

Coding Plan

Summary

Follow-up File-Level Change Summary (for implementing agents)

Implementation Steps

Phase 1: Eliminate Duplicated and Derived Columns

Phase 2: Decompose the Overloaded `messages` Table

Phase 3: Decompose Multivalued Calendar Attributes

🚀 Next Steps

Short version / implementation defaults I’d use

Metadata

Assignees

Labels

Type

Projects

Milestone

Relationships

Development

Normalize schema #677

Description

Activity

ngoiyaeric commented on Jun 26, 2026

coderabbitai commented on Jun 26, 2026

Coding Plan

Summary

Follow-up File-Level Change Summary (for implementing agents)

Implementation Steps

Phase 1: Eliminate Duplicated and Derived Columns

Phase 2: Decompose the Overloaded messages Table

Phase 3: Decompose Multivalued Calendar Attributes

🚀 Next Steps

ngoiyaeric commented on Jun 26, 2026

coderabbitai commented on Jun 26, 2026

Short version / implementation defaults I’d use

Metadata

Metadata

Assignees

Labels

Type

Projects

Milestone

Relationships

Development

Issue actions

Phase 2: Decompose the Overloaded `messages` Table