Interview Revision Lookup Sheet¶

How to use¶

Each section is a topic cluster. Use it as a lookup — pick a topic, read the what/why/how, then close it and explain it back to yourself. If you can't, you're not ready on that topic.

SECTION 1 — SOLID Principles¶

S — Single Responsibility¶

One class, one reason to change. If two different stakeholders could cause you to modify the same class, it's doing too much.

Violation: OrderManager that validates, persists, sends email, and logs.
Fix: Split into OrderValidator, OrderRepository, OrderNotifier, OrderLogger. Each changes for one reason only.

O — Open/Closed¶

Open for extension, closed for modification. Add new behaviour by adding code, not changing existing code.

Violation: if (type == "email") ... else if (type == "sms") — every new channel requires modifying this class.
Fix: INotificationChannel interface. New channel = new class implementing it. Existing code untouched.
Where it lives: Strategy pattern, Template Method pattern.

L — Liskov Substitution¶

Subclasses must be substitutable for their base class without breaking behaviour.

Classic violation: Square extends Rectangle. SetWidth on a Square also sets height — breaks the area invariant a caller expects from Rectangle.
Rule of thumb: If your subclass overrides a method and the override does something the base class contract doesn't expect, you're violating LSP.

I — Interface Segregation¶

Clients should not be forced to depend on interfaces they don't use. Fat interfaces are a smell — split them.

Violation: IWorker with Work() and Eat(). A Robot class has to implement Eat() and throw NotImplementedException.
Fix: IWorkable and IFeedable. Robot implements only IWorkable.

D — Dependency Inversion¶

High-level modules should not depend on low-level modules. Both should depend on abstractions.

Violation: InvoiceService directly instantiates SqlInvoiceRepository.
Fix: InvoiceService depends on IInvoiceRepository. The concrete SQL implementation is injected. You already do this everywhere — IDocumentStore, DefaultAzureCredential.

SECTION 2 — Design Patterns¶

Strategy¶

Swap algorithms at runtime. Family of interchangeable behaviours behind a common interface.

When to reach for it: "We have multiple ways to do X and the choice happens at runtime."
Your example: Pricing engine — different formula strategies per product type, swapped via config.
Structure: IPricingStrategy → FixedPricingStrategy, TieredPricingStrategy, PromotionalPricingStrategy. Context holds a reference to the interface and delegates to it.

Observer¶

One-to-many dependency. When one object changes state, all dependents are notified automatically.

When to reach for it: "Something happened and multiple things need to react, but the publisher shouldn't know about the subscribers."
Your example: RabbitMQ is Observer at the infrastructure level. In-process: domain events — OrderPlaced event, multiple handlers (send invoice, update stock, notify customer).
Structure: IEventPublisher, IEventHandler<T>. Publisher raises event, dispatcher routes to all registered handlers.

Factory Method¶

Create objects without specifying the exact class. The type to create depends on context.

When to reach for it: "Which concrete type I need depends on something I only know at runtime."
Example: DocumentStoreFactory — checks config for provider, returns AzureBlobDocumentStore or LocalFileDocumentStore.
Structure: IDocumentStore CreateStore(string provider) — returns the right implementation, caller never sees the concrete type.

Abstract Factory¶

Family of related objects that must be used together. Swap the whole family in one place.

When to reach for it: "I have multiple related types that need to be consistent with each other."
Example: DB provider family — IDbConnection, IDbCommand, IDbTransaction all need to be from the same provider.

Builder¶

Construct complex objects step by step. Useful when many optional parameters make constructors unwieldy.

When to reach for it: "Object construction has many optional parts and telescoping constructors are getting ugly."
Example: InvoiceBuilder.ForClient(clientId).WithLines(lines).WithDueDate(date).Build().
Note: Different from Factory — Factory decides what type to create. Builder assembles one complex object incrementally.

Decorator¶

Add behaviour to an object dynamically without subclassing. Wraps the original and adds before/after logic.

When to reach for it: "I want to add cross-cutting concerns (logging, caching, retry) without modifying the original class."
Your example: ASP.NET Core middleware pipeline is decorators. Each middleware wraps the next.
Example: CachingInvoiceRepository wraps SqlInvoiceRepository, checks cache before delegating.

State¶

Object behaviour changes based on its internal state. Instead of if (status == X) everywhere, each state is its own class.

When to reach for it: "An object behaves very differently depending on what state it's in, and state transitions have rules."
Your example: Invoice state machine — Draft, Submitted, Delivered, Failed. Each state knows which transitions are valid. invoice.Submit() behaves differently if the invoice is in Draft vs Failed.
Structure: IInvoiceState → DraftState, SubmittedState, etc. Context holds current state, delegates method calls to it.

Command¶

Encapsulate a request as an object. Enables undo, queuing, logging of operations.

When to reach for it: "I want to treat operations as objects — queue them, log them, undo them."
Example: MediatR — every request is a Command or Query object. Handler is the executor. The command object carries all the data the handler needs.

Adapter¶

Convert one interface to another. Makes incompatible interfaces work together.

When to reach for it: "I have an existing class with the right behaviour but the wrong interface."
Your example: Hexagonal Architecture — external services (Exact Online API, Blob Storage) are wrapped in adapters that implement your domain ports. Domain never sees the external SDK directly.
Structure: IDocumentStore (port) → AzureBlobDocumentStore (adapter wrapping Azure SDK).

Template Method¶

Define skeleton of an algorithm in a base class. Subclasses fill in specific steps without changing the skeleton.

When to reach for it: "Multiple classes do the same thing in the same order, but specific steps differ."
Example: BaseInvoiceProcessor — Validate() → Enrich() → Generate() → Deliver(). Subclasses override Generate() for UBL vs PDF format. The orchestration stays in the base class.

SECTION 3 — LLD Problems¶

Parking Lot¶

Entities: ParkingLot, ParkingFloor, ParkingSpot, Vehicle, Ticket, ParkingAttendant
Key decisions:

ParkingSpot is abstract — CompactSpot, LargeSpot, HandicappedSpot extend it
Vehicle is abstract — Car, Truck, Motorcycle extend it
Strategy for spot assignment — ISpotAssignmentStrategy — swap between nearest-to-entrance, random, etc.
Ticket holds entry time, spot reference. Fee calculated at exit via IFeeCalculator — Strategy pattern
ParkingLot is Singleton — one instance manages everything

SOLID mapping:

SRP: FeeCalculator is separate from Ticket
OCP: New vehicle type = new class, no existing code changes
Strategy: fee calculation, spot assignment both swappable

LRU Cache¶

Structure: Doubly linked list (DLL) + HashMap

HashMap: key → node reference, O(1) lookup
DLL: maintains access order, head = most recent, tail = least recent

Operations:

Get(key) — find in map, move node to head, return value. O(1).
Put(key, value) — if exists: update and move to head. If new: add at head, add to map. If over capacity: remove tail node, remove from map. O(1).

Why DLL over array: O(1) node removal anywhere in the list — just re-wire prev/next pointers. Array is O(n) for removal.

C# implementation skeleton:

class LRUCache {
    private int _capacity;
    private Dictionary<int, LinkedListNode<(int key, int value)>> _map;
    private LinkedList<(int key, int value)> _list;

    public int Get(int key) {
        if (!_map.ContainsKey(key)) return -1;
        var node = _map[key];
        _list.Remove(node);
        _list.AddFirst(node);
        return node.Value.value;
    }

    public void Put(int key, int value) {
        if (_map.ContainsKey(key)) {
            _list.Remove(_map[key]);
            _map.Remove(key);
        }
        if (_list.Count == _capacity) {
            _map.Remove(_list.Last.Value.key);
            _list.RemoveLast();
        }
        var node = _list.AddFirst((key, value));
        _map[key] = node;
    }
}

Vending Machine (pending drill — know the shape)¶

Entities: VendingMachine, Product, Slot, Coin, Display
Key pattern: State — IdleState, HasMoneyState, DispensingState, OutOfStockState
Each state handles: InsertCoin(), SelectProduct(), Dispense(), Refund()
VendingMachine holds current state, delegates all operations to it
Invalid operations in a given state throw or return error — enforced by the state class itself, not by if/else in the machine

SECTION 4 — HLD System Design Problems¶

Framework (apply to every problem)¶

Clarify requirements — core features, read/write heavy, scale, availability needs, global or single region
Capacity estimation — DAU → RPS, data size → storage over 5 years, read/write ratio
High level components — Client → LB → API Gateway → Services → DB. Add cache/queue/CDN where needed
Deep dive on 1-2 critical components
Non-functionals — scalability (10x traffic), availability (what fails, fallback), consistency (where eventual is acceptable)

URL Shortener¶

Key decisions:

Base62 (not Base64) — URL-safe character set
ID generation: auto-increment PK, RETURNING id, encode to Base62
Single unique constraint on alias column — not a separate alias table
301 (permanent, browser caches) vs 302 (temporary, every redirect hits your server) — 302 for analytics, 301 for performance
Redis cache-aside for hot URLs
Distributed rate limiting — Redis-backed, not in-process (in-process is per-instance)
Custom aliases: user provides slug, check uniqueness, store in same table

Twitter Home Feed¶

Core problem: User has 10M followers. Post once → fan-out to 10M feeds. Can't do this synchronously.

Fan-out on write (push model):

On post: async worker pushes tweet ID into every follower's feed (Redis Sorted Set, score = timestamp)
Read is O(1) — just range query the sorted set
Problem: 10M follower account = 10M writes per post. Write amplification.

Push/pull hybrid (celebrity threshold):

Normal users (<10K followers): fan-out on write
Celebrity accounts (>10K followers): fan-out on read — followers' feeds merge from their following list at read time
Threshold is tunable

Feed storage: Redis Sorted Set per user. ZREVRANGE user:feed:userId 0 19 = latest 20 tweets.
Pagination: Cursor-based (last seen tweet ID), not offset. Offset breaks when new tweets insert above.

Notification System¶

Three delivery mechanisms:

WebSocket — live, user is online
Push notification (FCM/APNs) — mobile, user background
Email/SMS — async, fallback

Fan-out at scale:

Small account: fan-out on write, push to all subscribers immediately
Large account (subreddit-scale): don't fan-out to 50M subscribers synchronously. Queue the event, consumers process in batches. Pull on read for active users.

Delivery guarantees: At-least-once. Idempotency on the consumer side — track notification ID, skip if already delivered.

Ecommerce Ordering / Saga¶

Why not 2PC: Locks resources across services until all commit. One slow service blocks the whole transaction. Not practical at scale.

Saga orchestrator:

Orchestrator service drives the workflow: reserve stock → charge payment → confirm order
Each step has a compensating transaction: release stock if payment fails

Redis stock gate:

Before hitting the DB, check Redis for available stock
Lua script for atomicity: if stock >= qty then stock -= qty; return 1 else return 0 end
Why Lua: Redis executes Lua atomically — no race condition between check and decrement

Indian payment gateway flow:

Browser leaves your domain, redirects to gateway (Razorpay, PayU)
Gateway handles card input and bank auth
On completion, gateway redirects back to your callback URL with payment token
Your server verifies token with gateway API, then confirms order

Rate Limiter¶

Token bucket: Bucket holds max N tokens, refills at rate R per second. Request consumes 1 token. Allows bursts up to N. Most common for API rate limiting.

Sliding window log: Store timestamp of each request. On new request, remove timestamps older than window, count remaining. If count < limit, allow. Accurate but memory-heavy.

Fixed window counter: Increment counter per window. Simple, but spike at window boundary — N requests at 11:59:59 + N requests at 12:00:00 = 2N in 2 seconds.

Leaky bucket: Queue requests, process at fixed rate. Smooths output. No burst. Good for metered APIs.

Distributed rate limiting: Counter must be in Redis, not in-process. In-process = per-instance, 10 instances = 10x your limit effectively.

Lua for check-and-decrement:

local count = redis.call('INCR', KEYS[1])
if count == 1 then redis.call('EXPIRE', KEYS[1], ARGV[1]) end
if count > tonumber(ARGV[2]) then return 0 end
return 1

Atomic — no race between check and increment.

Building Blocks Quick Reference¶

Block	Use when
Load Balancer	More than one service instance. Round robin default, consistent hashing for stateful routing
API Gateway	Entry point for all clients. Auth, rate limiting, routing, SSL termination
Cache (Redis)	Read-heavy, infrequently changing data. Cache-aside is the default pattern
CDN	Static assets, global users, reduce origin load
Message Queue	Async, spike absorption, fan-out, decoupling producers from consumers
Read Replica	Read-heavy DB. Accept eventual consistency on reads or implement LSN tracking
Sharding	Single DB can't hold the data. Shard key selection is the hardest part — avoid hotspots
Consistent Hashing	Minimise redistribution when nodes added/removed. Virtual nodes for even distribution

SECTION 5 — Distributed Systems Theory¶

CAP Theorem¶

C = Consistency: every read returns the most recent write
A = Availability: every request gets a response (not an error)
P = Partition tolerance: system works despite network failures between nodes

P is non-negotiable. Network partitions happen. Real choice is CP vs AP.

CP examples: ZooKeeper, HBase, your PEPPOL platform (reject > wrong invoice delivered)
AP examples: Cassandra default (ONE consistency level), DynamoDB default mode, DNS

CAP is per-operation, not per-system. Your system can be CP for writes and AP for reads. Labelling an entire system "CP" is imprecise.

PACELC extends CAP: when no partition (normal operation), you still face Latency vs Consistency tradeoff. Most real decisions are PACELC decisions, not CAP decisions.

Consistency Models (spectrum)¶

Model	What it guarantees	Example
Linearizable (strong)	Every read returns most recent write. Global ordering.	Reading from primary only
Read-your-own-writes	You always see your own writes, others may not	LSN tracking in Redis
Causal	Causally related ops seen in order by all	Vector clocks, causal sessions
Eventual	All replicas converge eventually, no timing guarantee	Async replica reads, DNS

Your LSN implementation:

On write: capture LSN from PostgreSQL, store in Redis keyed by user ID with TTL
On read: check Redis for user's write LSN, route to replica only if replica LSN >= write LSN
Gap: silently partitioned alive replica serves stale data past Redis TTL — needs primary heartbeat check to close

ACID¶

Atomicity: All operations in a transaction succeed or all roll back. No partial commits.
Consistency: DB moves from one valid state to another. Constraints, foreign keys, invariants enforced.
Isolation: Concurrent transactions don't see each other's intermediate state. Levels: Read Uncommitted → Read Committed → Repeatable Read → Serializable.
Durability: Committed data survives crash. Enforced via WAL + fsync to disk.

Isolation levels in practice:

Level	What it prevents
Read Uncommitted	Nothing — dirty reads allowed
Read Committed	Dirty reads. Default in PostgreSQL.
Repeatable Read	Dirty reads + non-repeatable reads
Serializable	All anomalies. Transactions execute as if serial.

ACID breaks in distributed systems. Across microservices, a single ACID transaction is not possible. Saga is the replacement.

Saga Pattern¶

Why: 2PC (two-phase commit) across services locks resources until all participants commit. One slow service blocks everything. Not practical at scale.

Choreography: Each service publishes events, others react. No central coordinator. Harder to trace, emergent behaviour.

Orchestration: Central orchestrator service drives the workflow. Tells each service what to do. Easier to reason about, single point of failure.

Compensating transactions: If step 3 fails, undo steps 1 and 2 by running compensating operations (release reserved stock, refund charge). Compensations must also be idempotent.

Outbox Pattern¶

Problem: You can't atomically write to your DB and publish to RabbitMQ in one operation. If DB commits and publish fails, you've lost the event.

Solution:

In the same DB transaction as your domain write, insert the event into an outbox table
A relay process polls the outbox, publishes events to RabbitMQ, marks them as published
If the relay crashes, it re-publishes on restart — consumers handle duplicates via idempotency

Guarantees: At-least-once delivery. Exactly-once processing is the consumer's responsibility.

Quorum (Cassandra model)¶

N = total replicas, W = writes that must confirm, R = reads that must confirm
W + R > N guarantees at least one node has the latest write
W=1, R=1 (Cassandra default ONE): AP, fastest, stale reads possible
W=QUORUM, R=QUORUM: CP, consistent reads, higher latency
ALL: CP, maximum consistency, lowest availability

LOCAL_QUORUM: Quorum within one data centre only — avoids cross-DC latency while maintaining consistency locally.

Replication Mechanics (PostgreSQL)¶

Primary writes to WAL (Write-Ahead Log)
Replica streams WAL and replays it
LSN (Log Sequence Number) tracks position
Async replication (default): Primary acks write before replica confirms. Replica can lag.
Sync replication: Primary blocks until at least one replica confirms. No lag, but write latency increases. Primary stalls if designated sync replica goes down.

Read replica routing in .NET: Two connection strings — primary for writes (EF Core), replica endpoint for reads (Dapper). Application decides which to use per operation.

SECTION 6 — C# / .NET Internals¶

async/await¶

The compiler transforms an async method into a state machine. await is a yield point — the current thread is released back to the thread pool while waiting. When the awaited task completes, execution resumes (possibly on a different thread).

ConfigureAwait(false):

By default, after await, execution tries to resume on the original SynchronizationContext (e.g. UI thread, ASP.NET request context)
ConfigureAwait(false) tells the runtime: don't capture the context, resume on any available thread
Use in library code — libraries don't need UI context and avoiding capture improves performance
In ASP.NET Core there is no SynchronizationContext — ConfigureAwait(false) has no functional effect but is still a good habit in library code

Deadlock scenario (classic):

// UI thread calls this
var result = GetDataAsync().Result; // blocks UI thread

async Task<string> GetDataAsync() {
    await Task.Delay(1000); // tries to resume on UI thread — but UI thread is blocked
    return "data";
}

Fix: await all the way up, never .Result or .Wait(). Or ConfigureAwait(false) in the async method.

CancellationToken: Pass through every async call in the chain. Check token.ThrowIfCancellationRequested() at natural checkpoints. If ignored, the operation completes even after the caller has moved on — wastes resources.

DI Lifetimes¶

Lifetime	Created	Destroyed
Singleton	Once, app start	App shutdown
Scoped	Once per HTTP request	End of request
Transient	Every time resolved	When scope/container disposes

Captive dependency: Singleton captures a Scoped service. The Scoped service is now effectively Singleton — it never gets disposed. DbContext as a Singleton is the classic bug — connection held open, stale state across requests.

Detection: ASP.NET Core validates this on startup in development mode — ValidateScopes = true in UseDefaultServiceProvider. In production it's silent and wrong.

IEnumerable vs IQueryable¶

IEnumerable: In-memory. LINQ operators are extension methods on objects already loaded. Filtering happens in C#.
IQueryable: Expression tree. EF Core translates LINQ to SQL. Filtering happens in the database.

.ToList() too early: dbContext.Orders.ToList().Where(o => o.Status == "paid") — loads ALL orders into memory, then filters in C#. Should be dbContext.Orders.Where(o => o.Status == "paid").ToList().

.ToList() too late (N+1):

var orders = dbContext.Orders.Where(...); // IQueryable, not executed yet
foreach (var order in orders) {
    // Each iteration hits the DB
    var lines = dbContext.OrderLines.Where(l => l.OrderId == order.Id).ToList();
}

Fix: Include(o => o.Lines) or batch load lines separately.

Deferred execution: The query doesn't run until you iterate (foreach, ToList(), First(), Count()). Each enumeration re-runs the query unless materialised.

EF Core¶

Change tracking: EF Core tracks every entity returned from a query. On SaveChanges(), it diffs tracked entities against their original state and generates UPDATE statements. Expensive for large read-only queries.

AsNoTracking(): Disables change tracking for a query. Use for all read-only paths. On your read side (Dapper for reads), this isn't relevant — Dapper has no tracker.

Optimistic concurrency:

// PostgreSQL: use xmin system column as rowversion
modelBuilder.Entity<Invoice>()
    .UseXminAsConcurrencyToken();

On SaveChanges(), EF Core adds WHERE xmin = @originalXmin to the UPDATE. If another process updated the row, xmin changed, rowcount = 0, EF throws DbUpdateConcurrencyException. Caller catches and retries or surfaces conflict.

OAuth2 / OpenID Connect¶

Authorization Code Flow (what you built):

Client redirects user to auth server with client_id, redirect_uri, scope, state
User authenticates, auth server issues authorization code, redirects to redirect_uri
Client exchanges code for tokens (POST to /token with code, client_secret)
Auth server returns access_token, id_token (OIDC), refresh_token

JWT structure: header.payload.signature

Header: algorithm (RS256)
Payload: claims (sub, iss, aud, exp, custom claims)
Signature: signed with auth server's private key

What the signature proves: This token was issued by the auth server that holds the private key. The payload hasn't been tampered with.
What it doesn't prove: The token hasn't been stolen. Token theft = valid signature on a stolen token.

Refresh token: Short-lived access token (15 min). Long-lived refresh token (days/weeks). Client uses refresh token to get new access token without user re-authenticating. Rotation: each use issues a new refresh token, old one invalidated.

SECTION 7 — SQL / DB Design (Quick Reference)¶

Execution Order¶

FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY

Window functions run after WHERE/GROUP BY, before ORDER BY. Column aliases from SELECT not available in HAVING.

Window Function Patterns¶

-- Running total
SUM(amount) OVER (PARTITION BY customer_id ORDER BY order_date
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)

-- 7-day moving average
AVG(price) OVER (PARTITION BY ticker ORDER BY price_date
    ROWS BETWEEN 6 PRECEDING AND CURRENT ROW)

-- Top N per group
WITH ranked AS (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date DESC) AS rn
    FROM orders
)
SELECT * FROM ranked WHERE rn <= 3

-- Compare to previous row
LAG(amount) OVER (PARTITION BY account_id ORDER BY created_at)
-- First row LAG = NULL. Handle explicitly.

-- LAST_VALUE needs explicit full frame
LAST_VALUE(col) OVER (PARTITION BY ... ORDER BY ...
    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)

Gaps and Islands¶

ROW_NUMBER subtraction — consecutive rows, gap = broken streak:

WITH numbered AS (
    SELECT user_id, login_date,
           ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY login_date) AS rn
    FROM logins
)
SELECT user_id,
       login_date - INTERVAL rn DAY AS grp,
       MIN(login_date), MAX(login_date), COUNT(*)
FROM numbered
GROUP BY user_id, grp

If table has non-qualifying rows (e.g. temperatures below threshold), filter first — filter induces the gaps the subtraction detects.

LAG + running SUM — boundary is a status change or time gap:

WITH flagged AS (
    SELECT *,
           LAG(status) OVER (PARTITION BY device_id ORDER BY event_time) AS prev_status
    FROM events
),
sessioned AS (
    SELECT *,
           SUM(CASE WHEN prev_status = 'off' OR prev_status IS NULL THEN 1 ELSE 0 END)
               OVER (PARTITION BY device_id ORDER BY event_time
                    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS session_id
    FROM flagged
)
SELECT device_id, session_id, MIN(event_time), MAX(event_time)
FROM sessioned
GROUP BY device_id, session_id

Four variants:

Problem	Boundary	Approach
Login streaks	Gap in consecutive dates	ROW_NUMBER subtraction
Event sessions	status = 'off'	LAG + running SUM
Sensor periods	Drop below threshold	Filter first, then ROW_NUMBER subtraction
Transaction windows	Gap > 30 minutes	LAG + TIMESTAMPDIFF boundary flag

Normalization¶

Form	Eliminates
1NF	Non-atomic values, repeating groups
2NF	Partial dependencies on composite PK
3NF	Transitive dependencies (non-key → non-key)
BCNF	Edge cases 3NF misses — every determinant is a candidate key

Intentional denormalization is correct: Invoice snapshots of customer name/address are right. Point-in-time legal record. You don't want the invoice changing if the customer updates their profile.

Indexing¶

Composite index column order:

Equality filters first, highest cardinality first among them
Range filter next (only one range filter per composite index)
ORDER BY columns last — if they match the index tail, sort is free

Left-prefix rule: Index on (a, b, c) serves queries on a, (a,b), (a,b,c). Not b alone or c alone.

Low selectivity: Index on a boolean or 2-value status column won't be used — planner does full scan instead.

B-tree: Sorted tree, O(log n) traversal. Works for equality, range, ORDER BY, prefix matching. Default index type.

Recursive CTEs¶

WITH RECURSIVE cte AS (
    SELECT employee_id, name, manager_id, 0 AS depth
    FROM employees WHERE manager_id IS NULL   -- anchor
    UNION ALL
    SELECT e.employee_id, e.name, e.manager_id, cte.depth + 1
    FROM employees e JOIN cte ON e.manager_id = cte.employee_id   -- recursive
)
SELECT * FROM cte

Use for: org charts, category trees, bill of materials, folder hierarchies. Stops when recursive part returns no rows.

SECTION 8 — Resume Claims: Numbers to Know Cold¶

Claim	Before	After
EPOS cold start	40s	6s
Offline file generation (14K products)	10 mins	8s
DB round trips (offline file gen)	4 per product	3 per batch of 500
Pricing engine	5s	100ms
Description engine	2s	50ms
Order processing	4s	50ms
Invoice generation	3s	50ms
Image upload (Azure Function)	3s	<300ms
PEPPOL delivery rate	—	99.86% same-day
PEPPOL volume	—	7K invoices/day, 32 clients
ERP satellite sync	—	50K events/day, 15 clients

SECTION 9 — Architecture Patterns (Skills Section Claims)¶

DDD¶

Aggregate root: Only entry point for mutations on that aggregate. Enforces invariants. Nothing reaches inside the aggregate except through the root.
Value object vs entity: Entity has identity (survives state changes, identified by ID). Value object is defined entirely by its values — two value objects with same values are equal (Money(100, "USD") == Money(100, "USD")).
Domain event: Something that happened in the domain. Published after the aggregate mutates. Handlers react to it (send email, update read model, trigger saga step).

CQRS¶

Write path: Command → Handler → Aggregate → EF Core → DB (primary). Reads the aggregate to hydrate it, then persists changes.
Read path: Query → Handler → Dapper → DB (replica). Returns a DTO shaped for the consumer. No aggregate, no EF Core overhead.
Why the split: Aggregates optimised for consistency and invariant enforcement, not for query shapes. Read models optimised for the query, not for mutation.

Ports and Adapters (Hexagonal)¶

Port: Interface owned by the domain. The domain defines what it needs, not how it's delivered.
Adapter: Implements the port. Lives outside the domain. Wires the port to infrastructure (DB, blob storage, external API, RabbitMQ).
Why: Domain has zero infrastructure dependencies. Swap Azure Blob for S3 = new adapter, domain unchanged. Test domain logic with in-memory adapters.

Context-First Model (LTCP)¶

Load: Two batch queries regardless of batch size. Load everything the compute layer will need upfront.
Transform: Build a lean in-memory context object from the loaded data. No DB calls after this point.
Compute: Pure function. No DB awareness. Takes the context object, produces results. Own DI boundary — designed for future extraction as a standalone service.
Persist: Batch write results. No per-item DB round trips.

Why it works: Eliminates N+1 at the architectural level, not just the query level. The compute layer is provably free of DB calls by design, not by convention.

SECTION 10 — RabbitMQ¶

Exchange Types¶

Type	Routing	Use when
Direct	Exact routing key match	Point-to-point, commands
Topic	Pattern match (`order.#`, `*.created`)	Event routing with wildcards
Fanout	All bound queues, no routing key	Broadcast to all consumers
Headers	Message headers	Rarely used

Publisher Confirms¶

Broker sends ack when message is persisted to disk (for durable queues)
Does NOT mean consumer processed it — only that broker has it safely
Without confirms: fire-and-forget, message loss possible on broker crash
Async confirms: publish continuously, handle acks/nacks in a callback

Consumer ACK Modes¶

Auto ACK: Message removed from queue the moment it's delivered. If consumer crashes mid-processing, message is lost.
Manual ACK: Consumer explicitly calls basicAck after processing. If consumer crashes before ACK, broker requeues the message.
Always use manual ACK for anything that matters.

Retry Topology¶

Exchange → Queue → Consumer
                ↓ (on failure, republish with TTL)
           Retry Queue (TTL = 30s) → dead-letters back to main exchange
                ↓ (after N attempts)
           Dead Letter Queue (DLQ)

x-dead-letter-exchange on retry queue routes expired messages back to the main exchange
Consumer tracks attempt count (message header), moves to DLQ after N attempts
DLQ is inspected manually or by an ops process

Idempotent Consumer¶

On PEPPOL: store processed message ID in DB before processing. On receive: check if ID already processed — if yes, ACK and skip. Ensures duplicate deliveries (broker retry, consumer crash) don't cause duplicate invoice submissions.

SECTION 11 — Docker Swarm / Azure Infrastructure¶

Swarm vs AKS¶

	Docker Swarm	AKS
Complexity	Low	High
Portability	Same config on VPS or Azure	Azure-specific
Features	Core orchestration	Full Kubernetes ecosystem
Ops overhead	Low	Higher

Your choice: Swarm — cloud-agnostic, same stack works on VPS, no Kubernetes complexity justified at current scale.

Networking Layers (two separate things)¶

Azure VNet: VM-level isolation. Controls which VMs can talk to each other. NSG rules on the subnet.
Docker overlay network: Container-level networking within Swarm. Containers across VMs communicate via encrypted VXLAN tunnel on top of the VNet.

They're not competing — VNet secures the VM layer, overlay network handles container-to-container routing.

Service Principal vs Managed Identity¶

Managed Identity: Azure assigns an identity to the VM. Containers on that VM inherit it via IMDS endpoint. Simple but containers don't reliably get VM identity in all setups.
Service Principal: Explicit credentials (tenant_id, client_id, client_secret). Works anywhere — Azure VMs, VPS, local dev. Your choice for cloud-agnostic deployments.
DefaultAzureCredential resolution order: Environment → Workload Identity → Managed Identity → Azure CLI → ...
Known issue: first request ~19s on Azure because DefaultAzureCredential tries Managed Identity first, IMDS call times out. Fix: DefaultAzureCredentialOptions to exclude unused credential providers.

CI/CD Flow¶

Push to master
→ GitHub Actions triggered
→ Login to GHCR with PAT (GHCR_TOKEN secret)
→ Build image, push :latest and :<sha> tags
→ appleboy/ssh-action: SSH into manager VM
→ docker service update --with-registry-auth --image ...:latest aver_api
→ Swarm rolling update: new container starts, health check passes, old container removed

Stack file changes still require manual docker stack deploy — pipeline only updates the image.

SECTION 12 — Problem-Type Lookup¶

If asked about...	Reach for...
Multiple algorithms for same problem	Strategy pattern
One thing happening → many things react	Observer / domain events
Object creation based on config/context	Factory
Add logging/caching without modifying class	Decorator
Object behaves differently per state	State pattern
Tree structure, same operations on leaf and branch	Composite
Incompatible interface from external lib	Adapter
Complex object with many optional params	Builder
Same algorithm skeleton, different steps	Template Method
N-day moving average	Sliding window frame
Top N per group	ROW_NUMBER + CTE filter
Consecutive streak	ROW_NUMBER subtraction
Session grouping from events	LAG + running SUM
System design: social feed	Fan-out on write + push/pull hybrid
System design: notifications	Three-mechanism delivery + tiered fan-out
System design: rate limiting	Token bucket + Redis atomic Lua
Dual write problem (DB + message broker)	Outbox pattern
Cross-service transaction	Saga + compensating transactions
Read your own writes in stateless REST	LSN tracking in Redis
Concurrent update conflict	Optimistic concurrency + xmin
Singleton capturing Scoped service	Captive dependency bug
Query runs too early / too late	IEnumerable vs IQueryable