Pasirašytojų lentelė ir transakcijų vamzdynas

Penki failai kortelėse, iš apačios į viršų: lentelė, jos užklausos, statuso pervertimo užklausa ir transakcijų vamzdynas. 00005_signers.sql → signers.sql → documents.sql → handlers.go → main.go.

1. db/migrations/00005_signers.sql (naujas) — signers lentelė. Svarbūs stulpeliai: document_id (FK, ON DELETE CASCADE), email, token_hash TEXT NOT NULL UNIQUE, status (pending/signed), trys parašo stulpeliai (signed_name, signed_at, signed_doc_hash — užpildomi kitą pamoką) ir expires_at. UNIQUE (document_id, email) neleidžia to paties žmogaus pakviesti prie to paties dokumento du kartus. token_hash UNIQUE yra tai, kas leidžia žetonui sutapti su lygiai viena eilute.

2. internal/db/queries/signers.sql (naujas) — kol kas dvi užklausos: CreateSigner (įterpimas, RETURNING *) ir ListSignersByDocument (sąrašas). Pasirašymo + skaičiavimo užklausos atkeliaus kitą pamoką. Paleisk sqlc generate.

3. internal/db/queries/documents.sql (keitimas) — viena nauja užklausa, MarkDocumentSent: UPDATE ... SET status='sent' WHERE id=$1 AND owner_id=$2 AND status='draft'. Savininkui apribota ir juodraščiu apsaugota — ji negali perversti dokumento, kuris ne tavo ar ne juodraštis.

4. internal/handlers/handlers.go (keitimas) — Handlers struktūra įgyja Pool *pgxpool.Pool, o New jį priima. Užklausų metodai eina per Queries; neapdorotas Pool reikalingas tik transakcijoms atverti (pool.Begin). Tai pirmas valdiklis, kuriam reikia daugiau nei vieno atominio rašymo.

5. cmd/signflow/main.go (keitimas) — paduok jau atvertą telkinį į New:

h := handlers.New(cfg, pool, queries, sessions, mailer, store, log)

pool yra tas pats *pgxpool.Pool, ant kurio pastatytos užklausos; dabar valdikliai jį laiko ir tiesiogiai, kad galėtų atverti transakciją, kai vieno rašymo nepakanka.

00005_signers.sql SUKURTI

Sukurk šį failą kataloge db/migrations/

-- +goose Up
-- +goose StatementBegin
-- A signer is invited to sign one document. A signer is NOT a user: they have no
-- account. The tokened link is the entire authorization — whoever holds the raw
-- token may view and sign this one row. We store only the token's SHA-256 hash,
-- consistent with sessions and password resets.
--
-- The signature itself is captured inline on the row when they sign:
--   signed_name     — what they typed
--   signed_at       — when
--   signed_doc_hash — SHA-256 of the document AT SIGNING TIME
-- That triple is the whole (simplified) signature: tamper-evident integrity and
-- nothing more. See the README's honesty caveat.
CREATE TABLE signers (
    id              UUID        PRIMARY KEY DEFAULT gen_random_uuid(),
    document_id     UUID        NOT NULL REFERENCES documents(id) ON DELETE CASCADE,
    email           TEXT        NOT NULL,
    token_hash      TEXT        NOT NULL UNIQUE,
    status          TEXT        NOT NULL DEFAULT 'pending'
                    CHECK (status IN ('pending', 'signed')),
    signed_name     TEXT,
    signed_at       TIMESTAMPTZ,
    signed_doc_hash TEXT,
    expires_at      TIMESTAMPTZ NOT NULL,
    created_at      TIMESTAMPTZ NOT NULL DEFAULT now(),
    -- Can't invite the same email to the same document twice.
    UNIQUE (document_id, email)
);
CREATE INDEX idx_signers_document_id ON signers(document_id);
-- +goose StatementEnd

-- +goose Down
-- +goose StatementBegin
DROP TABLE signers;
-- +goose StatementEnd

signers.sql SUKURTI

Sukurk šį failą kataloge internal/db/queries/

-- name: CreateSigner :one
INSERT INTO signers (document_id, email, token_hash, expires_at)
VALUES ($1, $2, $3, $4)
RETURNING *;

-- name: ListSignersByDocument :many
SELECT * FROM signers WHERE document_id = $1 ORDER BY created_at;

documents.sql MODIFIKUOTI 6 eilutės pakeistos

Redaguok šį failą kataloge internal/db/queries/

-- name: CreateDocument :one
INSERT INTO documents (owner_id, filename, content_type, size, file_hash, storage_key)
VALUES ($1, $2, $3, $4, $5, $6)
RETURNING *;

-- name: ListDocumentsByOwner :many
SELECT * FROM documents
WHERE owner_id = $1
ORDER BY created_at DESC;

-- name: GetDocumentForOwner :one
-- Owner-scoped read: returns nothing if the document belongs to someone else,
-- so authorization is enforced in the query, not just the handler.
SELECT * FROM documents
WHERE id = $1 AND owner_id = $2;

-- name: MarkDocumentSent :execrows
-- draft -> sent. Owner-scoped and draft-guarded, so it is idempotent-safe and
-- can't resurrect a completed document.
UPDATE documents SET status = 'sent'
WHERE id = $1 AND owner_id = $2 AND status = 'draft';

-- name: DeleteDraftDocument :execrows
-- Deletes only if the caller owns it AND it is still a draft. Returns the number
-- of rows affected so the handler can tell "deleted" from "not allowed".
DELETE FROM documents
WHERE id = $1 AND owner_id = $2 AND status = 'draft';

handlers.go MODIFIKUOTI 5 eilutės pakeistos

Redaguok šį failą kataloge internal/handlers/

// Package handlers holds the HTTP handlers. In this skeleton there is only the
// home page and a health check; later lessons add auth, documents, and signing.
package handlers

import (
	"log/slog"
	"net/http"

	"github.com/jackc/pgx/v5/pgxpool"

	"github.com/programuoki/signflow/internal/config"
	"github.com/programuoki/signflow/internal/db"
	"github.com/programuoki/signflow/internal/email"
	"github.com/programuoki/signflow/internal/session"
	"github.com/programuoki/signflow/internal/storage"
	"github.com/programuoki/signflow/internal/web"
)

// Handlers bundles the dependencies every handler needs. Constructed once in
// main; its methods are registered on the router.
type Handlers struct {
	Cfg      config.Config
	Pool     *pgxpool.Pool // for multi-statement transactions
	Queries  *db.Queries
	Sessions *session.Manager
	Mailer   email.Sender
	Store    storage.Store
	Log      *slog.Logger
}

func New(cfg config.Config, pool *pgxpool.Pool, q *db.Queries, sessions *session.Manager, mailer email.Sender, store storage.Store, log *slog.Logger) *Handlers {
	return &Handlers{Cfg: cfg, Pool: pool, Queries: q, Sessions: sessions, Mailer: mailer, Store: store, Log: log}
}

// Home renders the landing page. A signed-in visitor is sent to their dashboard;
// otherwise we show the public landing page with a live user count.
func (h *Handlers) Home(w http.ResponseWriter, r *http.Request) {
	if _, ok := UserFrom(r.Context()); ok {
		http.Redirect(w, r, "/dashboard", http.StatusSeeOther)
		return
	}
	count, err := h.Queries.CountUsers(r.Context())
	if err != nil {
		h.serverError(w, r, "count users", err)
		return
	}
	render(w, r, http.StatusOK, web.Home(h.nav(r), count))
}

// Health is a liveness probe for Railway / uptime checks. It also pings the DB,
// so an unreachable database reports unhealthy rather than silently serving 500s.
func (h *Handlers) Health(w http.ResponseWriter, r *http.Request) {
	if _, err := h.Queries.CountUsers(r.Context()); err != nil {
		http.Error(w, "db unavailable", http.StatusServiceUnavailable)
		return
	}
	w.Header().Set("Content-Type", "text/plain")
	_, _ = w.Write([]byte("ok"))
}

// serverError logs the underlying error and returns a generic 500 to the client.
func (h *Handlers) serverError(w http.ResponseWriter, r *http.Request, msg string, err error) {
	h.Log.Error(msg, "err", err, "path", r.URL.Path)
	http.Error(w, "internal server error", http.StatusInternalServerError)
}

main.go MODIFIKUOTI 1 eilutė pakeista

Redaguok šį failą kataloge cmd/signflow/

// Command signflow is the HTTP server entrypoint.
//
// On startup it: loads config, runs embedded goose migrations, opens a pgx
// pool, builds the Chi router, and serves with graceful shutdown.
package main

import (
	"context"
	"crypto/sha256"
	"errors"
	"fmt"
	"io/fs"
	"log/slog"
	"net/http"
	"os"
	"os/signal"
	"syscall"
	"time"

	"github.com/jackc/pgx/v5/stdlib"
	"github.com/pressly/goose/v3"

	migrations "github.com/programuoki/signflow/db"
	"github.com/programuoki/signflow/internal/config"
	"github.com/programuoki/signflow/internal/db"
	"github.com/programuoki/signflow/internal/email"
	"github.com/programuoki/signflow/internal/handlers"
	"github.com/programuoki/signflow/internal/session"
	"github.com/programuoki/signflow/internal/storage"
	"github.com/programuoki/signflow/static"
)

func main() {
	log := slog.New(slog.NewTextHandler(os.Stdout, &slog.HandlerOptions{Level: slog.LevelInfo}))
	if err := run(log); err != nil {
		log.Error("fatal", "err", err)
		os.Exit(1)
	}
}

func run(log *slog.Logger) error {
	cfg, err := config.Load()
	if err != nil {
		return err
	}
	log.Info("starting signflow", "env", cfg.Env, "port", cfg.Port, "base_url", cfg.BaseURL)

	ctx := context.Background()

	// 1. Migrate. We use a throwaway database/sql handle (pgx stdlib driver)
	//    because goose speaks database/sql, then hand the app a pgx pool.
	if err := runMigrations(cfg.DatabaseURL, log); err != nil {
		return err
	}

	// 2. Connect the application pool the sqlc queries run against.
	pool, err := db.Connect(ctx, cfg.DatabaseURL)
	if err != nil {
		return err
	}
	defer pool.Close()

	queries := db.New(pool)
	sessions := session.NewManager(queries, cfg.IsProd())
	mailer := email.New(cfg.EmailSender, cfg.ResendAPIKey, cfg.EmailFrom, log)
	store, err := storage.NewLocalStore(cfg.UploadDir)
	if err != nil {
		return err
	}
	log.Info("file storage: local disk", "dir", cfg.UploadDir)
	h := handlers.New(cfg, pool, queries, sessions, mailer, store, log)

	staticFS, err := fs.Sub(static.FS, "assets")
	if err != nil {
		return err
	}

	// Derive a stable 32-byte CSRF key from the session secret so there is only
	// one secret to manage.
	csrfKey := sha256.Sum256([]byte("csrf:" + cfg.SessionSecret))

	srv := &http.Server{
		Addr:              ":" + cfg.Port,
		Handler:           h.Router(staticFS, csrfKey[:]),
		ReadHeaderTimeout: 10 * time.Second,
	}

	// Graceful shutdown: stop accepting on SIGINT/SIGTERM, then drain in-flight
	// requests for up to 15s before exiting.
	shutdownCtx, stop := signal.NotifyContext(ctx, os.Interrupt, syscall.SIGTERM)
	defer stop()

	go func() {
		log.Info("listening", "addr", srv.Addr)
		if err := srv.ListenAndServe(); err != nil && !errors.Is(err, http.ErrServerClosed) {
			log.Error("server error", "err", err)
			stop()
		}
	}()

	<-shutdownCtx.Done()
	log.Info("shutting down")

	timeout, cancel := context.WithTimeout(context.Background(), 15*time.Second)
	defer cancel()
	return srv.Shutdown(timeout)
}

// runMigrations applies all pending goose migrations from the embedded FS.
func runMigrations(databaseURL string, log *slog.Logger) error {
	sqlDB, err := goose.OpenDBWithDriver("pgx", databaseURL)
	if err != nil {
		return err
	}
	defer sqlDB.Close()

	goose.SetBaseFS(migrations.FS)
	goose.SetLogger(gooseLogger{log})
	if err := goose.SetDialect("postgres"); err != nil {
		return err
	}
	return goose.Up(sqlDB, "migrations")
}

// gooseLogger adapts slog to goose's logger interface.
type gooseLogger struct{ log *slog.Logger }

func (g gooseLogger) Fatalf(format string, v ...interface{}) {
	g.log.Error("goose", "msg", fmt.Sprintf(format, v...))
}
func (g gooseLogger) Printf(format string, v ...interface{}) {
	g.log.Info("goose", "msg", fmt.Sprintf(format, v...))
}

// ensure the pgx stdlib driver is registered for goose.OpenDBWithDriver("pgx", ...)
var _ = stdlib.GetDefaultDriver