Offline-first POS, part 2: never store a stock level

-- Do not do this.
CREATE TABLE products (
  id    uuid PRIMARY KEY,
  name  text NOT NULL,
  stock integer NOT NULL DEFAULT 0
);

CREATE TABLE stock_movements (
  id          uuid PRIMARY KEY,
  product_id  uuid NOT NULL REFERENCES products(id),
  quantity    integer NOT NULL,   -- negative for a sale, positive for a delivery
  reason      text NOT NULL,      -- 'sale' | 'delivery' | 'count' | 'wastage'
  sale_id     uuid REFERENCES sales(id),
  occurred_at timestamptz NOT NULL,
  created_at  timestamptz NOT NULL DEFAULT now()
);

SELECT COALESCE(SUM(quantity), 0) AS on_hand
FROM stock_movements
WHERE product_id = $1;

CREATE MATERIALIZED VIEW product_stock AS
SELECT p.id AS product_id,
       COALESCE(SUM(m.quantity), 0) AS on_hand
FROM products p
LEFT JOIN stock_movements m ON m.product_id = p.id
GROUP BY p.id;

CREATE UNIQUE INDEX ON product_stock (product_id);

CREATE TABLE sales (
  id           uuid PRIMARY KEY,          -- generated on the till
  device_id    text NOT NULL,
  cashier_id   uuid NOT NULL REFERENCES users(id),
  occurred_at  timestamptz NOT NULL,      -- when it happened, on the till
  received_at  timestamptz NOT NULL DEFAULT now(),
  subtotal_cents integer NOT NULL,
  discount_cents integer NOT NULL DEFAULT 0,
  total_cents    integer NOT NULL,
  currency     char(3) NOT NULL DEFAULT 'USD',
  payment_method text NOT NULL,           -- 'cash' | 'khqr' | 'card'
  payment_state  text NOT NULL,           -- 'paid' | 'pending_verification'
  voided_at    timestamptz
);

CREATE TABLE sale_lines (
  id          uuid PRIMARY KEY,
  sale_id     uuid NOT NULL REFERENCES sales(id) ON DELETE CASCADE,
  product_id  uuid NOT NULL REFERENCES products(id),
  quantity    integer NOT NULL CHECK (quantity > 0),
  unit_price_cents integer NOT NULL,
  line_total_cents integer NOT NULL
);

import { Entity, PrimaryColumn, Column, OneToMany, Index } from "typeorm";
import { SaleLine } from "./SaleLine";

@Entity("sales")
export class Sale {
  /**
   * PrimaryColumn, not PrimaryGeneratedColumn. The till generates this id
   * before the server knows the sale exists — that is the whole basis of
   * idempotent retries in part 4.
   */
  @PrimaryColumn("uuid")
  id!: string;

  @Column({ name: "device_id" })
  deviceId!: string;

  @Column({ name: "cashier_id", type: "uuid" })
  cashierId!: string;

  /** When the sale happened on the till. Reports use this. */
  @Index()
  @Column({ name: "occurred_at", type: "timestamptz" })
  occurredAt!: Date;

  /** When it reached the server. Diagnostics only — never report on this. */
  @Column({ name: "received_at", type: "timestamptz", default: () => "now()" })
  receivedAt!: Date;

  @Column({ name: "total_cents", type: "integer" })
  totalCents!: number;

  @Column({ name: "payment_method" })
  paymentMethod!: "cash" | "khqr" | "card";

  @Column({ name: "payment_state" })
  paymentState!: "paid" | "pending_verification";

  @Column({ name: "voided_at", type: "timestamptz", nullable: true })
  voidedAt!: Date | null;

  @OneToMany(() => SaleLine, (line) => line.sale, { cascade: ["insert"] })
  lines!: SaleLine[];
}

import { openDB, type DBSchema } from "idb";

interface TillDB extends DBSchema {
  /** Read-only catalogue copy, refreshed whenever the server is reachable. */
  products: {
    key: string;
    value: { id: string; name: string; priceCents: number; barcode?: string };
    indexes: { "by-barcode": string };
  };

  /** Sales made on this device. Never deleted — the local audit trail. */
  sales: {
    key: string;
    value: LocalSale;
    indexes: { "by-synced": number };
  };

  /**
   * The outbox: ids awaiting acknowledgement. Separate from `sales` so that
   * "what happened" and "what still needs sending" cannot drift apart. A sale
   * is never removed from `sales` when it syncs — only from here.
   */
  outbox: {
    key: string;
    value: { saleId: string; attempts: number; lastAttemptAt: number | null };
  };
}

export const db = await openDB<TillDB>("till", 1, {
  upgrade(database) {
    const products = database.createObjectStore("products", { keyPath: "id" });
    products.createIndex("by-barcode", "barcode");

    const sales = database.createObjectStore("sales", { keyPath: "id" });
    // IndexedDB cannot index booleans — 0/1 is the standard workaround.
    sales.createIndex("by-synced", "synced");

    database.createObjectStore("outbox", { keyPath: "saleId" });
  },
});

/**
 * A stock count does not overwrite anything. It records the difference between
 * what the system believed and what the shelf actually holds, as its own
 * movement with a reason.
 *
 * The discrepancy is the valuable number — it is theft, breakage or a scanning
 * mistake. Overwriting the level erases the one figure worth looking at.
 */
export async function recordCount(
  productId: string,
  countedQty: number,
  countedBy: string,
) {
  const onHand = await currentStock(productId);
  const delta = countedQty - onHand;

  if (delta === 0) return { adjusted: false, delta: 0 };

  await movements.insert({
    id: newId(),
    productId,
    quantity: delta,
    reason: "count",
    occurredAt: new Date(),
    note: `Counted ${countedQty}, system had ${onHand}, by ${countedBy}`,
  });

  return { adjusted: true, delta };
}