Veriyi tasarlamak.

-- fkdev şeması · MySQL 8 · utf8mb4
CREATE TABLE users (
  id            BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  email         VARCHAR(190) NOT NULL UNIQUE,
  name          VARCHAR(120) NOT NULL,
  password_hash VARCHAR(255) NOT NULL,
  role          ENUM('admin','editor','viewer') NOT NULL DEFAULT 'viewer',
  is_active     TINYINT(1) NOT NULL DEFAULT 1,
  last_login_at DATETIME NULL,
  created_at    TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at    TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE sessions (
  id          CHAR(64) PRIMARY KEY,
  user_id     BIGINT UNSIGNED NOT NULL,
  ip          VARCHAR(45) NOT NULL,
  user_agent  VARCHAR(500) NOT NULL DEFAULT '',
  expires_at  DATETIME NOT NULL,
  created_at  TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_sessions_user (user_id),
  CONSTRAINT fk_sessions_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE projects (
  id          BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  slug        VARCHAR(120) NOT NULL UNIQUE,
  title       VARCHAR(160) NOT NULL,
  summary     VARCHAR(500) NOT NULL DEFAULT '',
  url         VARCHAR(255) NULL,
  status      ENUM('draft','live','archived') NOT NULL DEFAULT 'draft',
  sort_order  INT NOT NULL DEFAULT 0,
  created_at  TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at  TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE uploads (
  id             BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  project_id     BIGINT UNSIGNED NULL,
  original_name  VARCHAR(255) NOT NULL,
  stored_path    VARCHAR(255) NOT NULL,
  mime_type      VARCHAR(128) NOT NULL,
  size_bytes     BIGINT UNSIGNED NOT NULL,
  sha256         CHAR(64) NOT NULL,
  uploader_name  VARCHAR(120) NOT NULL,
  uploader_email VARCHAR(190) NOT NULL,
  status         ENUM('new','seen','flagged') NOT NULL DEFAULT 'new',
  ip             VARCHAR(45) NOT NULL,
  created_at     TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_uploads_created (created_at),
  INDEX idx_uploads_email (uploader_email),
  CONSTRAINT fk_uploads_project FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE messages (
  id          BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  channel     ENUM('contact','hr') NOT NULL DEFAULT 'contact',
  name        VARCHAR(120) NOT NULL,
  email       VARCHAR(190) NOT NULL,
  subject     VARCHAR(160) NOT NULL DEFAULT '',
  body        TEXT NOT NULL,
  is_read     TINYINT(1) NOT NULL DEFAULT 0,
  ip          VARCHAR(45) NOT NULL,
  created_at  TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_messages_unread (is_read, created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE ai_conversations (
  id          BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id     BIGINT UNSIGNED NULL,
  visitor_id  CHAR(36) NULL,
  title       VARCHAR(160) NOT NULL DEFAULT 'Yeni sohbet',
  model       VARCHAR(64) NOT NULL,
  created_at  TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_ai_conv_user (user_id),
  CONSTRAINT fk_ai_conv_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE ai_messages (
  id               BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  conversation_id  BIGINT UNSIGNED NOT NULL,
  role             ENUM('user','assistant') NOT NULL,
  content          MEDIUMTEXT NOT NULL,
  input_tokens     INT UNSIGNED NOT NULL DEFAULT 0,
  output_tokens    INT UNSIGNED NOT NULL DEFAULT 0,
  stop_reason      VARCHAR(32) NULL,
  created_at       TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_ai_msg_conv (conversation_id, id),
  CONSTRAINT fk_ai_msg_conv FOREIGN KEY (conversation_id) REFERENCES ai_conversations(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE access_logs (
  id          BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id     BIGINT UNSIGNED NULL,
  ip          VARCHAR(45) NOT NULL,
  method      VARCHAR(10) NOT NULL,
  path        VARCHAR(500) NOT NULL,
  status      SMALLINT UNSIGNED NOT NULL,
  blocked     TINYINT(1) NOT NULL DEFAULT 0,
  reason      VARCHAR(120) NULL,
  created_at  TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_logs_created (created_at),
  INDEX idx_logs_ip (ip)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE settings (
  name        VARCHAR(64) PRIMARY KEY,
  value       JSON NOT NULL,
  updated_at  TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

<?php
declare(strict_types=1);

function db(): PDO {
  static $pdo = null;
  if ($pdo) return $pdo;
  $dsn = sprintf('mysql:host=%s;dbname=%s;charset=utf8mb4', getenv('DB_HOST'), getenv('DB_NAME'));
  $pdo = new PDO($dsn, getenv('DB_USER'), getenv('DB_PASS'), [
    PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
    PDO::ATTR_EMULATE_PREPARES   => false,
  ]);
  return $pdo;
}

<?php
$stmt = db()->prepare('SELECT id, name, role, password_hash FROM users WHERE email = ? AND is_active = 1 LIMIT 1');
$stmt->execute([$_POST['email'] ?? '']);
$user = $stmt->fetch();

if (!$user || !password_verify($_POST['password'] ?? '', $user['password_hash'])) {
  http_response_code(401);
  exit('E-posta ya da şifre hatalı.');
}

SELECT DATE(created_at) AS gun, COUNT(*) AS adet, SUM(size_bytes) AS toplam
FROM uploads
WHERE created_at >= NOW() - INTERVAL 12 DAY
GROUP BY DATE(created_at)
ORDER BY gun;

SELECT COUNT(*) FROM messages WHERE is_read = 0;

SELECT role, content FROM ai_messages
WHERE conversation_id = ?
ORDER BY id
LIMIT 50;

DELETE FROM access_logs WHERE created_at < NOW() - INTERVAL 90 DAY;

migrations/
├── 001_create_users.sql
├── 002_create_uploads.sql
└── 003_add_status_to_uploads.sql

ALTER TABLE uploads ADD COLUMN status ENUM('new','seen','flagged') NOT NULL DEFAULT 'new';