Your First ADR

This tutorial walks you through writing a complete Architectural Decision Record using Structured MADR. We will use a realistic scenario: choosing a database for a new web application.

The Scenario

Your team is building an order management system. You need to decide on a primary database. The candidates are PostgreSQL, MySQL, and MongoDB. Let’s document this decision properly.

Create the File

touch docs/decisions/0001-select-primary-database.md

Write the Frontmatter

Start with the YAML metadata block. This is the machine-readable part that enables tooling and AI integration:

---
title: "Select Primary Database"
description: "Choose the primary database for the order management system."
type: adr
category: architecture
tags:
  - database
  - storage
  - infrastructure
status: proposed
created: 2026-02-16
updated: 2026-02-16
author: Platform Team
project: order-management
technologies:
  - postgresql
  - mysql
  - mongodb
audience:
  - developers
  - architects
  - devops
---

Notice that technologies lists all candidates, not just the winner. This helps tools surface the ADR when anyone searches for any of the evaluated technologies.

Write the Heading and Status

# ADR-0001: Select Primary Database

## Status

Proposed

Keep the status in sync with the frontmatter status field.

Describe the Context

Explain why this decision needs to be made now:

## Context

### Background and Problem Statement

The order management system requires a persistent data store for orders, customers,
inventory, and audit logs. The system must support ACID transactions for order processing
and handle approximately 500 orders per minute at peak load.

### Current Limitations

1. **No existing database**: This is a greenfield project without an incumbent system.
2. **Multi-team access**: Three teams will query the database, requiring standard SQL interfaces.

Be specific about constraints and numbers. Vague context leads to vague decisions.

List Decision Drivers

Separate primary drivers (deal-breakers) from secondary drivers (nice-to-haves):

## Decision Drivers

### Primary Decision Drivers

1. **ACID transactions**: Order processing requires strong consistency guarantees.
2. **SQL compatibility**: Multiple teams rely on SQL for analytics and reporting.
3. **Operational maturity**: The team has deep experience with relational databases.

### Secondary Decision Drivers

1. **JSON support**: Some order metadata is semi-structured.
2. **Managed service availability**: Prefer a database with managed hosting options on AWS and GCP.

Document Considered Options

This is the most detailed section. For each option, provide a structured evaluation:

## Considered Options

### Option 1: PostgreSQL

**Description**: Open-source relational database with extensive feature set.

**Technical Characteristics**:
- Full ACID compliance with MVCC
- Native JSONB column type for semi-structured data
- Row-level security and robust permission model

**Advantages**:
- Advanced indexing (B-tree, GIN, GiST, BRIN)
- Mature replication and high-availability tooling
- Strong JSON querying eliminates need for a separate document store

**Disadvantages**:
- Vertical scaling has practical limits before sharding is needed
- Write-heavy workloads may require tuning

**Risk Assessment**:
- **Technical Risk**: Low. PostgreSQL is proven at this scale.
- **Schedule Risk**: Low. Team has prior experience.
- **Ecosystem Risk**: Low. Available as managed service on all major clouds.

### Option 2: MySQL

**Description**: Widely-used open-source relational database.

**Technical Characteristics**:
- ACID compliance with InnoDB engine
- Proven at large scale (Meta, GitHub)

**Advantages**:
- Extremely well-documented and widely understood
- Fast read performance for simple queries

**Disadvantages**:
- JSON support is less mature than PostgreSQL
- Fewer advanced indexing options
- Window functions and CTEs added only in MySQL 8.0

**Risk Assessment**:
- **Technical Risk**: Low. Mature and battle-tested.
- **Schedule Risk**: Low. Familiar to most developers.
- **Ecosystem Risk**: Low. Widely available as managed service.

### Option 3: MongoDB

**Description**: Document-oriented NoSQL database.

**Technical Characteristics**:
- Document model with flexible schemas
- Horizontal scaling via native sharding

**Advantages**:
- Flexible schema suits evolving data models
- Built-in horizontal scaling

**Disadvantages**:
- No multi-document ACID transactions until v4.0, with performance overhead
- Requires learning a non-SQL query language
- Analytics teams would need adapters or ETL pipelines

**Disqualifying Factor**: The requirement for multi-team SQL access and ACID transactions makes a document store a poor fit for the primary database.

**Risk Assessment**:
- **Technical Risk**: Medium. Transaction support is newer and less proven.
- **Schedule Risk**: High. Team lacks MongoDB experience.
- **Ecosystem Risk**: Low. Managed services widely available.

Notice the Disqualifying Factor on Option 3. When an option clearly fails a primary driver, call it out explicitly.

State the Decision

## Decision

We will use PostgreSQL as the primary database for the order management system.

The implementation will use:
- **Amazon RDS for PostgreSQL** for managed hosting with automated backups
- **JSONB columns** for semi-structured order metadata
- **pgAudit** for compliance-grade audit logging

Document Consequences

Split consequences into three categories:

## Consequences

### Positive

1. **Strong consistency**: ACID transactions ensure order integrity across all operations.
2. **Single query language**: All teams use standard SQL without adapters.
3. **Operational confidence**: Team has five years of PostgreSQL production experience.

### Negative

1. **Vertical scaling ceiling**: If load exceeds single-instance capacity, sharding adds complexity.
2. **Managed service cost**: RDS pricing is higher than self-hosted for equivalent compute.

### Neutral

1. **Schema migrations required**: Relational schemas require explicit migrations, adding process but also providing an audit trail of data model changes.

Summarize and Add Audit

## Decision Outcome

PostgreSQL satisfies all primary decision drivers with low risk across all dimensions.
The team's existing expertise minimizes onboarding time.

Mitigations:
- Monitor query performance and set up read replicas before reaching capacity limits
- Evaluate Citus extension if horizontal scaling becomes necessary

## Related Decisions

- [ADR-0002: Caching Strategy](0002-caching-strategy.md) - Cache layer to reduce database load

## Links

- [PostgreSQL Documentation](https://www.postgresql.org/docs/) - Official reference
- [pgAudit Extension](https://www.pgaudit.org/) - Audit logging for PostgreSQL

## More Information

- **Date:** 2026-02-16
- **Source:** Platform Team architecture review meeting
- **Related ADRs:** ADR-0002

## Audit

### 2026-02-16

**Status:** Pending

**Findings:**

| Finding                  | Files | Lines | Assessment |
|--------------------------|-------|-------|------------|
| Awaiting implementation  | -     | -     | pending    |

**Summary:** ADR created, awaiting implementation.

**Action Required:** Implement decision and audit after deployment.

Updating the Status

Once stakeholders approve, update both the frontmatter status field and the Status section:

status: accepted

## Status

Accepted

When the implementation is complete, add a new audit entry with findings that reference specific files and line numbers in your codebase.