backend-php/migrations/20260223_add_document_table.sql

BEGIN;

CREATE TABLE IF NOT EXISTS "document" (
  "document_id" SERIAL PRIMARY KEY,
  "user_id" INTEGER NOT NULL,
  "document_type" TEXT NOT NULL,
  "document_path" TEXT NOT NULL
);

DO $$
BEGIN
  IF NOT EXISTS (
    SELECT 1
    FROM pg_constraint
    WHERE conname = 'document_user_id_fkey'
  ) THEN
    ALTER TABLE "document"
      ADD CONSTRAINT document_user_id_fkey
      FOREIGN KEY ("user_id") REFERENCES "user"("user_id")
      ON DELETE CASCADE;
  END IF;
END $$;

CREATE INDEX IF NOT EXISTS idx_document_user_id ON "document"("user_id");
CREATE INDEX IF NOT EXISTS idx_document_user_id_type ON "document"("user_id", "document_type");

COMMIT;