SuperAPI AI Directory
Partner with us中文AI newsFavorites
Skillslight-system-design

light-system-design

Design & creation

View repository

Updated 10-16 00:00

About this skill

Backend Systems and Database Design

Applicable Scenarios

Research systems, management systems, data analysis/visualization platforms, competition works, software copyright application projects. The design must be both implementable (handed to a03 for implementation) and writable into software copyright/thesis documents (handed to m15/m07).

System Architecture

  • Module division (layered: interface layer/business layer/data layer), responsibility boundaries, data flow diagram.
  • Technology selection as needed (see a09; for each framework's starter/layering/configuration see references.md):FastAPI(routing + Pydantic validation +Depends injection +/docs Swagger)、Django REST(serializer+ModelViewSet+Router，REST_FRAMEWORK centralized configuration),Spring Boot(@RestController→@Service→@Repository layering + constructor injection + multiple profiles).
  • Non-functional: performance (caching/indexing/pagination), scalability, observability.
  • Vector Retrieval Selection(commonly used in research RAG/semantic retrieval): for up to millions, prefer pgvector + HNSW(one Postgres manages both relational and vector data, simple operations); the operator class and query distance must be consistent (cosine→vector_cosine_ops); only for tens of millions to hundreds of millions, high QPS, or when distribution is required should a dedicated vector database be used (Milvus/Qdrant/Weaviate). For selection comparison see the "pgvector + HNSW" section in references.
  • Observability: research AI services use OpenTelemetry to uniformly collect trace/metric/log (opentelemetry-instrument automatically instruments without code changes, exports via OTLP to Jaeger/Prometheus), to locate "where the time of one inference is spent, which step errors"; do not debug with print; pay attention to the sampling rate and do not write keys/PII into spans. See the "OpenTelemetry" section in references for details.

Database Design

  • ER Diagram: entities, relationships, cardinality. You can use scripts/er_diagram.py to convert table structure definitions (YAML/JSON: entities + columns(PK/FK/UK) + relationships(cardinality)) into Mermaid erDiagram text in one click, paste into Markdown/software copyright documents or preview at mermaid.live (python scripts/er_diagram.py --in schema.yaml, purely offline).
  • Table Structure: fields, types, constraints, primary/foreign keys, normalization vs denormalization trade-offs. Choose deliberately; do not blindly use text/oversized numeric.
  • Indexes: select by query pattern—B-Tree (equality/range/sort/prefix LIKE 'x%'), GIN (array/JSONB/full-text/trigram), BRIN (very large tables + physically ordered time columns), GiST (geometry/nearest neighbor), Hash (pure equality).Foreign keys must have indexes(the most common omission); for production index creation use CREATE INDEX CONCURRENTLY to avoid table locking; before release use EXPLAIN ANALYZE to verify the plan.
  • Migration：Alembic revision --autogenerate after generationmust be manually reviewed(it cannot detect renames/anonymous constraints and reports them as drop+add),upgrade head apply, CI alembic check; Prisma development migrate dev、production can only migrate deploy(no reset/shadow DB), for manual SQL changes use --create-only。
  • ORM prevents N+1: SQLAlchemy uses selectinload(secondary IN query)/joinedload(JOIN) preloading; DRF uses select_related/prefetch_related. Connections must use a connection pool.
  • Best-practices reference supabase-postgres-best-practices (the security section below contains RLS specifics and naming conventions).

Interface Design

  • RESTful/OpenAPI 3.x spec: top-level openapi/info/servers(complete base URL, versioning placed in /v1)/paths/components。
  • Resource naming, methods, status codes (201 Created/204/4xx), versioning.
  • Request/response schemas go in components.schemas use $ref for reuse;securitySchemes define bearer/oauth2.
  • Error code system, pagination (cursor preferred over large OFFSET)/filtering/sorting conventions.
  • API docs can be exported: FastAPI /openapi.json, DRF/Spring can all auto-generate OpenAPI; when writing by hand, don't omit error response codes and examples.

Security and Operations

  • Permissions: authentication (JWT/session) + authorization (RBAC); principle of least privilege.
  • Row-level security (RLS): multi-tenant tables enable row level security + explicit policy, don't rely only on application-layer WHERE; for performance wrap functions in select to trigger initPlan caching, index columns referenced by policies, always write the to clause, for complex cross-table use security definer function; use raw_app_meta_datafor authorization data (users cannot modify it). See the references "supabase RLS" section for the skeleton and syntax.
  • Caching (Redis): Cache-aside (check cache → on miss read DB → write back with TTL);maxmemory-policy default allkeys-lru; add random jitter to TTL to prevent avalanche, cache null values to prevent penetration; monitor hit rate and evicted_keys。
  • Logging: leveled logging, request tracing, audit logs.
  • Exception handling: unified exception responses, don't leak internal details.
  • Security policy: input validation, injection prevention, rate limiting, secret management (not stored in DB / not hardcoded).
  • Deployment(full config/skeleton see references.md and templates/ci.yml): Docker multi-stage build, dependency manifest before source to protect cache, non-root + slim/distroless + pin digest; K8s Deployment + three probes (readiness/liveness/startup) + requests/limits + HPA, Secret with RBAC; Nginx reverse proxy pass-through XFF/TLS termination/limit_req, after changing config nginx -t reload; CI (Actions) checkout→install→lint→test→alembic check/migrate deploy, least privilege contents: read, rollback kubectl rollout undo. ⚠ If a service is exposed to the network with no authentication, you must proactively flag it (security_awareness).

Deliverables

Architecture diagram + ER diagram + table structure DDL + API docs (OpenAPI) + permissions/security/deployment documentation. The design document can be fed directly into the m15 software copyright feature description. See the starter skeleton at templates/(schema.sql / openapi.yaml / rls_policy.sql / ci.yml), just change the fields per project.

Connection

Design → a03 implementation → a06 organize directory; keep consistent with the thesis system description and software copyright materials (a07); versions go into db09.

For each tool's real endpoints/syntax/config/known pitfalls see references.md (per-tool verification notes).