CRUD and when to use Postgres

For data with an owner, use one actor per owner: a workspace, a user or a document. Keep that owner's rows in tables inside its actor. Commands create, update and delete rows; queries read them.

Akter doesn't give every row an actor. A workspace's tasks can be ordinary SQLite rows, all changed in the workspace's transactions.

Put tasks in their workspace⁠

The workspace key chooses the database. The task id identifies a row inside that database:

workspace.contract.ts

import { Schema } from "effect"
import { Akter } from "@akterjs/effect/contract"

export const Task = Schema.Struct({ id: Schema.String, title: Schema.String, done: Schema.Boolean })

export class TaskNotFoundError extends Schema.TaggedError<TaskNotFoundError>()("TaskNotFoundError", {
  id: Schema.String,
}) {}

export const Workspace = Akter.make("Workspace", {
  key: Schema.String,
  state: Schema.Struct({}),
  initial: () => ({}),
  commands: {
    add: Akter.command({ payload: { title: Schema.String }, success: Schema.String }),
    complete: Akter.command({ payload: { id: Schema.String }, success: Schema.Void, error: TaskNotFoundError }),
    remove: Akter.command({ payload: { id: Schema.String }, success: Schema.Void }),
  },
  queries: {
    tasks: Akter.query({ success: Schema.Array(Task) }),
  },
})

The command's SQL and state changes share one transaction. A failure rolls them all back:

workspace.actor.ts

import { Effect } from "effect"
import { Akter } from "@akterjs/effect/contract"
import { TaskNotFoundError, Workspace } from "./workspace.contract.ts"

export const WorkspaceLive = Workspace.toLayer({
  migrations: {
    1: (sql) => sql`CREATE TABLE tasks (
      id TEXT PRIMARY KEY,
      title TEXT NOT NULL,
      done INTEGER NOT NULL CHECK (done IN (0, 1))
    )`,
  },
  commands: {
    add: (_workspace, { title }) =>
      Effect.gen(function* () {
        const sql = yield* Akter.sql
        const id = yield* Akter.id
        yield* sql`INSERT INTO tasks ${sql.insert({ id, title, done: 0 })}`
        return id
      }),
    complete: (_workspace, { id }) =>
      Effect.gen(function* () {
        const sql = yield* Akter.sql
        const changed = yield* sql<{ id: string }>`UPDATE tasks SET done = 1 WHERE id = ${id} RETURNING id`
        if (changed.length === 0) {
          return yield* new TaskNotFoundError({ id })
        }
      }),
    remove: (_workspace, { id }) =>
      Effect.asVoid(Effect.flatMap(Akter.sql, (sql) => sql`DELETE FROM tasks WHERE id = ${id}`)),
  },
  queries: {
    tasks: () =>
      Effect.flatMap(Akter.sql, (sql) =>
        Effect.map(
          sql<{ id: string; title: string; done: number }>`SELECT id, title, done FROM tasks ORDER BY title, id`,
          (rows) => rows.map((row) => ({ id: row.id, title: row.title, done: row.done === 1 })),
        ),
      ),
  },
})

complete refuses a missing task; remove is a no-op when the row is already gone. SQLite stores the boolean as 0 or 1, and the query converts it to the contract's Boolean.

Try it⁠

Rows in alpha aren't in beta, even though both workspaces use the same contract and handlers:

workspace.story.ts

import { Effect } from "effect"
import { Story } from "@akterjs/effect/testing"
import { Workspace } from "./workspace.contract.ts"
import { WorkspaceLive } from "./workspace.actor.ts"

const program = Story.run(WorkspaceLive, function* (story) {
  const alpha = yield* story.actor(Workspace, "alpha")
  const beta = yield* story.actor(Workspace, "beta")

  const docs = yield* alpha.add({ title: "Ship docs" })
  const tests = yield* alpha.add({ title: "Write tests" })
  yield* alpha.complete({ id: docs })

  for (const task of yield* alpha.tasks()) {
    console.log(`${task.title}: ${task.done ? "done" : "open"}`)
  }

  yield* alpha.remove({ id: tests })
  console.log(`alpha: ${(yield* alpha.tasks()).length}, beta: ${(yield* beta.tasks()).length}`)
})

Effect.runPromise(program)

Text

Ship docs: done
Write tests: open
alpha: 1, beta: 0

Separate databases aren't authorization. Check that a caller may use the selected workspace in your handlers; see Access. The example leaves that check out to show the data model.

For shared data with modest writes, one database actor can own it. Its commands are serialized, so it gives shared tables one writer and one transaction boundary. It also puts all of those writes through that actor; use it when that fits the workload, rather than splitting the data only to need a cross-actor transaction later.

A stateless endpoint can be a Worker or an Effect HttpApi handler that calls the relevant actor client. The endpoint doesn't need its own actor.

Use Postgres when it is simpler⁠

Choose Postgres for:

Global unique constraints, such as a slug that must be unique across every workspace.
Transactions that must change rows belonging to different owners together.
Reporting that joins data across many owners.
Existing tools or integrations built around Postgres.

An actor transaction covers one actor, not the whole app. Messages coordinate actors after commit, and jobs can call other services, but neither makes separate databases one atomic transaction.

Put tasks in their workspace⁠

Try it⁠

Shared data⁠

Use Postgres when it is simpler⁠