Backend Live Coding

# ======================================================================
# Q: You accidentally ran git reset --hard and lost commits. What do you do?
#
# The reflog keeps every position of HEAD for about 90 days locally, so almost
# nothing that was committed is lost. On public history I use revert, not reset.
# ======================================================================

git reflog                       # history of HEAD, including the "lost" commits
# a1b2c3d HEAD@{0}: reset: moving to HEAD~3
# 9f8e7d6 HEAD@{1}: commit: Add VAT rounding per line
git branch rescue 9f8e7d6        # or: git reset --hard HEAD@{1}

# bad commit already pushed to main: I do not rewrite history
git revert <sha>

# commit on the wrong branch
git switch correct-branch && git cherry-pick <sha>
git switch wrong-branch   && git reset --hard HEAD~1

-- ======================================================================
-- Q: Multi-tenant schema for assets and alerts: how do you ensure that an alert
-- does not point to another tenant's asset?
--
-- The composite foreign key (tenant_id, asset_id) makes it impossible in the
-- database to link an alert to another tenant's asset, even if the code has a
-- bug. tenant_id goes first in all indexes. RLS is a second line of defense: the
-- application runs SET LOCAL app.tenant_id at the start of every transaction.
-- ======================================================================

CREATE TABLE assets (
    id        bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    tenant_id bigint NOT NULL REFERENCES tenants(id),
    hostname  text   NOT NULL,
    UNIQUE (tenant_id, hostname),
    UNIQUE (tenant_id, id)              -- target for the composite FK
);

CREATE TABLE alerts (
    id         bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    tenant_id  bigint NOT NULL,
    asset_id   bigint NOT NULL,
    severity   smallint NOT NULL CHECK (severity BETWEEN 1 AND 5),
    status     text NOT NULL DEFAULT 'open' CHECK (status IN ('open','ack','closed')),
    created_at timestamptz NOT NULL DEFAULT now(),
    FOREIGN KEY (tenant_id, asset_id) REFERENCES assets (tenant_id, id)
);

-- Defense in depth: Row Level Security
ALTER TABLE alerts ENABLE ROW LEVEL SECURITY;
CREATE POLICY tenant_isolation ON alerts
    USING (tenant_id = current_setting('app.tenant_id')::bigint);

-- ======================================================================
-- Q: Composite index: in what order do you put the columns? What is a covering
-- index?
--
-- Rule: equality columns first, then the range or ORDER BY column. The index (a,
-- b, c) serves WHERE a, WHERE a AND b, but not WHERE b alone (leftmost prefix).
-- INCLUDE adds columns to the leaf pages without making them part of the key, so
-- the query can be answered from the index (Index Only Scan, if the visibility
-- map is up to date from VACUUM). Every index has a cost on writes, so I drop
-- them when pg_stat_user_indexes shows idx_scan = 0.
-- ======================================================================

-- Query: WHERE tenant_id = ? AND status = ? ORDER BY created_at DESC LIMIT 50
CREATE INDEX CONCURRENTLY ix_alerts_tenant_status_created
    ON alerts (tenant_id, status, created_at DESC)
    INCLUDE (severity, title);           -- index-only scan, no heap fetch

-- Partial: only the open alerts, a much smaller index
CREATE INDEX CONCURRENTLY ix_alerts_open
    ON alerts (tenant_id, created_at DESC) WHERE status = 'open';

-- ======================================================================
-- Q: Design a schema for B2B orders with per-customer price lists that are valid
-- from-to.
--
-- Points I explain out loud in the interview:
-- - EXCLUDE with daterange: the database forbids two price lists that overlap in
--   time for the same customer. With a check in code there would be a race
--   condition between two concurrent admins.
-- - Partial unique index: one draft cart per customer, but unlimited completed
--   orders. Same for the ERP external_ref, which makes the sync idempotent
--   (INSERT ... ON CONFLICT).
-- - Snapshot of price and VAT on the line: if the price list changes, the
--   invoice does not change.
-- - ON DELETE RESTRICT for customers with history, CASCADE only for "children"
--   that make no sense on their own.
-- - In Django: ExclusionConstraint (django.contrib.postgres),
--   UniqueConstraint(condition=Q(...)), CheckConstraint.
-- ======================================================================

CREATE EXTENSION IF NOT EXISTS btree_gist;

CREATE TABLE customers (
    id          bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    vat_number  varchar(20) NOT NULL UNIQUE,
    name        text        NOT NULL,
    kind        text        NOT NULL CHECK (kind IN ('b2b', 'b2c')),
    deleted_at  timestamptz
);

CREATE TABLE products (
    id          bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    sku         varchar(40) NOT NULL UNIQUE,
    name        text        NOT NULL,
    vat_rate    numeric(4,2) NOT NULL CHECK (vat_rate >= 0)
);

CREATE TABLE price_lists (
    id          bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    customer_id bigint NOT NULL REFERENCES customers(id) ON DELETE RESTRICT,
    currency    char(3) NOT NULL DEFAULT 'EUR',
    valid       daterange NOT NULL,
    -- overlapping price lists for the same customer are not allowed
    EXCLUDE USING gist (customer_id WITH =, valid WITH &&)
);

CREATE TABLE price_list_items (
    price_list_id bigint NOT NULL REFERENCES price_lists(id) ON DELETE CASCADE,
    product_id    bigint NOT NULL REFERENCES products(id),
    unit_cents    integer NOT NULL CHECK (unit_cents >= 0),
    PRIMARY KEY (price_list_id, product_id)
);

CREATE TABLE orders (
    id           bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    customer_id  bigint NOT NULL REFERENCES customers(id),
    status       text   NOT NULL DEFAULT 'draft'
                 CHECK (status IN ('draft','placed','paid','shipped','cancelled')),
    external_ref varchar(64),               -- ERP code
    total_cents  bigint NOT NULL DEFAULT 0 CHECK (total_cents >= 0),
    created_at   timestamptz NOT NULL DEFAULT now()
);
-- only one open cart (draft) per customer: partial unique index
CREATE UNIQUE INDEX ux_orders_one_draft ON orders (customer_id) WHERE status = 'draft';
CREATE UNIQUE INDEX ux_orders_external_ref ON orders (external_ref)
    WHERE external_ref IS NOT NULL;
CREATE INDEX ix_orders_cust_created ON orders (customer_id, created_at DESC);

CREATE TABLE order_lines (
    order_id    bigint  NOT NULL REFERENCES orders(id) ON DELETE CASCADE,
    line_no     smallint NOT NULL,
    product_id  bigint  NOT NULL REFERENCES products(id),
    qty         integer NOT NULL CHECK (qty > 0),
    unit_cents  integer NOT NULL,           -- snapshot of the price at the time of the order
    vat_rate    numeric(4,2) NOT NULL,      -- snapshot of VAT
    PRIMARY KEY (order_id, line_no)
);

-- ======================================================================
-- Q: How do you delete duplicates while keeping the most recent record?
--
-- I actually did this in the ASP migration, where the email was not unique and
-- there were duplicate customers with different letter case. Before the DELETE,
-- however, I re-point the orders to the "surviving" customer (UPDATE orders SET
-- customer_id = ...), otherwise either the FK breaks or you lose history. I run
-- the SELECT first, count, and then run the DELETE inside a transaction.
-- ======================================================================

WITH ranked AS (
    SELECT id,
           ROW_NUMBER() OVER (PARTITION BY lower(email)
                              ORDER BY updated_at DESC, id DESC) AS rn
    FROM customers
)
DELETE FROM customers c
USING ranked r
WHERE c.id = r.id AND r.rn > 1;

-- and then add protection so it does not happen again
CREATE UNIQUE INDEX CONCURRENTLY ux_customers_email_lower ON customers (lower(email));