Bagian 1: User Schema

Sistem autentikasi menjawab satu pertanyaan: pengguna mana yang sedang berada di sisi lain koneksi? Sebelum pertanyaan itu bisa dijawab, kita butuh tempat mencatat siapa saja pengguna yang sah, dan tempat itu adalah tabel users di database. Kesalahan desain pada tahap ini paling mahal diperbaiki, karena sekali kata sandi tersimpan dalam bentuk yang salah, seluruh data lama harus dimigrasi. Pada bagian ini kita merancang skema pengguna di atas SQLite, membuktikan setiap aturannya lewat PHP, dan menyiapkan data awal yang membuat bagian-bagian berikutnya langsung bisa diuji.

Dari Kebutuhan Autentikasi ke Struktur Data #

Kita mulai dengan mendaftarkan kebutuhan data secara eksplisit, sama seperti proses pada chapter Merancang Database Schema . Sistem autentikasi yang akan kita bangun setidaknya harus memenuhi hal berikut:

• Setiap pengguna punya identitas berupa email dan nama tampilan.
• Satu email hanya boleh dipakai satu akun — termasuk [email protected] versus [email protected].
• Kata sandi tidak pernah disimpan sebagai teks asli, melainkan sebagai hash.
• Sistem perlu membedakan akun yang emailnya sudah diverifikasi dan yang belum.
• Waktu pembuatan akun tercatat otomatis untuk audit.

Setiap kebutuhan tersebut diterjemahkan menjadi satu keputusan konkret pada skema, bukan dibiarkan sebagai niat baik di kode aplikasi:

Pemetaan ini menjadi kontrak antara database dan seluruh bagian proyek selanjutnya. Ketika Bagian 2 menulis layanan registrasi, layanan tersebut cukup percaya bahwa email duplikat tidak mungkin lolos ke tabel, sehingga kode PHP tinggal menerjemahkan penolakan database menjadi pesan yang ramah.

Merancang Tabel users #

Seluruh kebutuhan di atas bermuara pada satu tabel dengan enam kolom. Berikut definisinya sebagaimana ditulis pada berkas schema.sql:

CREATE TABLE IF NOT EXISTS users (
    id                INTEGER PRIMARY KEY AUTOINCREMENT,
    email             TEXT    NOT NULL UNIQUE COLLATE NOCASE,
    password_hash     TEXT    NOT NULL,
    nama              TEXT    NOT NULL,
    email_verified_at TEXT    NULL,
    dibuat_pada       TEXT    NOT NULL DEFAULT CURRENT_TIMESTAMP
);

Mari kita bedah peran setiap kolom pada deklarasi tersebut:

1. id INTEGER PRIMARY KEY AUTOINCREMENT: kolom id menjadi primary key sekaligus alias rowid di SQLite, sehingga terisi otomatis dan unik. AUTOINCREMENT mencegah SQLite memakai ulang id akun yang sudah dihapus.
2. email TEXT NOT NULL UNIQUE COLLATE NOCASE: constraint UNIQUE menolak dua akun dengan email yang sama, dan COLLATE NOCASE membuat aturan tersebut berlaku tanpa membedakan huruf kapital. Perbandingan [email protected] dengan [email protected] dianggap sama, sehingga keduanya tidak mungkin hidup bersamaan.
3. password_hash TEXT NOT NULL: kolom ini menyimpan hasil password_hash() PHP, bukan kata sandi asli. bcrypt menghasilkan string tepat 60 karakter, tetapi kolomnya sengaja tidak dibatasi panjang — pada database yang memakai VARCHAR, berikan ruang minimal 255 karakter agar kolom tetap cukup bila kelak algoritma hash diganti ke argon2id yang hasilnya lebih panjang.
4. nama TEXT NOT NULL: nama tampilan pengguna; akun tanpa nama tidak bermakna bagi aplikasi.
5. email_verified_at TEXT NULL: kolom ini sekaligus menjadi penanda dan catatan audit. Nilai NULL berarti email belum diverifikasi; begitu verifikasi selesai, kolom diisi waktu kejadiannya, bukan sekadar angka 1.
6. dibuat_pada TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP: waktu pembuatan akun terisi otomatis dalam format YYYY-MM-DD HH:MM:SS zona waktu UTC.

Selain tabel, schema.sql menambahkan satu index eksplisit untuk pola query “daftar akun terbaru”:

CREATE INDEX IF NOT EXISTS idx_users_dibuat_pada ON users(dibuat_pada);

Constraint UNIQUE pada email juga diam-diam membuat index keduanya bernama sqlite_autoindex_users_1, sehingga pencarian email — operasi paling sering dilakukan saat login — sudah cepat tanpa kerja tambahan.

Hash Kata Sandi di PHP, Bukan di SQL #

SQL tidak memiliki fungsi bcrypt, dan memang seharusnya tidak: perhitungan hash adalah urusan kode aplikasi, bukan query. PHP menyediakan pasangan fungsi password_hash() untuk menghasilkan hash dan password_verify() untuk memeriksa kata sandi terhadap hash yang tersimpan — materi lengkapnya ada pada chapter Password Hashing . Skrip berikut menunjukkan bagaimana nilai password_hash pada schema.sql dihasilkan:

<?php
$sandi = 'sandi-budi-2026';

$hash1 = password_hash($sandi, PASSWORD_BCRYPT);
$hash2 = password_hash($sandi, PASSWORD_BCRYPT);

echo 'panjang hash                 : ' . strlen($hash1) . " karakter\n";
echo 'awalan algoritma             : ' . substr($hash1, 0, 4) . "\n";
echo 'cost bcrypt                  : ' . password_get_info($hash1)['options']['cost'] . "\n";
echo 'dua hash sandi yang sama     : ' . var_export($hash1 === $hash2, true) . "\n";
echo 'verify sandi asli            : ' . var_export(password_verify($sandi, $hash1), true) . "\n";
echo 'verify sandi salah           : ' . var_export(password_verify('sandi-salah', $hash1), true) . "\n";

Output yang Dihasilkan:

panjang hash                 : 60 karakter
awalan algoritma             : $2y$
cost bcrypt                  : 12
dua hash sandi yang sama     : false
verify sandi asli            : true
verify sandi salah           : false

Baris panjang hash dan awalan algoritma membedah anatomi bcrypt: 4 karakter pertama ($2y$) menandai algoritma bcrypt, disusul cost dua digit, lalu salt 22 karakter dan hasil hash 31 karakter — total tepat 60. Baris dua hash sandi yang sama bernilai false karena salt dihasilkan acak pada setiap panggilan, sehingga dua pengguna dengan kata sandi identik tetap menyimpan hash yang berbeda. Terakhir, dua baris verify menegaskan satu-satunya cara memeriksa kata sandi: lewat password_verify(), yang mengembalikan boolean.

Perhatikan juga bahwa skrip di atas sengaja tidak mencetak nilai hash itu sendiri. Hash bukanlah data yang perlu dilihat manusia; yang kita periksa hanyalah fakta strukturalnya — panjang, awalan, dan hasil verify. Kebiasaan ini kita pertahankan di seluruh proyek: output program hanya menampilkan fakta struktural, tidak pernah hash mentah.

Data Awal dengan Hash Konstan #

Database kosong memaksa setiap demo menyusun data dari nol. Karena itu akhir schema.sql menyisipkan tiga pengguna contoh:

INSERT OR IGNORE INTO users (email, password_hash, nama, email_verified_at, dibuat_pada) VALUES
    ('[email protected]', '$2y$12$Wb28CVffgGb2ZhdUhdrGi.SJ4d7lGrnxKzh05Uy4oZgWQesrExyCS', 'Budi Santoso',  '2026-01-11 09:00:00', '2026-01-10 09:15:00'),
    ('[email protected]', '$2y$12$ISJ1enIuLhADeRrLspWbQ.LDY85Gbg1KbYzYm6uXxRpb/yZDtt6ku', 'Siti Nurhaliza', NULL,                  '2026-01-12 10:30:00'),
    ('[email protected]', '$2y$12$l9fO6eNBq118eSddkWU8feS/9KTa175PPNEUxRdvuC/BIp2DAn6UK', 'Dewi Lestari',  NULL,                  '2026-01-13 14:05:00');

Tiga keputusan kecil pada blok tersebut perlu dijelaskan. Pertama, nilai password_hash adalah keluaran password_hash() PHP yang ditempel sebagai konstanta — kata sandinya adalah sandi-budi-2026, sandi-siti-2026, dan sandi-dewi-2026, persis proses yang dibuktikan skrip pada section sebelumnya. Kedua, INSERT OR IGNORE bekerja sama dengan UNIQUE pada email: bila email sudah ada, SQLite melewati baris tersebut diam-diam, sehingga schema.sql aman dijalankan berulang kali. Ketiga, kolom dibuat_pada dan email_verified_at diisi nilai eksplisit yang dibekukan agar setiap demo pada bagian-bagian proyek ini menampilkan hasil yang sama persis.

Menguji Skema dari PHP #

Skema belum bisa dipercaya sebelum strukturnya terbukti. SQLite menyimpan daftar tabel dan index miliknya pada tabel khusus sqlite_master, dan posisi kolom bisa dibaca lewat PRAGMA table_info. Skrip berikut memuat schema.sql ke database :memory: lalu memeriksa keduanya, dengan pola koneksi PDO yang sama seperti chapter PDO :

<?php
$pdo = new PDO('sqlite::memory:');
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$pdo->exec(file_get_contents(__DIR__ . '/schema.sql'));

echo "Objek database hasil schema.sql:\n";
foreach ($pdo->query('SELECT type, name FROM sqlite_master ORDER BY type, name') as $objek) {
    echo "  {$objek['type']}: {$objek['name']}\n";
}

echo "\nStruktur kolom users:\n";
foreach ($pdo->query('PRAGMA table_info(users)') as $kolom) {
    $default = $kolom['dflt_value'] ?? '-';
    echo "  {$kolom['name']} bertipe {$kolom['type']},"
        . " wajib diisi: {$kolom['notnull']},"
        . " primary key: {$kolom['pk']},"
        . " nilai default: $default\n";
}

Output yang Dihasilkan:

Objek database hasil schema.sql:
  index: idx_users_dibuat_pada
  index: sqlite_autoindex_users_1
  table: sqlite_sequence
  table: users

Struktur kolom users:
  id bertipe INTEGER, wajib diisi: 0, primary key: 1, nilai default: -
  email bertipe TEXT, wajib diisi: 1, primary key: 0, nilai default: -
  password_hash bertipe TEXT, wajib diisi: 1, primary key: 0, nilai default: -
  nama bertipe TEXT, wajib diisi: 1, primary key: 0, nilai default: -
  email_verified_at bertipe TEXT, wajib diisi: 0, primary key: 0, nilai default: -
  dibuat_pada bertipe TEXT, wajib diisi: 1, primary key: 0, nilai default: CURRENT_TIMESTAMP

Ada empat objek pada output di atas dan semuanya sah. Tabel users dan index idx_users_dibuat_pada adalah buatan kita; sqlite_autoindex_users_1 dibuat diam-diam oleh constraint UNIQUE pada email; dan sqlite_sequence dikelola SQLite sendiri untuk mengingat nomor id terakhir pada tabel AUTOINCREMENT. Baris id wajib diisi: 0 juga bukan kekeliruan: primary key INTEGER selalu terisi otomatis oleh SQLite, sehingga kolom tersebut tidak perlu ditandai NOT NULL secara eksplisit. Perhatikan pula email_verified_at wajib diisi: 0 — itulah wujud kolom yang boleh NULL.

Selanjutnya kita pastikan data awal masuk dengan benar dan hash yang tersimpan benar-benar bisa diverifikasi:

<?php
$pdo = new PDO('sqlite::memory:');
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$pdo->exec(file_get_contents(__DIR__ . '/schema.sql'));

echo "Data awal:\n";
foreach ($pdo->query('SELECT id, email, nama, email_verified_at FROM users ORDER BY id') as $user) {
    $status = $user['email_verified_at'] === null ? 'belum terverifikasi' : 'terverifikasi';
    echo "  #{$user['id']} {$user['nama']} <{$user['email']}> - $status\n";
}

$hash = $pdo->query(
    "SELECT password_hash FROM users WHERE email = '[email protected]'"
)->fetchColumn();

echo "\nHash yang tersimpan untuk [email protected]:\n";
echo '  panjang string              : ' . strlen($hash) . " karakter\n";
echo '  awalan                      : ' . substr($hash, 0, 4) . "\n";
echo '  verify sandi-budi-2026      : ' . var_export(password_verify('sandi-budi-2026', $hash), true) . "\n";
echo '  verify sandi-yang-salah     : ' . var_export(password_verify('sandi-yang-salah', $hash), true) . "\n";

Output yang Dihasilkan:

Data awal:
  #1 Budi Santoso <[email protected]> - terverifikasi
  #2 Siti Nurhaliza <[email protected]> - belum terverifikasi
  #3 Dewi Lestari <[email protected]> - belum terverifikasi

Hash yang tersimpan untuk [email protected]:
  panjang string              : 60 karakter
  awalan                      : $2y$
  verify sandi-budi-2026      : true
  verify sandi-yang-salah     : false

Tiga pengguna awal tampil dengan status verifikasi yang berbeda: Budi terverifikasi, sedangkan Siti dan Dewi belum — variasi yang nanti berguna menguji alur login. Hash yang tersimpan di database lolos pemeriksaan password_verify() untuk kata sandi yang benar dan gagal untuk yang salah, sehingga kita tahu konstanta pada schema.sql bukan sekadar string hiasan.

Membuktikan Constraint Bekerja #

Aturan yang paling penting adalah yang paling sering dilanggar. Tiga penyisipan berikut menguji jaminan inti skema: email baru diterima, email duplikat persis ditolak, dan email duplikat dengan kapital berbeda juga ditolak:

<?php
$pdo = new PDO('sqlite::memory:');
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$pdo->exec(file_get_contents(__DIR__ . '/schema.sql'));

$stmt = $pdo->prepare('INSERT INTO users (email, password_hash, nama) VALUES (?, ?, ?)');

// Tono mendaftar dengan email baru - hash asli dari password_hash().
$stmt->execute(['[email protected]', password_hash('sandi-tono-2026', PASSWORD_BCRYPT), 'Tono Wijaya']);
echo 'Pengguna baru tersimpan dengan id: ' . $pdo->lastInsertId() . "\n";

// Email sama persis dengan salah satu seed.
try {
    $stmt->execute(['[email protected]', 'x', 'Siti Kedua']);
    echo "Tersimpan.\n";
} catch (PDOException $e) {
    echo "Email duplikat ditolak [{$e->getCode()}]\n";
    echo "  {$e->getMessage()}\n";
}

// Email sama, tetapi huruf kapitalnya berbeda.
try {
    $stmt->execute(['[email protected]', 'x', 'Budi Kapital']);
    echo "Tersimpan.\n";
} catch (PDOException $e) {
    echo "Email beda kapital ditolak [{$e->getCode()}]\n";
    echo "  {$e->getMessage()}\n";
}

// Skema idempoten: seed tidak dobel saat dijalankan ulang.
$pdo->exec(file_get_contents(__DIR__ . '/schema.sql'));
$jumlah = $pdo->query('SELECT COUNT(*) FROM users')->fetchColumn();
$seedBudi = $pdo->query(
    "SELECT COUNT(*) FROM users WHERE email = '[email protected]'"
)->fetchColumn();
echo "Skema dijalankan dua kali, total pengguna: $jumlah, baris [email protected]: $seedBudi\n";

Output yang Dihasilkan:

Pengguna baru tersimpan dengan id: 4
Email duplikat ditolak [23000]
  SQLSTATE[23000]: Integrity constraint violation: 19 UNIQUE constraint failed: users.email
Email beda kapital ditolak [23000]
  SQLSTATE[23000]: Integrity constraint violation: 19 UNIQUE constraint failed: users.email
Skema dijalankan dua kali, total pengguna: 4, baris [email protected]: 1

Kedua pelanggaran ditolak dengan kode SQLSTATE 23000 — kode standar industri untuk pelanggaran constraint — dan pesan kedua adalah bukti COLLATE NOCASE bekerja: [email protected] dianggap sama dengan [email protected] meskipun bytenya berbeda. Baris terakhir membuktikan sifat idempoten: schema.sql dijalankan untuk kedua kalinya setelah Tono masuk, total pengguna tetap empat (tiga seed ditambah Tono), dan seed Budi tetap satu baris karena INSERT OR IGNORE melewati email yang sudah ada. Pesan mentah semacam ini pantas masuk log developer, tetapi bukan pesan yang layak ditampilkan ke pengguna akhir — Bagian 2 akan menerjemahkannya menjadi pesan yang ramah.

Cara Menjalankan Contoh #

Seluruh snippet pada bagian ini membutuhkan schema.sql yang berada di folder yang sama dengan skrip PHP. Susun foldernya sebagai berikut:

sistem-autentikasi/
└── bagian-1-user-schema/
    ├── schema.sql
    └── uji-skema.php

Simpan snippet mana pun dari halaman ini sebagai uji-skema.php di samping schema.sql, lalu jalankan dari folder mana pun:

php uji-skema.php

PHP membaca schema.sql relatif terhadap __DIR__, yaitu folder tempat uji-skema.php berada, sehingga kedua berkas cukup tetap satu folder. Seluruh contoh memakai :memory:, jadi tidak ada berkas database yang tersisa setelah eksekusi. Bagian-bagian berikutnya akan merujuk schema.sql ini lewat path relatif ../bagian-1-user-schema/schema.sql.

Apa yang Berubah #

Proyek dimulai dari folder kosong dan kini memiliki fondasi data yang lengkap:

• Berkas schema.sql menjadi satu-satunya sumber kebenaran struktur pengguna: enam kolom, satu index eksplisit pada dibuat_pada, plus index otomatis dari UNIQUE email.
• Keunikan email berlaku tanpa membedakan kapital berkat COLLATE NOCASE, dan perilakunya sudah dibuktikan lewat percobaan pelanggaran.
• Tiga pengguna awal menyertai skema dengan hash bcrypt konstanta dan kata sandi yang terdokumentasi, membuat setiap demo pada bagian berikutnya langsung berisi data.
• Anatomi hash bcrypt (60 karakter, awalan $2y$, cost 12 pada PHP 8.4) sudah dibedah, beserta kebiasaan hanya mencetak fakta struktural.

Ringkasan #

Desain skema menegakkan aturan autentikasi persis di tempat yang paling sulit dilanggar: database. Email unik tanpa peduli kapital, kata sandi hanya hidup sebagai hash bcrypt, verifikasi email berbentuk penanda waktu yang bisa NULL, dan seluruh aturan tersebut dibuktikan lewat sqlite_master, PRAGMA table_info, serta tiga percobaan penyisipan. Struktur inilah yang akan dipakai berulang kali oleh lapisan PHP. Pada Bagian 2 kita menulis UserRepository dan RegistrationService — kode yang mengubah skema ini menjadi alur registrasi sungguhan dengan validasi dan penanganan duplikat yang ramah pengguna.

Referensi #

• password_hash() — php.net
• password_verify() — php.net
• SQLite — CREATE TABLE
• OWASP Password Storage Cheat Sheet

-- schema.sql - struktur database sistem autentikasi.
-- Aman dijalankan berulang: CREATE TABLE dan CREATE INDEX memakai
-- IF NOT EXISTS, sedangkan data awal memakai INSERT OR IGNORE.

CREATE TABLE IF NOT EXISTS users (
    id                INTEGER PRIMARY KEY AUTOINCREMENT,
    email             TEXT    NOT NULL UNIQUE COLLATE NOCASE,
    password_hash     TEXT    NOT NULL,
    nama              TEXT    NOT NULL,
    email_verified_at TEXT    NULL,
    dibuat_pada       TEXT    NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX IF NOT EXISTS idx_users_dibuat_pada ON users(dibuat_pada);

-- Data awal. Nilai password_hash di bawah adalah keluaran password_hash()
-- PHP (bcrypt, cost default PHP 8.4) untuk kata sandi demo:
--   [email protected] -> sandi-budi-2026
--   [email protected] -> sandi-siti-2026
--   [email protected] -> sandi-dewi-2026
-- SQL tidak memiliki fungsi bcrypt, sehingga hash memang dihitung di PHP
-- lalu ditempel sebagai konstanta; prosesnya dibuktikan pada Bagian 1.
-- Hash tidak pernah dicetak ke output program - yang dicetak hanya
-- fakta struktural (panjang, awalan, hasil password_verify()).
INSERT OR IGNORE INTO users (email, password_hash, nama, email_verified_at, dibuat_pada) VALUES
    ('[email protected]', '$2y$12$Wb28CVffgGb2ZhdUhdrGi.SJ4d7lGrnxKzh05Uy4oZgWQesrExyCS', 'Budi Santoso',  '2026-01-11 09:00:00', '2026-01-10 09:15:00'),
    ('[email protected]', '$2y$12$ISJ1enIuLhADeRrLspWbQ.LDY85Gbg1KbYzYm6uXxRpb/yZDtt6ku', 'Siti Nurhaliza', NULL,                  '2026-01-12 10:30:00'),
    ('[email protected]', '$2y$12$l9fO6eNBq118eSddkWU8feS/9KTa175PPNEUxRdvuC/BIp2DAn6UK', 'Dewi Lestari',  NULL,                  '2026-01-13 14:05:00');