Synergy Lesson 3 Walkthrough: Entity Index and Version Log

pnpm add -D -w prettier-plugin-packagejson@^3.1.0

"plugins": [
   "prettier-plugin-astro",
   "@ianvs/prettier-plugin-sort-imports",
   "prettier-plugin-packagejson"
],

pnpm format

pnpm add drizzle-orm@^0.45.4 --filter @sidekick/synergy

import { sql, type SQL } from 'drizzle-orm';
import {
   check,
   text,
   timestamp,
   uuid,
   type AnyPgColumn,
   type CheckBuilder,
} from 'drizzle-orm/pg-core';

// Columns every Synergy table carries (Synergy overview, "Every table").
// Spread them into each pgTable: Drizzle builds a fresh column per table.

export function utcTimestamp(name: string): ReturnType<typeof timestamp> {
   return timestamp(name, { withTimezone: true });
}

// Renders our own constants as a SQL list: 'a', 'b'. sql.raw pastes text
// unescaped, so never pass it anything a user typed.
export function sqlList(values: readonly string[]): SQL {
   return sql.raw(values.map((value) => `'${value}'`).join(', '));
}

export const timestamps = {
   createdAt: utcTimestamp('created_at').notNull().defaultNow(),
   updatedAt: utcTimestamp('updated_at').notNull().defaultNow(),
};

// NULL while the row is live. A soft-deleted row (a tombstone) keeps its data.
export const tombstone = {
   deletedAt: utcTimestamp('deleted_at'),
};

// Who made the row's last write, and why. Keys only (D201).
export const ACTOR_KINDS = ['human', 'system', 'agent', 'api_key'] as const;

export const provenance = {
   actorKind: text('actor_kind', { enum: ACTOR_KINDS }).notNull(),
   actorId: uuid('actor_id'),
   reason: text('reason'),
};

// The database's half of the provenance contract. Call it in each table's
// third pgTable argument: ...provenanceChecks('entity_index', self)
export function provenanceChecks(
   table: string,
   self: { actorKind: AnyPgColumn; actorId: AnyPgColumn },
): [CheckBuilder, CheckBuilder] {
   return [
      check(
         `${table}_actor_kind_check`,
         sql`${self.actorKind} IN (${sqlList(ACTOR_KINDS)})`,
      ),
      check(
         `${table}_actor_id_check`,
         sql`(${self.actorKind} = 'system') = (${self.actorId} IS NULL)`,
      ),
   ];
}

import { sql } from 'drizzle-orm';
import {
   check,
   index,
   integer,
   pgTable,
   text,
   uuid,
} from 'drizzle-orm/pg-core';

import { profiles } from '@sidekick/core/db/schema';

import {
   provenance,
   provenanceChecks,
   sqlList,
   timestamps,
   tombstone,
   utcTimestamp,
} from './columns';
import { ownRowsPolicy } from './policies';

// Delete model: soft delete, tombstones visible (D66). RLS compares only
// user_id; the repository filters deleted_at in each query that wants live rows.

export const ENTITY_STATUSES = ['active', 'deprecated', 'superseded'] as const;

export const entityIndex = pgTable(
   'entity_index',
   {
      // Client-generated, so the editor knows the id before the first save.
      id: uuid('id').primaryKey(),
      userId: uuid('user_id')
         .notNull()
         .references(() => profiles.id, { onDelete: 'cascade' }),
      // Keys, never labels (D201): 'sandbox-notes', 'note'.
      provider: text('provider').notNull(),
      type: text('type').notNull(),
      // User content, stored as written.
      displayName: text('display_name').notNull(),
      title: text('title'),
      status: text('status', { enum: ENTITY_STATUSES })
         .notNull()
         .default('active'),
      version: integer('version').notNull().default(1),
      revision: integer('revision').notNull().default(0),
      // Moves on every content write, anchors included: the stale-write token.
      contentSeq: integer('content_seq').notNull().default(0),
      editedAt: utcTimestamp('edited_at').notNull().defaultNow(),
      ...timestamps,
      ...provenance,
      ...tombstone,
   },
   (self) => [
      index('entity_index_user_id_status_idx').on(self.userId, self.status),
      check(
         'entity_index_status_check',
         sql`${self.status} IN (${sqlList(ENTITY_STATUSES)})`,
      ),
      check(
         'entity_index_counters_check',
         sql`${self.version} > 0 AND ${self.revision} >= 0 AND ${self.contentSeq} >= 0`,
      ),
      ...provenanceChecks('entity_index', self),
      ownRowsPolicy('entity_index', self),
   ],
);

import { sql } from 'drizzle-orm';
import {
   check,
   integer,
   pgTable,
   text,
   uniqueIndex,
   uuid,
} from 'drizzle-orm/pg-core';

import { profiles } from '@sidekick/core/db/schema';

import { provenance, provenanceChecks, timestamps, tombstone } from './columns';
import { entityIndex } from './entity-index';
import { ownRowsPolicy } from './policies';

// Delete model: soft delete, tombstones visible (D66), like entity_index.
// One row per version bump, written by saveEntityContent in the same transaction.

export const versionLog = pgTable(
   'version_log',
   {
      // Written by Synergy on the server, never by an offline client.
      id: uuid('id').primaryKey().defaultRandom(),
      userId: uuid('user_id')
         .notNull()
         .references(() => profiles.id, { onDelete: 'cascade' }),
      entityId: uuid('entity_id')
         .notNull()
         .references(() => entityIndex.id, { onDelete: 'cascade' }),
      version: integer('version').notNull(),
      // Optional and stored as written: user content.
      changeNote: text('change_note'),
      ...timestamps,
      ...provenance,
      ...tombstone,
   },
   (self) => [
      // Partial: a tombstoned row never blocks logging the same version again.
      uniqueIndex('version_log_entity_id_version_key')
         .on(self.entityId, self.version)
         .where(sql`${self.deletedAt} IS NULL`),
      check('version_log_version_check', sql`${self.version} > 0`),
      ...provenanceChecks('version_log', self),
      ownRowsPolicy('version_log', self),
   ],
);

// Synergy's Drizzle tables. drizzle-kit reads this file to generate
// migrations, so every table must be exported from here.
export * from './entity-index';
export * from './version-log';

pnpm db:generate --filter=@sidekick/synergy -- --name=entity_index_and_version_log

import { sql } from 'drizzle-orm';
import { pgPolicy, type AnyPgColumn, type PgPolicy } from 'drizzle-orm/pg-core';

// Synergy's row-level security: a user reads and writes only their own rows.
// It compares only user_id, never deleted_at: tombstones stay visible to the
// repository, which filters deleted rows itself (D66).
export function ownRowsPolicy(
   tableName: string,
   self: { userId: AnyPgColumn },
): PgPolicy {
   // nullif: once a withRLS transaction ends, its pooled connection reads the
   // setting back as '' (not NULL), and ''::uuid would throw.
   const ownRow = sql`${self.userId} = nullif(current_setting('app.current_user_id', true), '')::uuid`;

   return pgPolicy(`${tableName}_own_rows`, {
      for: 'all',
      using: ownRow,
      withCheck: ownRow,
   });
}

pnpm db:generate --filter=@sidekick/synergy -- --name=own_rows_policies

pnpm db:generate --filter=@sidekick/synergy -- --custom --name=force_rls_and_soft_delete_triggers

-- Synergy's tables keep their tombstones visible (D66): 0002's policies compare
-- only user_id, and these triggers hold the soft-delete rules for every writer.

-- FORCE: the policies bind the table owner too. If app_runtime ever owned these
-- tables, RLS would still hold. Roles with BYPASSRLS (postgres, for migrations
-- and ops scripts) still bypass it.
ALTER TABLE entity_index FORCE ROW LEVEL SECURITY;
ALTER TABLE version_log FORCE ROW LEVEL SECURITY;

-- Soft delete only: a DELETE is refused unless its transaction opts in with
-- SET LOCAL app.allow_hard_delete = 'true' (erasure). Core's shared function.
CREATE TRIGGER no_hard_delete_entity_index
   BEFORE DELETE ON entity_index
   FOR EACH ROW EXECUTE FUNCTION enforce_soft_delete();

CREATE TRIGGER no_hard_delete_version_log
   BEFORE DELETE ON version_log
   FOR EACH ROW EXECUTE FUNCTION enforce_soft_delete();

-- A tombstone can be restored, but not otherwise updated. Synergy's own
-- function (0000), not core's block_update_on_deleted, which refuses restores.
CREATE TRIGGER no_update_deleted_entity_index
   BEFORE UPDATE ON entity_index
   FOR EACH ROW EXECUTE FUNCTION synergy_block_update_on_deleted();

CREATE TRIGGER no_update_deleted_version_log
   BEFORE UPDATE ON version_log
   FOR EACH ROW EXECUTE FUNCTION synergy_block_update_on_deleted();

pnpm test:integration

pnpm db:generate --filter=@sidekick/synergy -- --name=actor_kind_human

import { sql } from 'drizzle-orm';

import type { RlsTx } from '@sidekick/core/db';

// Every Synergy write calls this first, before it reads anything (D202).
// It takes the current user's lock, held until the transaction ends, so one
// user's Synergy writes run one at a time. Reads never wait for it.
async function lockCurrentUser(tx: RlsTx): Promise<void> {
   // The key is the user withRLS set, so the lock can't name another user.
   // Without withRLS, current_setting raises an error instead of locking nothing.
   await tx.execute(
      sql`select pg_advisory_xact_lock(hashtextextended(current_setting('app.current_user_id'), 0))`,
   );
}

export type SynergyMutation<Args extends unknown[], Result> = (
   tx: RlsTx,
   ...args: Args
) => Promise<Result>;

// Wraps a Synergy mutation so it takes the user's lock before anything else.
export function withUserLock<Args extends unknown[], Result>(
   mutation: SynergyMutation<Args, Result>,
): SynergyMutation<Args, Result> {
   return async function openUserLock(tx, ...args) {
      await lockCurrentUser(tx);
      return mutation(tx, ...args);
   };
}

// Synergy's error codes. Keys only (D201): the client looks up the words.
// Lesson 4's routes turn each code into an HTTP status.
export type SynergyErrorCode = 'not_found' | 'stale_content';

export class SynergyError extends Error {
   constructor(readonly code: SynergyErrorCode) {
      super(code);
      this.name = 'SynergyError';
   }
}

import { sql, type SQL } from 'drizzle-orm';
import type { AnyPgColumn } from 'drizzle-orm/pg-core';

export function increment(
   column: AnyPgColumn<{ dataType: 'number' }>,
): SQL<unknown> {
   return sql`${column} + 1`;
}

import { and, eq, isNull, sql, type InferSelectModel } from 'drizzle-orm';

import type { RlsTx } from '@sidekick/core/db';

import { entityIndex, versionLog } from '../db/schema';
import type { ACTOR_KINDS } from '../db/schema/columns';
import { increment } from '../db/utils';
import { SynergyError } from './errors';
import { withUserLock } from './lock';

type ActorKind = (typeof ACTOR_KINDS)[number];

// Who is writing. Mirrors the provenance CHECK: only the system has no id.
export interface SystemAsActor {
   kind: 'system';
   id?: never;
}
export interface NonSystemAsActor {
   kind: Exclude<ActorKind, 'system'>;
   id: string;
}

export type Actor = SystemAsActor | NonSystemAsActor;

// A row of entity_index, exactly as Drizzle returns it.
export interface EntityIndexRow extends InferSelectModel<typeof entityIndex> {}

// The user withRLS set. RLS's WITH CHECK refuses any other user_id anyway.
export const currentUserId = sql`current_setting('app.current_user_id')::uuid`;

export interface RegisterEntityPayload {
   id: string;
   provider: string;
   type: string;
   displayName: string;
   title?: string;
   actor: Actor;
}
// Creates an entity's index row. The provider calls it in the same transaction
// that inserts its own row.
export const registerEntity = withUserLock(
   async function registerEntityMutation(
      tx,
      payload: RegisterEntityPayload,
   ): Promise<EntityIndexRow> {
      const [entity] = await tx
         .insert(entityIndex)
         .values({
            id: payload.id,
            provider: payload.provider,
            type: payload.type,
            displayName: payload.displayName,
            title: payload.title,
            actorKind: payload.actor.kind,
            actorId: payload.actor.id,
            userId: currentUserId,
         })
         .returning();

      return entity;
   },
);

export interface SaveEntityContentPayload {
   id: string;
   // The content_seq the editor started from.
   baselineContentSeq: number;
   // The author flags a version; every other save is a revision.
   bump: 'revision' | 'version';
   changeNote?: string;
   displayName: string;
   title?: string;
   actor: Actor;
}

export const saveEntityContent = withUserLock(
   async function saveEntityContentMutation(
      tx,
      payload: SaveEntityContentPayload,
   ): Promise<EntityIndexRow> {
      const {
         id,
         baselineContentSeq,
         bump,
         changeNote,
         displayName,
         title,
         actor: { kind: actorKind, id: actorId },
      } = payload;
      const revision =
         bump === 'revision'
            ? increment(entityIndex.revision)
            : entityIndex.revision;
      const version =
         bump === 'version'
            ? increment(entityIndex.version)
            : entityIndex.version;
      const contentSeq = increment(entityIndex.contentSeq);

      const [updatedEntityRow] = await tx
         .update(entityIndex)
         .set({
            version,
            revision,
            contentSeq,
            displayName,
            title: title ?? null,
            editedAt: sql`now()`,
            updatedAt: sql`now()`,
            actorKind,
            actorId: actorId ?? null,
            reason: null,
         })
         .where(
            and(
               eq(entityIndex.id, id),
               eq(entityIndex.contentSeq, baselineContentSeq),
               isNull(entityIndex.deletedAt),
            ),
         )
         .returning();

      if (!updatedEntityRow) {
         // No match: either
         // 1. the baseline from which edit was initiated has shifted (updated by another tab or device),
         // 2. entity has been deleted, or
         // 3. it never existed in first place.
         // We hold the lock, so this answer can't change before we return it.
         const [live] = await tx
            .select({ id: entityIndex.id })
            .from(entityIndex)
            .where(and(eq(entityIndex.id, id), isNull(entityIndex.deletedAt)));

         throw new SynergyError(live ? 'stale_content' : 'not_found');
      }

      if (bump === 'version') {
         await tx.insert(versionLog).values({
            userId: currentUserId,
            entityId: id,
            version: updatedEntityRow.version,
            changeNote,
            actorKind,
            actorId: actorId ?? null,
         });
      }

      return updatedEntityRow;
   },
);

export interface GetEntityOptions {
   includeDeleted: boolean;
}

export async function getEntity(
   tx: RlsTx,
   id: string,
   options: GetEntityOptions,
): Promise<EntityIndexRow | undefined> {
   const [entity] = await tx
      .select()
      .from(entityIndex)
      .where(
         options.includeDeleted
            ? eq(entityIndex.id, id)
            : and(eq(entityIndex.id, id), isNull(entityIndex.deletedAt)),
      );
   return entity;
}

{
   // Lets an inferred type be named as an interface (EntityIndexRow extends
   // InferSelectModel<...>), so editors show its name instead of every field.
   files: ['**/*.ts', '**/*.tsx'],
   rules: {
      '@typescript-eslint/no-empty-object-type': [
         'error',
         { allowInterfaces: 'with-single-extends' },
      ],
   },
},

{
   // Every Synergy write takes the per-user lock first (D202): each insert,
   // update or delete in the repository must sit inside withUserLock(...).
   files: ['packages/platform/synergy/src/repository/**/*.ts'],
   plugins: { sidekick: sidekickPlugin },
   rules: {
      'sidekick/require-user-lock': 'error',
   },
},

pnpm db:generate --filter=@sidekick/synergy -- --custom --name=strict_restore

-- Restores are strict (lesson 3): an update to a soft-deleted row is allowed only
-- when it restores the row and changes nothing but deleted_at and the bookkeeping
-- columns. A restore that also edits is two statements in one transaction:
-- restore, then a normal save (which runs the content_seq check).
-- Synergy's own function: only Synergy's tables bind it, so replacing it here
-- changes exactly those tables.
CREATE OR REPLACE FUNCTION synergy_block_update_on_deleted()
RETURNS trigger LANGUAGE plpgsql AS $$
BEGIN
    IF OLD.deleted_at IS NULL THEN
        RETURN NEW;
    END IF;

    IF NEW.deleted_at IS NOT NULL THEN
        RAISE EXCEPTION
            'Cannot update a soft-deleted row in % (id: %). Restore it first.',
            TG_TABLE_NAME, OLD.id;
    END IF;

    -- The row as JSON, minus the columns a restore may change, must not differ
    IF to_jsonb(NEW) - ARRAY['deleted_at', 'updated_at', 'actor_kind', 'actor_id', 'reason']
        IS DISTINCT FROM
        to_jsonb(OLD) - ARRAY['deleted_at', 'updated_at', 'actor_kind', 'actor_id', 'reason'] THEN
        RAISE EXCEPTION
            'A restore in % (id: %) may change only deleted_at and the provenance columns.',
            TG_TABLE_NAME, OLD.id;
    END IF;

    RETURN NEW;
END;
$$;

pnpm test:integration

pnpm db:generate --filter=@sidekick/core -- --custom --name=block_update_on_deleted_message