-- INNER JOIN: only rows with a match in BOTH tables
SELECT o.id, c.name, o.total
FROM orders o
INNER JOIN customers c ON o.customer_id = c.id;

-- LEFT JOIN: all rows from left, NULLs where no match on right
-- Use to find customers who have NEVER placed an order
SELECT c.name, o.id AS order_id
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id
WHERE o.id IS NULL;  -- anti-join pattern

-- FULL OUTER JOIN: all rows from both, NULLs where no match
-- Rarely used — good for reconciliation / diff reports

-- CROSS JOIN: cartesian product — every row × every row
-- Generates all possible driver-vehicle combinations for assignment
SELECT d.name, v.plate
FROM drivers d CROSS JOIN vehicles v;

-- Find each employee and their manager (same employees table)
SELECT e.name AS employee, m.name AS manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.id;

-- Find consecutive GPS events for the same vehicle (gap detection)
SELECT a.vehicle_id, a.recorded_at AS t1, b.recorded_at AS t2,
       b.recorded_at - a.recorded_at AS gap
FROM gps_events a
JOIN gps_events b ON a.vehicle_id = b.vehicle_id
                  AND b.recorded_at = (
  SELECT MIN(recorded_at) FROM gps_events
  WHERE vehicle_id = a.vehicle_id AND recorded_at > a.recorded_at
);

-- Running total of fuel cost per vehicle (window SUM)
SELECT vehicle_id, recorded_at, fuel_cost,
       SUM(fuel_cost) OVER (
         PARTITION BY vehicle_id
         ORDER BY recorded_at
         ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
       ) AS running_total
FROM fuel_events;

-- Compare each GPS ping speed to the previous ping (LAG)
SELECT vehicle_id, recorded_at, speed,
       LAG(speed) OVER (PARTITION BY vehicle_id ORDER BY recorded_at) AS prev_speed,
       speed - LAG(speed) OVER (PARTITION BY vehicle_id ORDER BY recorded_at) AS delta
FROM gps_events;

-- RANK vs ROW_NUMBER vs DENSE_RANK
-- ROW_NUMBER: unique 1,2,3,4 (no ties)
-- RANK:       ties get same rank, next rank skips: 1,1,3
-- DENSE_RANK: ties same rank, next rank doesn't skip: 1,1,2

-- Recursive CTE: all vehicles in a fleet hierarchy
WITH RECURSIVE fleet_tree AS (
  SELECT id, name, parent_fleet_id, 1 AS level
  FROM fleets WHERE parent_fleet_id IS NULL
  UNION ALL
  SELECT f.id, f.name, f.parent_fleet_id, ft.level + 1
  FROM fleets f
  JOIN fleet_tree ft ON f.parent_fleet_id = ft.id
)
SELECT * FROM fleet_tree ORDER BY level, name;

-- Standard aggregates
SELECT fleet_id,
       COUNT(*) AS total_vehicles,
       AVG(speed) AS avg_speed,
       MAX(speed) AS top_speed,
       PERCENTILE_CONT(0.95) WITHIN GROUP (ORDER BY speed) AS p95_speed
FROM gps_events
GROUP BY fleet_id;

-- GROUPING SETS: multiple GROUP BY levels in one query
SELECT fleet_id, vehicle_id, COUNT(*) AS events
FROM gps_events
GROUP BY GROUPING SETS (
  (fleet_id, vehicle_id),  -- per vehicle subtotal
  (fleet_id),              -- per fleet subtotal
  ()                       -- grand total
);

-- FILTER clause on aggregates (cleaner than CASE WHEN)
SELECT
  COUNT(*) FILTER (WHERE speed > speed_limit) AS speeding_events,
  COUNT(*) FILTER (WHERE speed = 0)          AS idle_events,
  COUNT(*) AS total_events
FROM gps_events
WHERE fleet_id = 42;

-- Full diagnosis mode
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT v.plate, COUNT(*) AS events
FROM gps_events g
JOIN vehicles v ON g.vehicle_id = v.id
WHERE g.recorded_at > NOW() - INTERVAL '7 days'
GROUP BY v.plate;

-- What to look for in the output:
-- Seq Scan on gps_events → missing index on recorded_at
-- rows=1000 (estimated) vs rows=50000 (actual) → stale stats, run ANALYZE
-- Buffers: shared hit=0, read=50000 → data not cached, cold I/O is the bottleneck
-- Hash Batches: 4 → hash doesn't fit in work_mem, spilling to disk

-- Fix stale statistics
ANALYZE gps_events;

-- Increase work_mem for sort/hash operations (session-level)
SET work_mem = '256MB';

-- 1. Function on indexed column (index bypassed)
WHERE DATE_TRUNC('month', recorded_at) = '2024-01-01'  -- BAD
WHERE recorded_at >= '2024-01-01' AND recorded_at < '2024-02-01'  -- GOOD

-- 2. Implicit type cast
WHERE vehicle_id = '42'   -- vehicle_id is INT, '42' is TEXT → cast prevents index
WHERE vehicle_id = 42     -- GOOD

-- 3. Leading wildcard
WHERE plate LIKE '%ABC'   -- can't use B-Tree, full scan
WHERE plate LIKE 'ABC%'   -- can use B-Tree index

-- 4. IS NULL / IS NOT NULL (often OK but check cardinality)
-- 5. OR across different columns (use UNION instead)
WHERE fleet_id = 1 OR driver_id = 99  -- may not use indexes well
-- Rewrite as:
SELECT * FROM vehicles WHERE fleet_id = 1
UNION
SELECT * FROM vehicles WHERE driver_id = 99

SELECT vehicle_id, ROUND(AVG(speed), 2) AS avg_speed
FROM   gps_events
WHERE  recorded_at >= NOW() - INTERVAL '30 days'
  AND  speed > 0
GROUP  BY vehicle_id
ORDER  BY avg_speed DESC
LIMIT  10;

-- Composite index: column ORDER matters
-- (fleet_id, recorded_at) serves: WHERE fleet_id=X AND recorded_at > Y
-- Does NOT efficiently serve: WHERE recorded_at > Y (no fleet_id prefix)
CREATE INDEX idx_gps_fleet_time ON gps_events(fleet_id, recorded_at DESC);

-- Covering index: INCLUDE avoids a second heap lookup (index-only scan)
CREATE INDEX idx_gps_cover
  ON gps_events(vehicle_id, recorded_at)
  INCLUDE (speed, lat, lng);  -- dashboard queries get everything from index

-- Partial index: smaller, faster, specific
CREATE INDEX idx_active_alerts ON alerts(vehicle_id, triggered_at)
  WHERE resolved_at IS NULL;  -- only unresolved alerts indexed

-- Expression index: index the computation, not the raw column
CREATE INDEX idx_plate_lower ON vehicles(LOWER(plate));
-- Now WHERE LOWER(plate) = 'sba1234a' uses the index

-- Non-blocking index creation (never locks table)
CREATE INDEX CONCURRENTLY idx_gps_speed ON gps_events(speed)
  WHERE speed > 0;

-- One-to-Many: one fleet → many vehicles
vehicles (id, fleet_id REFERENCES fleets(id) ON DELETE CASCADE)

-- Many-to-Many: drivers ↔ vehicles over time (junction table)
driver_vehicle_assignments (
  id           BIGSERIAL PRIMARY KEY,
  driver_id    INT NOT NULL REFERENCES drivers(id),
  vehicle_id   INT NOT NULL REFERENCES vehicles(id),
  assigned_at  TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  unassigned_at TIMESTAMPTZ,           -- NULL = currently assigned
  CONSTRAINT no_overlap EXCLUDE USING gist (
    vehicle_id WITH =,
    tstzrange(assigned_at, unassigned_at) WITH &&  -- no overlapping time ranges
  )
);

-- ON DELETE options
-- CASCADE:  deleting fleet deletes all vehicles
-- RESTRICT: error if fleet has vehicles (default-ish)
-- SET NULL: vehicle.fleet_id becomes NULL
-- NO ACTION: like RESTRICT but deferred until end of transaction

-- Multi-tenancy boundary
customers   (id, name, plan, created_at)
fleets      (id, customer_id, name)              -- one customer → many fleets

-- Core operational entities
vehicles    (id, fleet_id, plate, make, model, year, vin, status)
drivers     (id, fleet_id, name, licence_no, licence_expiry, status)

-- Many-to-many assignment with time tracking
driver_vehicle_assignments (
  id, driver_id, vehicle_id, assigned_at, unassigned_at
)

-- Trips reconstructed from GPS stream
trips (id, vehicle_id, driver_id, start_time, end_time,
       distance_km, max_speed, avg_speed, fuel_litres,
       start_lat, start_lng, end_lat, end_lng)

-- High-volume: PARTITION BY RANGE(recorded_at) monthly
gps_events (id, vehicle_id, trip_id, recorded_at,
            lat, lng, speed, heading, odometer, ignition)

-- Flexible alert payload with JSONB
alerts (id, vehicle_id, driver_id, alert_type, severity,
        triggered_at, resolved_at,
        payload JSONB)   -- e.g. {"geofence_id": 5, "zone": "depot"}

-- Set isolation level for a transaction
BEGIN ISOLATION LEVEL REPEATABLE READ;
  SELECT balance FROM accounts WHERE id = 1;  -- snapshot taken here
  SELECT balance FROM accounts WHERE id = 1;  -- same result even if updated
COMMIT;

-- Pessimistic locking: lock rows you intend to update
BEGIN;
  SELECT * FROM accounts WHERE id = 1 FOR UPDATE;  -- locks the row
  UPDATE accounts SET balance = balance - 100 WHERE id = 1;
COMMIT;

-- Optimistic locking: use version column, check before update
UPDATE vehicles
SET status = 'inactive', version = version + 1
WHERE id = 42 AND version = 7;  -- fails if someone else updated first
-- Check affected rows = 0 → conflict, retry

-- Check for table bloat (high dead tuples = vacuum not keeping up)
SELECT relname,
       n_live_tup, n_dead_tup,
       ROUND(n_dead_tup::numeric / NULLIF(n_live_tup + n_dead_tup, 0) * 100, 1) AS dead_pct,
       last_autovacuum,
       last_autoanalyze
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC;

-- Manual vacuum (non-blocking, runs in background)
VACUUM gps_events;

-- VACUUM FULL: rewrites table, reclaims disk space — but locks table!
-- Use pg_repack extension instead for zero-downtime compaction

-- Force autovacuum to run sooner on a specific table
ALTER TABLE gps_events SET (autovacuum_vacuum_scale_factor = 0.01);

CREATE TABLE gps_events (
  id          BIGSERIAL,
  vehicle_id  INT NOT NULL,
  recorded_at TIMESTAMPTZ NOT NULL,
  lat NUMERIC(9,6), lng NUMERIC(9,6), speed NUMERIC
) PARTITION BY RANGE(recorded_at);

CREATE TABLE gps_2024_01 PARTITION OF gps_events
  FOR VALUES FROM('2024-01-01') TO('2024-02-01');

-- Drop old partition instantly — no DELETE, no VACUUM needed
DROP TABLE gps_2023_01;  -- instant, removes millions of rows in milliseconds

-- LIST partitioning: by category
PARTITION BY LIST(region);  -- 'APAC', 'EMEA', 'Americas'

-- HASH partitioning: distribute write load evenly
PARTITION BY HASH(customer_id);  -- 8 equal-sized buckets

-- Arrow operators for extraction
SELECT payload->'geofence_id'         -- returns JSON type
SELECT payload->>'geofence_id'        -- returns TEXT (better for comparison)
SELECT payload#>>ARRAY['location','city'] -- nested path

-- Containment operator @> (uses GIN index)
SELECT * FROM alerts
WHERE payload @> '{"alert_type":"speeding","severity":"high"}';

-- GIN index makes @> very fast even on millions of rows
CREATE INDEX idx_alerts_payload ON alerts USING GIN(payload);

-- Update a specific key without replacing the whole document
UPDATE alerts
SET payload = payload || '{"acknowledged": true}'
WHERE id = 99;

-- Remove a key
UPDATE alerts SET payload = payload - 'temp_debug_field';

-- Generated column: computed from other columns, stored on disk
ALTER TABLE gps_events
ADD COLUMN speed_kmh NUMERIC GENERATED ALWAYS AS (speed * 3.6) STORED;
-- Can be indexed, queried like a normal column

-- Trigger: auto-update updated_at on any row change
CREATE OR REPLACE FUNCTION set_updated_at()
RETURNS TRIGGER AS $$
BEGIN
  NEW.updated_at = NOW();
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER vehicles_updated_at
  BEFORE UPDATE ON vehicles
  FOR EACH ROW EXECUTE FUNCTION set_updated_at();

-- Row Level Security: enforce tenant isolation at DB level
ALTER TABLE vehicles ENABLE ROW LEVEL SECURITY;
CREATE POLICY fleet_isolation ON vehicles
  USING (fleet_id IN (
    SELECT id FROM fleets
    WHERE customer_id = current_setting('app.current_customer_id')::INT
  ));

-- Enable PostGIS on the database (run once as superuser)
CREATE EXTENSION postgis;
CREATE EXTENSION postgis_topology;   -- optional: topological relationships
SELECT PostGIS_Version();             -- verify: '3.4.0 ...'

-- Vehicle positions table (using GEOGRAPHY for accurate global distances)
CREATE TABLE vehicle_positions (
  id          BIGSERIAL PRIMARY KEY,
  vehicle_id  INT NOT NULL REFERENCES vehicles(id),
  recorded_at TIMESTAMPTZ NOT NULL,
  position    GEOGRAPHY(POINT, 4326) NOT NULL,   -- lon/lat in WGS 84
  speed_kmh   NUMERIC(5,1),
  heading     SMALLINT                            -- 0-359 degrees
);

-- Geofences table (using GEOMETRY for faster polygon operations)
CREATE TABLE geofences (
  id          SERIAL PRIMARY KEY,
  fleet_id    INT NOT NULL REFERENCES fleets(id),
  name        TEXT NOT NULL,
  zone_type   TEXT,                              -- 'depot', 'restricted', 'delivery'
  boundary    GEOMETRY(POLYGON, 4326) NOT NULL,  -- or MULTIPOLYGON for complex zones
  created_at  TIMESTAMPTZ DEFAULT NOW()
);

-- GiST spatial index — makes spatial queries fast
CREATE INDEX idx_positions_geog  ON vehicle_positions USING GiST(position);
CREATE INDEX idx_geofences_boundary ON geofences USING GiST(boundary);
CREATE INDEX idx_positions_fleet ON vehicle_positions(vehicle_id, recorded_at DESC);

-- Insert a GPS ping (vehicle position from device)
INSERT INTO vehicle_positions (vehicle_id, recorded_at, position, speed_kmh)
VALUES (42, NOW(), ST_MakePoint(103.8198, 1.3521)::geography, 65.5);
-- ST_MakePoint(longitude, latitude) — note: lon FIRST, lat SECOND

-- Distance between two points (returns metres when using GEOGRAPHY)
SELECT ST_Distance(
  ST_MakePoint(103.8198, 1.3521)::geography,  -- vehicle position
  ST_MakePoint(103.8500, 1.2800)::geography   -- depot location
) AS distance_metres;
-- Returns: 8432.7 (metres)

-- Point-in-polygon: is a vehicle inside a geofence?
SELECT g.name, g.zone_type
FROM geofences g
WHERE ST_Within(
  ST_MakePoint(103.8198, 1.3521)::geometry,  -- vehicle position
  g.boundary                                   -- geofence polygon
)
AND g.fleet_id = 7;

-- Alternative: ST_Contains(polygon, point) — same result, different argument order
WHERE ST_Contains(g.boundary, ST_MakePoint(103.82, 1.35)::geometry)

-- All vehicles within 500 metres of a depot
SELECT v.plate, vp.speed_kmh,
       ST_Distance(vp.position, depot.location) AS distance_m
FROM vehicle_positions vp
JOIN vehicles v ON v.id = vp.vehicle_id
JOIN depots depot ON depot.id = 5
WHERE ST_DWithin(vp.position, depot.location, 500)  -- uses GiST index efficiently
AND vp.recorded_at > NOW() - INTERVAL '30 seconds';

-- Create a circular geofence (200m radius around a depot)
INSERT INTO geofences (fleet_id, name, zone_type, boundary)
VALUES (7, 'Main Depot', 'depot',
  ST_Buffer(
    ST_MakePoint(103.8198, 1.3521)::geography,
    200        -- 200 metres radius
  )::geometry
);

-- Trip path as LINESTRING (reconstructed from GPS events)
SELECT ST_MakeLine(position::geometry ORDER BY recorded_at) AS trip_path,
       ST_Length(ST_MakeLine(position::geometry ORDER BY recorded_at)::geography) AS distance_m
FROM vehicle_positions
WHERE vehicle_id = 42
AND recorded_at BETWEEN '2024-01-15 08:00' AND '2024-01-15 17:00';

-- Bounding box of all geofences for a customer (for map viewport)
SELECT ST_AsGeoJSON(ST_Extent(boundary)) AS bounding_box
FROM geofences
WHERE fleet_id IN (SELECT id FROM fleets WHERE customer_id = 42);

-- Convert to GeoJSON (for API responses to frontend map)
SELECT ST_AsGeoJSON(boundary)::json AS geojson
FROM geofences WHERE id = 1;
-- Returns: {"type":"Polygon","coordinates":[[[103.8,1.3],[103.85,1.3],...]]}

-- Convert from GeoJSON (from frontend map drawing tool)
SELECT ST_GeomFromGeoJSON('{"type":"Polygon","coordinates":[...]}');

-- Convert to WKT (Well-Known Text) for debugging
SELECT ST_AsText(boundary) FROM geofences WHERE id = 1;
-- Returns: POLYGON((103.8 1.3, 103.85 1.3, ...))

-- Convert from WKT
SELECT ST_GeomFromText('POLYGON((103.8 1.3, 103.85 1.3, 103.85 1.35, 103.8 1.35, 103.8 1.3))', 4326);

-- Reconstruct trip as a path and compute total distance
WITH trip_points AS (
  SELECT position, recorded_at
  FROM vehicle_positions
  WHERE vehicle_id = 42
    AND recorded_at BETWEEN '2024-01-15 08:00' AND '2024-01-15 17:00'
  ORDER BY recorded_at
)
SELECT
  ST_MakeLine(position::geometry ORDER BY recorded_at) AS path,
  ST_Length(
    ST_MakeLine(position::geometry ORDER BY recorded_at)::geography
  ) / 1000.0 AS distance_km,
  COUNT(*) AS ping_count
FROM trip_points;

## CPU diagnosis
top                          # live process list; press P to sort by CPU
htop                         # visual, colour-coded; F6 to sort, F9 to kill
ps aux --sort=-%cpu | head   # snapshot sorted by CPU usage
pidstat -u 1 5               # per-process CPU every 1s for 5 samples
mpstat -P ALL 1              # per-core CPU stats (find unbalanced load)

## Memory diagnosis
free -h                      # total/used/free/buffers/cache
vmstat 1 5                   # virtual memory stats: swpd, si, so = swap activity
cat /proc/meminfo            # detailed kernel memory breakdown
ps aux --sort=-%mem | head   # top memory consumers

## Disk I/O diagnosis
iostat -xz 1                 # per-disk util%, await (ms), r/s, w/s
iotop -o                     # per-process I/O (like top for disk)
lsof +D /var/lib/postgresql  # which processes have DB files open
df -h                        # disk space by filesystem
du -sh /var/log/*            # find log directories eating disk
ncdu /                       # interactive disk usage explorer

## Process inspection
strace -p <PID> -c           # summarise syscalls (what is it doing?)
lsof -p <PID>                # all files/sockets open by a process
cat /proc/<PID>/status       # detailed process info including threads
ps -eLf | grep php           # list all threads of a process
ps aux | grep Z              # find zombie processes

## Service management
systemctl status my-service          # current state + last 10 log lines
systemctl start|stop|restart my-service
systemctl enable my-service          # start on boot
systemctl reload my-service          # reload config without full restart (if supported)
systemctl list-units --type=service  # all services and their states
systemctl list-dependencies my-service

## Log investigation
journalctl -u my-service -b          # logs since last boot
journalctl -u my-service -f          # follow live
journalctl -u my-service --since "2024-01-15 14:00" --until "14:30"
journalctl -p err -b                 # only ERROR and above from this boot

## Writing a systemd unit file
# /etc/systemd/system/gps-processor.service
[Unit]
Description=GPS Event Processor
After=network.target postgresql.service
Requires=postgresql.service

[Service]
Type=simple
User=appuser
WorkingDirectory=/opt/gps-processor
ExecStart=/opt/gps-processor/bin/processor
Restart=always
RestartSec=5
EnvironmentFile=/etc/gps-processor/env
StandardOutput=journal
StandardError=journal

# Memory/CPU limits
MemoryLimit=512M
CPUQuota=50%

[Install]
WantedBy=multi-user.target

## Permissions
chmod 750 /opt/gps-processor/bin/processor
# 7=rwx (owner), 5=r-x (group), 0=--- (other)
chmod u+x,g-w,o-rwx script.sh    # symbolic mode
chown appuser:appgroup /opt/app   # change owner:group
chown -R appuser /opt/app         # recursive

## Special permissions
chmod +s /usr/bin/sudo            # SUID: run as file owner (not caller)
chmod g+s /shared/data            # SGID on dir: new files inherit group
chmod +t /tmp                     # Sticky bit: only owner can delete their files

## Find files by permission/ownership
find /opt -type f -perm /u+s      # find SUID files (security audit)
find / -user root -perm -4000     # world-writable SUID root files (risk!)

## Disk usage investigation
df -h                             # filesystem usage
df -i                             # inode usage (can be full even if space available)
du -sh /var/log/* | sort -rh | head -20  # largest log directories
find /var/log -name "*.log" -size +100M  # large log files
lsof | grep deleted               # files deleted but still held open (disk not freed!)

## Active connections and listening ports
ss -tlnp                     # TCP listening ports + process name (fast, modern)
ss -s                        # connection count summary by state
ss -tnp state established    # all established TCP connections
netstat -tlnp                # older equivalent (netstat is deprecated but still common)

## Is the port open / reachable?
nc -zv 10.0.1.5 5432        # TCP port check (can I reach Postgres?)
curl -v https://api.cartrack.sg/health  # full HTTP trace including TLS handshake
telnet 10.0.1.5 5432         # older alternative

## DNS resolution
dig api.cartrack.sg          # full DNS query with timing
dig +short api.cartrack.sg   # just the IP
nslookup api.cartrack.sg     # simpler alternative
cat /etc/resolv.conf         # which DNS servers the server uses

## Route tracing
traceroute api.cartrack.sg   # hop-by-hop latency to destination
mtr --report api.cartrack.sg # continuous traceroute with packet loss %

## Packet capture (powerful but requires root)
tcpdump -i eth0 port 1883    # capture MQTT traffic
tcpdump -i eth0 host 10.0.1.5 -w /tmp/capture.pcap  # save to file
tcpdump -n -r /tmp/capture.pcap  # read saved capture

## Connection count by state (useful for diagnosing connection exhaustion)
ss -tn | awk 'NR>1 {print $1}' | sort | uniq -c | sort -rn
# Shows: ESTABLISHED, TIME_WAIT, CLOSE_WAIT counts
# Too many TIME_WAIT = normal for HTTP (short-lived connections)
# Too many CLOSE_WAIT = app not closing connections properly (code bug)

## UFW (Ubuntu Uncomplicated Firewall — abstraction over iptables)
ufw status verbose            # current rules
ufw allow 22/tcp              # allow SSH
ufw allow from 10.0.0.0/8 to any port 5432  # only internal IPs to Postgres
ufw deny 5432                 # block Postgres from public
ufw enable

## iptables (lower-level, still common)
iptables -L -n -v             # list all rules with packet counts
iptables -A INPUT -p tcp --dport 1883 -s 10.0.0.0/8 -j ACCEPT  # allow MQTT from private network
iptables -A INPUT -p tcp --dport 1883 -j DROP   # drop everything else to MQTT port

## Check if a specific rule is blocking (count packets hitting it)
iptables -L INPUT -v -n | grep 1883

#!/bin/bash
set -euo pipefail  # e=exit on error, u=error on unset var, o pipefail=pipe fails matter
trap 'echo "ERROR: Script failed at line $LINENO"' ERR

## Variables and quoting
NAME="Cartrack"
echo "Hello $NAME"           # double quotes: variable expanded
echo 'Hello $NAME'           # single quotes: literal, no expansion
FILES=$(ls /var/log/*.log)   # command substitution
COUNT=${#FILES}               # string length

## Conditionals
if [[ -f "/etc/config.env" ]]; then
  source /etc/config.env
elif [[ -d "/etc/config/" ]]; then
  source /etc/config/main.env
else
  echo "No config found" >&2; exit 1
fi

[[ $COUNT -gt 0 ]] && echo "Has files" || echo "Empty"

## String tests: -z (empty), -n (not empty), == (equal), != (not equal)
## File tests: -f (file), -d (dir), -r (readable), -x (executable)
## Number tests: -eq -ne -lt -gt -le -ge

## Loops
for file in /var/log/gps/*.log; do
  echo "Processing $file"
done

while IFS= read -r line; do
  echo "Line: $line"
done < /etc/servers.txt

## Functions
log() {
  local level="$1"; shift
  echo "$(date '+%Y-%m-%d %H:%M:%S') [$level] $*" >> /var/log/deploy.log
}
log INFO "Deployment started"
log ERROR "Config file missing"

## Arrays
SERVERS=("web1" "web2" "web3")
for s in "${SERVERS[@]}"; do
  ssh "deploy@$s" 'systemctl restart app'
done

## grep — pattern matching
grep -i "error" /var/log/app.log           # case-insensitive
grep -v "debug" /var/log/app.log           # invert (exclude debug lines)
grep -oP 'vehicle_id=\K[^\s]+'            # extract just the match (-o) with Perl regex
grep -c "CRITICAL" /var/log/app.log        # count matching lines
grep -A3 -B1 "ERROR" app.log              # 3 lines after, 1 before match (context)

## awk — structured text processing
awk '{print $1, $3}' /var/log/access.log  # print columns 1 and 3
awk -F',' '$5 > 100 {print $1}' data.csv  # filter CSV: col 5 > 100, print col 1
awk '{sum+=$NF} END {print sum}' file     # sum the last column
awk 'NR%1000==0 {print NR, $0}' bigfile  # sample every 1000th line from huge file

## sed — stream editor for substitution
sed 's/ERROR/CRITICAL/g' app.log          # replace ERROR with CRITICAL
sed -n '100,200p' bigfile                 # print only lines 100-200
sed '/^#/d' config.ini                    # delete comment lines
sed -i 's/old_host/new_host/g' config     # edit file in-place

## sort, uniq — counting and deduplication
sort -t',' -k3 -n data.csv                # sort by column 3 numerically
sort | uniq -c | sort -rn | head -20      # frequency analysis (top 20 most common)
LC_ALL=C sort -u bigfile                  # fast ASCII sort + dedup

## xargs — batch parallel execution
find /data -name "*.gz" | xargs -P4 -I{} gzip -d {}  # decompress 4 at a time
cat server_list.txt | xargs -I{} ssh {} 'uptime'      # run uptime on all servers

## Real-world: find top 10 IPs hammering the API
awk '{print $1}' /var/log/nginx/access.log | sort | uniq -c | sort -rn | head -10

## Parse structured logs (JSON)
cat app.log | python3 -c "
import sys, json
for line in sys.stdin:
    try:
        d = json.loads(line)
        if d.get('level') == 'ERROR':
            print(d['timestamp'], d['message'])
    except: pass
"

## crontab format: min hour day month weekday command
crontab -e         # edit current user's crontab
crontab -l         # list current user's crontab
crontab -u postgres -l  # list another user's crontab

## Cron examples
0  2 * * *    /opt/scripts/backup_db.sh          # daily at 2am
*/5 * * * *   /opt/scripts/health_check.sh       # every 5 minutes
0  0 1 * *    /opt/scripts/monthly_report.sh     # first of every month
0  8-18 * * 1-5  /opt/scripts/fleet_sync.sh     # every hour, business hours, weekdays

## Best practices for cron scripts
# 1. Use absolute paths — cron has a minimal PATH
# 2. Redirect output to a log file
0 2 * * * /opt/scripts/backup.sh >> /var/log/backup.log 2>&1

# 3. Add a lock file to prevent overlapping runs
0 */2 * * * flock -n /tmp/etl.lock /opt/scripts/etl.sh || echo "Already running"

# 4. Set MAILTO="" to suppress email output (noisy by default)
MAILTO=""

## /etc/cron.d/ — system-level cron with user specification
# /etc/cron.d/gps-archive:
0 3 * * * postgres /opt/scripts/archive_gps_partitions.sh

#!/bin/bash
## Health check script with alerting
set -euo pipefail

ENDPOINT="https://api.cartrack.sg/health"
SLACK_WEBHOOK="${SLACK_WEBHOOK_URL}"
THRESHOLD_MS=2000

check_health() {
  local start end elapsed http_code
  start=$(date +%s%N)
  http_code=$(curl -s -o /dev/null -w "%{http_code}" --max-time 5 "$ENDPOINT")
  end=$(date +%s%N)
  elapsed=$(( (end - start) / 1000000 ))  # ms

  if [[ "$http_code" != "200" ]]; then
    alert "API returned HTTP $http_code"
  elif [[ $elapsed -gt $THRESHOLD_MS ]]; then
    alert "API slow: ${elapsed}ms (threshold: ${THRESHOLD_MS}ms)"
  fi
}

alert() {
  local msg="$1"
  echo "$(date '+%Y-%m-%d %H:%M:%S') ALERT: $msg" >> /var/log/health-check.log
  curl -s -X POST "$SLACK_WEBHOOK" \
    -H 'Content-type: application/json' \
    --data "{\"text\":\"⚠️ $msg\"}" > /dev/null
}

check_health

## Generate a key pair (Ed25519 is modern and fast)
ssh-keygen -t ed25519 -C "deploy@cartrack" -f ~/.ssh/cartrack_deploy

## Copy public key to server
ssh-copy-id -i ~/.ssh/cartrack_deploy.pub user@server

## ~/.ssh/config for managing multiple servers
Host cartrack-prod
  HostName 10.0.1.100
  User deploy
  IdentityFile ~/.ssh/cartrack_deploy
  ServerAliveInterval 60

## Then just: ssh cartrack-prod

## /etc/ssh/sshd_config hardening
PermitRootLogin no               # never allow root SSH
PasswordAuthentication no        # key-only authentication
PubkeyAuthentication yes
AllowUsers deploy appuser        # whitelist specific users
Port 2222                        # non-standard port reduces script-kiddie noise

## SSH tunnelling (port forwarding)
ssh -L 5432:db-server:5432 jump-host  # forward local 5432 through jump-host to DB
ssh -R 8080:localhost:3000 server     # expose local port 3000 on remote server's 8080

## scp and rsync for file transfer
scp -i ~/.ssh/key file.tar.gz deploy@server:/opt/releases/
rsync -avz --delete /opt/app/ deploy@server:/opt/app/  # sync, delete removed files

## Setting and using environment variables
export DB_PASSWORD="secret"          # set for current session + subprocesses
DB_HOST=localhost ./app              # set only for one command
printenv                             # show all env vars
env | grep APP_                      # filter to app-specific vars

## /etc/environment — system-wide, all users, loaded at login
## /etc/profile.d/*.sh — loaded for login shells
## ~/.bashrc — user-specific interactive shells

## Loading from a .env file in a script
set -a                               # auto-export all variables
source /etc/app/.env
set +a

## Never commit .env files — use .gitignore
## In production: use systemd EnvironmentFile or AWS Secrets Manager

## Masking secrets in logs
echo "DB_PASSWORD: ${DB_PASSWORD:0:3}***"  # show only first 3 chars

## Check if a required env var is set (fail fast if not)
: "${DB_HOST:?DB_HOST must be set}"   # exits with error message if unset

# Start a feature
git checkout develop
git checkout -b feature/geofence-alerts
# ... work ...
git checkout develop
git merge --no-ff feature/geofence-alerts  # --no-ff preserves branch history
git branch -d feature/geofence-alerts

# Cut a release (only bug fixes from here, no new features)
git checkout develop
git checkout -b release/2.0.0
# bump version number, fix bugs found in testing
git checkout main && git merge --no-ff release/2.0.0
git tag -a v2.0.0 -m "Release 2.0.0"
git checkout develop && git merge --no-ff release/2.0.0  # back-merge fixes

# Hotfix in production
git checkout main
git checkout -b hotfix/critical-gps-null
# fix the bug
git checkout main && git merge --no-ff hotfix/critical-gps-null
git tag -a v2.0.1
git checkout develop && git merge --no-ff hotfix/critical-gps-null  # crucial!

# The workflow is intentionally simple
git switch -c feat/speed-alert-refactor   # branch from main
# make small, focused commits — push frequently
git push origin feat/speed-alert-refactor
# Open MR → CI passes → 1 approval → squash merge → delete branch

# Feature flag in application code (incomplete work is merged but hidden)
if (featureFlags.isEnabled('new-geofence-engine', customerId)) {
  // new unfinished code — only visible to internal testers
} else {
  // old code — what all customers see
}

# Backport a security fix to v2.x release branch
git checkout release/2.x
git cherry-pick abc1234   # cherry-pick the specific fix commit from main
git tag -a v2.8.1 -m "Security: fix GPS auth bypass"
# DO NOT merge release/2.x back into main — it contains old code

STRATEGY          RELEASE CADENCE   BRANCH COUNT   BEST FOR
Trunk-Based       Multiple/day      Low (1-2)      SaaS, continuous delivery
GitHub Flow       Daily             Low (1+)       Web services, small teams
Git Flow          Scheduled         High (5 types) Mobile apps, OSS libraries
Environment Br.   Scheduled         High (3+)      Enterprise with manual gates
Release Branches  Multiple versions  Medium        SDKs, enterprise software

# GitLab MR merge strategies (Settings → General → Merge requests)
# "Merge commit" — creates merge commit (default)
# "Merge commit with semi-linear history" — rebase first, then merge commit
# "Fast-forward merge" — rebase only, no merge commit, requires linear history

# Team recommendation: Squash and merge
# Every MR = one commit on main = easy to revert a feature = clean log

# .gitlab/merge_request_templates/default.md
## What does this MR do?
<!-- One sentence summary -->

## Why?
<!-- Link to issue/ticket. What problem does this solve? -->

## How to test
<!-- Steps to verify the change works -->

## Checklist
- [ ] Tests added or updated
- [ ] CI pipeline passing
- [ ] No debug/commented-out code
- [ ] Documentation updated if needed
- [ ] Screenshots attached (for UI changes)

# CODEOWNERS file — auto-assign reviewers by path
# GitLab reads this from the root of the repo
/database/migrations/   @db-lead
/api/auth/              @security-team
/frontend/              @frontend-team
*                       @tech-lead

# package.json — Husky setup for Node/PHP projects
{
  "husky": {
    "hooks": {
      "pre-commit": "lint-staged",          // lint only staged files
      "commit-msg": "commitlint --edit $1", // enforce conventional commits
      "pre-push": "npm test"               // run tests before push
    }
  },
  "lint-staged": {
    "*.php": ["phpcs", "phpstan analyse"],
    "*.ts": ["eslint --fix", "prettier --write"]
  }
}

# commitlint.config.js — enforce Conventional Commits
module.exports = {
  extends: ['@commitlint/config-conventional'],
  rules: {
    'type-enum': [2, 'always', [
      'feat', 'fix', 'chore', 'refactor', 'docs', 'test', 'perf'
    ]]
  }
};

# Format: type(scope): description
feat(gps): add deduplication for duplicate device packets
fix(auth): resolve null pointer on expired JWT refresh
chore(deps): upgrade php-cs-fixer to 3.x
refactor(trips): extract TripReconstructionService from GPSProcessor
docs(api): add OpenAPI spec for /v1/vehicles endpoint
test(geofence): add edge case for boundary crossing at exactly midnight
perf(dashboard): cache driver score aggregations for 5 minutes

# Breaking changes — triggers major version bump
feat(api)!: remove deprecated /v1/fleet/all endpoint

# Generate changelog automatically with standard-version or release-please
npx standard-version   # bumps version, generates CHANGELOG.md, tags commit

## git bisect — binary search for a regression
git bisect start
git bisect bad                  # current = broken
git bisect good v2.3.0          # this tag was working
git bisect run ./run_tests.sh   # automate (exit 0 = good)
git bisect reset                # clean up
# On 1,000 commits: finds the bad one in ~10 steps

## git reflog — your safety net for "lost" commits
git reflog                      # see every HEAD movement
git checkout HEAD@{3}           # go back 3 HEAD positions
git branch recovery HEAD@{3}   # recover "deleted" branch

## git stash — save work-in-progress without committing
git stash push -m "half-done geofence fix"
git stash list
git stash pop                   # restore latest stash
git stash apply stash@{2}       # restore specific stash, keep it

## git worktree — check out two branches simultaneously
git worktree add ../hotfix-branch hotfix/critical-fix
# Work in ../hotfix-branch while staying on your feature branch
git worktree remove ../hotfix-branch

# Lightweight tag (just a pointer to a commit)
git tag v2.4.1

# Annotated tag (preferred — includes message, author, date)
git tag -a v2.4.1 -m "Release 2.4.1: fix GPS null handling"
git push origin v2.4.1          # tags don't push automatically
git push origin --tags          # push all local tags

# List and filter tags
git tag -l "v2.*"

# GitLab: tag-triggered pipeline deploys to production
# rules: - if: $CI_COMMIT_TAG

# String — simple key-value, TTL-based cache
SET driver:score:123 "87.5" EX 300      # expires in 5 min
GET driver:score:123

# Hash — store object fields (avoid multiple roundtrips)
HSET vehicle:456 lat 1.3521 lng 103.8198 speed 65
HGETALL vehicle:456

# Sorted Set — leaderboards, rate limiting by score
ZADD driver_scores 87.5 "driver:123"
ZRANGE driver_scores 0 9 REV WITHSCORES  # top 10 drivers

# Distributed lock (prevent cache stampede)
SET lock:refresh:driver_scores "1" NX PX 5000  # NX = only if not exists, PX = ms TTL

# Rate limiting counter
INCR rate:api:customer:789
EXPIRE rate:api:customer:789 60           # reset every minute

# Pub/Sub — lightweight event fanout
PUBLISH geofence_alerts '{"vehicle":456,"zone":"depot","event":"exit"}'

# Static assets (content-hash in filename — cache forever)
Cache-Control: public, max-age=31536000, immutable

# API response — cache for 60s, allow stale for 5s while revalidating
Cache-Control: public, max-age=60, stale-while-revalidate=5

# Private user data — never cache at CDN level
Cache-Control: private, no-store

# Redis sliding window rate limit
function isRateLimited(customerId, limit=1000, windowSec=60):
  key = "rate:{customerId}:{floor(now/windowSec)}"
  count = INCR(key)
  if count == 1: EXPIRE(key, windowSec * 2)  # set TTL on first hit
  return count > limit

# Return these headers so clients can self-throttle
X-RateLimit-Limit: 1000
X-RateLimit-Remaining: 847
X-RateLimit-Reset: 1704067260

# Resource-oriented URLs
GET    /v1/fleets/{fleet_id}/vehicles
GET    /v1/vehicles/{vehicle_id}/trips?from=2024-01-01&to=2024-01-31
GET    /v1/vehicles/{vehicle_id}/position    # latest GPS fix
POST   /v1/alerts
PATCH  /v1/drivers/{driver_id}

// Blocking (bad — thread parked while DB responds)
public Vehicle GetVehicle(int id) {
    return _db.Vehicles.Find(id);
}

// Non-blocking (good — thread returns to pool during DB wait)
public async Task<Vehicle> GetVehicleAsync(int id) {
    return await _db.Vehicles.FindAsync(id);
}

// Bad — new array reference every render triggers re-render
const markers = vehicles.map(v => ({ lat: v.lat, lng: v.lng }));

// Good — memoised, only recalculates when vehicles changes
const markers = useMemo(
  () => vehicles.map(v => ({ lat: v.lat, lng: v.lng })),
  [vehicles]
);

# WRONG: cache busted every time source code changes
COPY . .
RUN npm ci     # reinstalls on every code change!

# CORRECT: deps layer cached unless package.json changes
COPY package*.json ./
RUN npm ci --production   # cached if deps unchanged
COPY . .                  # source invalidates cache only here

FROM php:8.3-fpm AS base
RUN docker-php-ext-install pdo pdo_pgsql

FROM base AS deps
COPY composer.json composer.lock ./
RUN composer install --no-dev --optimize-autoloader

FROM base AS final
COPY --from=deps /app/vendor ./vendor
COPY . .
EXPOSE 9000

---
# docker-compose.yml
services:
  api:
    build: .
    environment:
      DB_HOST: postgres
      DB_PASS: ${DB_PASS}   # from .env — never hardcode
    depends_on:
      postgres:
        condition: service_healthy
  postgres:
    image: postgres:16
    healthcheck:
      test: ["CMD-SHELL", "pg_isready -U app"]
      interval: 5s

stages: [lint, test, build, deploy]

lint-code:
  stage: lint
  image: php:8.3
  script:
    - composer run phpcs       # PSR-12 coding standards
    - composer run phpstan     # static analysis
  cache:
    paths: [vendor/]
    key: $CI_COMMIT_REF_SLUG

unit-tests:
  stage: test
  services: [postgres:16]    # real DB for tests
  variables: {POSTGRES_DB: test_db}
  script:
    - composer run phpunit --coverage-text
  coverage: '/Lines:\s+(\d+\.\d+)%/'

build-image:
  stage: build
  image: docker:24
  services: [docker:dind]
  script:
    - docker build -t $CI_REGISTRY_IMAGE:$CI_COMMIT_SHA .
    - docker push $CI_REGISTRY_IMAGE:$CI_COMMIT_SHA

deploy-staging:
  stage: deploy
  script: [kubectl set image deployment/api api=$CI_REGISTRY_IMAGE:$CI_COMMIT_SHA]
  environment: staging
  rules:
    - if: $CI_COMMIT_BRANCH == "main"

deploy-production:
  stage: deploy
  when: manual         # human approval gate
  environment: production
  script: [kubectl set image deployment/api api=$CI_REGISTRY_IMAGE:$CI_COMMIT_SHA]
  rules:
    - if: $CI_COMMIT_TAG    # only on tagged releases