familysync/.planning/milestones/v1.1-phases/10-admin-role-settings/10-01-SUMMARY.md

phase, plan, subsystem, tags, dependency_graph, tech_stack, key_files, decisions, metrics

phase	plan	subsystem	tags	dependency_graph	tech_stack	key_files	decisions	metrics
10-admin-role-settings	01	database	schema migration mariadb drizzle admin seed	requires provides affects users.is_admin column (boolean NOT NULL DEFAULT false) member_credentials.provider_type column (varchar(64) NOT NULL DEFAULT 'caldav') member_credentials UNIQUE(user_id) constraint uniq_member_credential_user calendar_events.reminder_lead_minutes column (int nullable) app_config table (key VARCHAR PK, value TEXT, updated_at) appConfig Drizzle table export in schema.ts migration 0001_famous_mad_thinker.sql applied to live dev MariaDB e2e global-setup seeds users id=1 with is_admin=true Phase 10 plans 02-04 (requireAdmin DB lookup, /api/me isAdmin, admin routes) Phase 11 (reminder_lead_minutes consumed) Phase 12 (app_config.setup_complete consumed)	added patterns drizzle-kit generate + direct SQL apply (journal hash mismatch workaround) idempotent INSERT ON DUPLICATE KEY UPDATE for e2e seed	created modified apps/api/src/db/migrations/0001_famous_mad_thinker.sql apps/api/src/db/migrations/meta/0001_snapshot.json apps/api/src/db/schema.ts apps/api/src/db/migrations/meta/_journal.json apps/pwa/e2e/global-setup.ts	Applied migration DDL directly (mysql2) and inserted hash into __drizzle_migrations due to journal hash mismatch with legacy migration tracking; drizzle-kit migrate silently exited 1 without applying SQL Migration is additive-only (verified: grep for DROP/TRUNCATE returns 0)	duration_seconds completed_date tasks_completed files_modified 265 2026-06-13 3 5

Phase 10 Plan 01: v1.1 DB Foundation Summary

One-liner: v1.1 schema migration adding users.is_admin, member_credentials.provider_type+UNIQUE(user_id), calendar_events.reminder_lead_minutes, and app_config table — applied to live dev MariaDB and seeded dev-bypass user as admin.

Tasks Completed

Task	Name	Commit	Files
1	Extend schema.ts with v1.1 column/table bundle	d0a4cb4	apps/api/src/db/schema.ts
2	Generate + apply v1.1 migration to live dev DB	ad7ba3a	0001_famous_mad_thinker.sql, meta/_journal.json, meta/0001_snapshot.json
3	Seed dev-bypass user id=1 as admin in e2e global-setup	bb00c71	apps/pwa/e2e/global-setup.ts

What Was Built

Task 1: Schema additions

Four additive changes to apps/api/src/db/schema.ts, all matching the existing column idiom:

1. users.isAdmin: boolean('is_admin').default(false).notNull() — copies the allDay boolean pattern; first-login-wins admin flag (D-01).
2. memberCredentials.providerType: varchar('provider_type', { length: 64 }).notNull().default('caldav') — generic provider discriminator (D-04); plus unique('uniq_member_credential_user').on(t.userId) added to the index array alongside the existing idx_member_credentials_user_id (D-05 one-credential-per-member enforcement + enables onDuplicateKeyUpdate upsert).
3. calendarEvents.reminderLeadMinutes: int('reminder_lead_minutes') — nullable, no .notNull(); consumed by Phase 11.
4. New appConfig table: key VARCHAR(128) PK, value TEXT nullable, updatedAt timestamp DEFAULT NOW() ON UPDATE; setup_complete key semantics documented for Phase 12.

TypeScript typecheck (tsc --noEmit) passes clean.

Task 2: Migration generation and application

pnpm --filter @familysync/api db:generate produced 0001_famous_mad_thinker.sql — additive-only DDL:

• CREATE TABLE app_config
• ALTER TABLE calendar_events ADD reminder_lead_minutes int
• ALTER TABLE member_credentials ADD provider_type varchar(64) DEFAULT 'caldav' NOT NULL
• ALTER TABLE users ADD is_admin boolean DEFAULT false NOT NULL
• ALTER TABLE member_credentials ADD CONSTRAINT uniq_member_credential_user UNIQUE(user_id)

grep -v '^--' ... | grep -ciE 'drop (table|column)|truncate' = 0 (additive-only confirmed).

Migration application deviation: drizzle-kit migrate exited 1 silently without applying the SQL. Root cause: the live dev DB __drizzle_migrations table contains 5 rows from legacy incremental development (different hashes from before the generate+migrate workflow was adopted); the journal's hash for 0000_baseline does not match any existing row, causing drizzle-kit to stop. Resolution: applied all 5 DDL statements directly via mysql2, then inserted the correct SHA-256 hash of 0001_famous_mad_thinker.sql into __drizzle_migrations. Future migrations via drizzle-kit should work correctly from this point.

Live DB verification:

SHOW COLUMNS FROM users LIKE 'is_admin'           → 1 row
SHOW COLUMNS FROM member_credentials LIKE 'provider_type' → 1 row
SHOW COLUMNS FROM calendar_events LIKE 'reminder_lead_minutes' → 1 row
SHOW TABLES LIKE 'app_config'                     → 1 row

Query output: MIGRATION OK

Task 3: E2E dev-bypass admin seed

Added idempotent seed in apps/pwa/e2e/global-setup.ts inside the seed block (before the FK-checks-on, matching the existing INSERT IGNORE INTO calendars pattern):

INSERT INTO users (id, oidc_iss, oidc_sub, display_name, color, is_admin)
VALUES (1, 'dev-bypass', 'dev-user-1', 'Dev User', '#4A90D9', true)
ON DUPLICATE KEY UPDATE is_admin=true

Supplies non-null oidc_iss, oidc_sub, color satisfying NOT NULL constraints. Idempotent via ON DUPLICATE KEY UPDATE. The bypass path never reads oidc_iss/oidc_sub so placeholder values are safe. Existing calendar/event/list seeds unchanged (INSERT IGNORE INTO calendars and Seeded Test Event anchors still present).

Deviations from Plan

Auto-fixed Issues

1. [Rule 1 - Bug] drizzle-kit migrate journal hash mismatch — applied DDL directly

• Found during: Task 2
• Issue: drizzle-kit migrate silently exited 1 without applying any SQL. The live dev DB __drizzle_migrations table had 5 rows with hashes from a prior incremental migration workflow (before the generate+migrate adoption in quick 260610-cr8). drizzle-kit compares the journal's first entry against the DB and stops on mismatch.
• Fix: Applied the 5 DDL statements from 0001_famous_mad_thinker.sql directly via mysql2 (splitting on --> statement-breakpoint delimiter). Inserted the SHA-256 hash of the migration file into __drizzle_migrations so future drizzle-kit runs treat this migration as applied.
• Impact: None on correctness — the DDL is identical to what drizzle-kit would have applied. Future migrations should work normally since the hash record is now in the DB.
• Files modified: No extra files; the migration SQL and journal were already committed.

Known Stubs

None. This plan creates DB infrastructure only; no UI stubs.

Threat Flags

None. No new network endpoints, auth paths, or trust-boundary changes in this plan. The migration is schema-only; the e2e seed is guarded by the existing NODE_ENV === 'production' and DEV_AUTH_BYPASS !== 'true' fail-closed guards.

Self-Check: PASSED

• apps/api/src/db/schema.ts contains is_admin, provider_type, uniq_member_credential_user, reminder_lead_minutes, appConfig export: verified
• apps/api/src/db/migrations/0001_famous_mad_thinker.sql exists and committed: verified (ad7ba3a)
• Live DB MIGRATION OK query: PASSED
• grep -c is_admin apps/pwa/e2e/global-setup.ts = 3 (≥1): verified
• pnpm --filter @familysync/api exec tsc --noEmit exits 0: verified
• Commits d0a4cb4, ad7ba3a, bb00c71 in git log: verified