mirror of
https://github.com/opelly27/WinStudentGoalTracker.git
synced 2026-05-20 01:47:41 +00:00
ivan-pelly
2894 lines
101 KiB
HTML
2894 lines
101 KiB
HTML
<!DOCTYPE html>
|
||
<html lang="en">
|
||
|
||
<head>
|
||
<meta charset="UTF-8" />
|
||
<meta name="viewport" content="width=device-width, initial-scale=1.0" />
|
||
<title>WIN Student Goal Tracker – Developer Documentation</title>
|
||
<meta name="description" content="Developer documentation for the WIN Student Goal Tracker project." />
|
||
<link
|
||
href="https://fonts.googleapis.com/css2?family=IBM+Plex+Mono:wght@400;500;600&family=DM+Sans:wght@400;500;600;700&display=swap"
|
||
rel="stylesheet">
|
||
<style>
|
||
:root {
|
||
--bg: #eef3f9;
|
||
--surface: #ffffff;
|
||
--surface-2: #f8fbff;
|
||
--sidebar: #0f172a;
|
||
--sidebar-text: #cbd5e1;
|
||
--text: #1f2937;
|
||
--heading: #0f172a;
|
||
--accent: #2563eb;
|
||
--accent-soft: #dbeafe;
|
||
--border: #dbe3f0;
|
||
--shadow: 0 10px 30px rgba(15, 23, 42, .08);
|
||
--radius: 16px;
|
||
}
|
||
|
||
* {
|
||
box-sizing: border-box
|
||
}
|
||
|
||
html {
|
||
scroll-behavior: smooth
|
||
}
|
||
|
||
body {
|
||
margin: 0;
|
||
font-family: Inter, Arial, Helvetica, sans-serif;
|
||
background: linear-gradient(180deg, #f8fbff 0%, #eef3f9 100%);
|
||
color: var(--text);
|
||
line-height: 1.65;
|
||
}
|
||
|
||
.page {
|
||
display: grid;
|
||
grid-template-columns: 280px minmax(0, 1fr);
|
||
gap: 24px;
|
||
max-width: 1320px;
|
||
margin: auto;
|
||
padding: 24px;
|
||
align-items: start;
|
||
}
|
||
|
||
.sidebar {
|
||
position: sticky;
|
||
top: 20px;
|
||
background: var(--sidebar);
|
||
color: var(--sidebar-text);
|
||
padding: 22px 18px;
|
||
border-radius: 20px;
|
||
box-shadow: var(--shadow);
|
||
height: fit-content;
|
||
}
|
||
|
||
.sidebar .brand {
|
||
font-size: 1rem;
|
||
font-weight: 700;
|
||
color: #fff;
|
||
margin-bottom: 18px;
|
||
padding-bottom: 14px;
|
||
border-bottom: 1px solid rgba(255, 255, 255, .08);
|
||
}
|
||
|
||
.sidebar a {
|
||
display: block;
|
||
padding: 10px 12px;
|
||
margin: 4px 0;
|
||
border-radius: 10px;
|
||
text-decoration: none;
|
||
color: var(--sidebar-text);
|
||
font-size: .95rem;
|
||
transition: all .2s ease;
|
||
}
|
||
|
||
.sidebar a:hover {
|
||
background: rgba(255, 255, 255, .08);
|
||
color: #fff;
|
||
}
|
||
|
||
.content {
|
||
min-width: 0;
|
||
}
|
||
|
||
.hero {
|
||
background: linear-gradient(135deg, rgba(37, 99, 235, .95), rgba(15, 23, 42, .95));
|
||
color: white;
|
||
padding: 38px 36px;
|
||
border-radius: 22px;
|
||
box-shadow: var(--shadow);
|
||
margin-bottom: 22px;
|
||
}
|
||
|
||
.hero h1 {
|
||
margin: 0 0 10px;
|
||
font-size: 2.1rem;
|
||
line-height: 1.15;
|
||
letter-spacing: -0.02em;
|
||
color: #fff;
|
||
}
|
||
|
||
.hero p {
|
||
margin: 4px 0;
|
||
color: rgba(255, 255, 255, .9);
|
||
}
|
||
|
||
.meta {
|
||
display: inline-block;
|
||
background: rgba(255, 255, 255, .14);
|
||
color: #fff;
|
||
border-radius: 999px;
|
||
padding: 6px 12px;
|
||
font-size: .88rem;
|
||
font-weight: 600;
|
||
margin-bottom: 12px;
|
||
}
|
||
|
||
section {
|
||
background: var(--surface);
|
||
padding: 28px;
|
||
border-radius: 20px;
|
||
margin-bottom: 20px;
|
||
border: 1px solid var(--border);
|
||
box-shadow: var(--shadow);
|
||
scroll-margin-top: 20px;
|
||
}
|
||
|
||
h2 {
|
||
margin: 0 0 14px;
|
||
color: var(--heading);
|
||
font-size: 1.45rem;
|
||
letter-spacing: -0.01em;
|
||
padding-bottom: 10px;
|
||
border-bottom: 2px solid var(--accent-soft);
|
||
}
|
||
|
||
h3 {
|
||
margin: 22px 0 10px;
|
||
color: var(--heading);
|
||
font-size: 1.05rem;
|
||
}
|
||
|
||
p {
|
||
margin: 10px 0 0;
|
||
}
|
||
|
||
ul,
|
||
ol {
|
||
padding-left: 22px;
|
||
margin: 10px 0 0;
|
||
}
|
||
|
||
li {
|
||
margin: 8px 0;
|
||
}
|
||
|
||
.stack {
|
||
display: grid;
|
||
grid-template-columns: repeat(auto-fit, minmax(220px, 1fr));
|
||
gap: 14px;
|
||
margin-top: 14px;
|
||
}
|
||
|
||
.card {
|
||
border: 1px solid var(--border);
|
||
background: var(--surface-2);
|
||
padding: 16px;
|
||
border-radius: 14px;
|
||
box-shadow: 0 4px 14px rgba(15, 23, 42, .04);
|
||
}
|
||
|
||
.card strong {
|
||
display: block;
|
||
margin-bottom: 6px;
|
||
color: var(--heading);
|
||
}
|
||
|
||
.flow {
|
||
background: linear-gradient(90deg, #eff6ff, #f8fbff);
|
||
border: 1px solid #bfdbfe;
|
||
border-left: 5px solid var(--accent);
|
||
color: #1e3a8a;
|
||
padding: 14px 16px;
|
||
border-radius: 12px;
|
||
margin: 10px 0 14px;
|
||
font-family: "Courier New", Courier, monospace;
|
||
font-size: .95rem;
|
||
overflow: auto;
|
||
}
|
||
|
||
pre {
|
||
background: #0b1220;
|
||
color: #e5e7eb;
|
||
padding: 16px 18px;
|
||
border-radius: 14px;
|
||
overflow: auto;
|
||
border: 1px solid #1e293b;
|
||
box-shadow: inset 0 1px 0 rgba(255, 255, 255, .03);
|
||
margin-top: 12px;
|
||
}
|
||
|
||
code {
|
||
font-family: "Courier New", Courier, monospace;
|
||
}
|
||
|
||
.pill {
|
||
display: inline-block;
|
||
background: var(--accent-soft);
|
||
color: var(--accent);
|
||
border-radius: 999px;
|
||
padding: 6px 12px;
|
||
font-size: .88rem;
|
||
font-weight: 600;
|
||
margin: 10px 8px 0 0;
|
||
}
|
||
|
||
@media (max-width: 980px) {
|
||
.page {
|
||
grid-template-columns: 1fr;
|
||
}
|
||
|
||
.sidebar {
|
||
position: relative;
|
||
top: 0;
|
||
}
|
||
}
|
||
|
||
@media (max-width: 640px) {
|
||
.page {
|
||
padding: 14px;
|
||
gap: 16px;
|
||
}
|
||
|
||
.hero {
|
||
padding: 28px 22px;
|
||
}
|
||
|
||
.hero h1 {
|
||
font-size: 1.7rem;
|
||
}
|
||
|
||
section {
|
||
padding: 20px;
|
||
}
|
||
}
|
||
</style>
|
||
|
||
<style>
|
||
/* ── Appendix C – Architecture Overview (all scoped to #appendix-c) ── */
|
||
.arch-link {
|
||
display: inline-flex;
|
||
align-items: center;
|
||
gap: .4rem;
|
||
font-size: .88rem;
|
||
color: var(--accent);
|
||
text-decoration: none;
|
||
font-weight: 600;
|
||
margin-bottom: 1.2rem;
|
||
}
|
||
|
||
.arch-link:hover {
|
||
text-decoration: underline;
|
||
}
|
||
|
||
#appendix-c .asec-title {
|
||
font-size: 1rem;
|
||
font-weight: 700;
|
||
color: var(--heading);
|
||
margin: 1.8rem 0 .8rem;
|
||
padding-bottom: .35rem;
|
||
border-bottom: 2px solid var(--border);
|
||
display: flex;
|
||
align-items: center;
|
||
gap: .4rem;
|
||
}
|
||
|
||
/* tier diagram */
|
||
#appendix-c .arch-diagram {
|
||
display: flex;
|
||
flex-direction: column;
|
||
gap: 0;
|
||
align-items: stretch;
|
||
}
|
||
|
||
#appendix-c .tier {
|
||
display: flex;
|
||
align-items: stretch;
|
||
gap: .75rem;
|
||
position: relative;
|
||
}
|
||
|
||
#appendix-c .tier-label {
|
||
writing-mode: vertical-rl;
|
||
text-orientation: mixed;
|
||
transform: rotate(180deg);
|
||
font-size: .65rem;
|
||
font-weight: 700;
|
||
letter-spacing: .08em;
|
||
text-transform: uppercase;
|
||
color: white;
|
||
padding: .5rem .35rem;
|
||
border-radius: 8px 0 0 8px;
|
||
display: flex;
|
||
align-items: center;
|
||
justify-content: center;
|
||
min-width: 28px;
|
||
}
|
||
|
||
#appendix-c .tier-body {
|
||
flex: 1;
|
||
display: flex;
|
||
gap: .75rem;
|
||
flex-wrap: wrap;
|
||
padding: .9rem;
|
||
border-radius: 0 12px 12px 0;
|
||
}
|
||
|
||
#appendix-c .tier--client .tier-label {
|
||
background: #2563eb;
|
||
}
|
||
|
||
#appendix-c .tier--client .tier-body {
|
||
background: #eff6ff;
|
||
border: 1.5px solid #bfdbfe;
|
||
}
|
||
|
||
#appendix-c .tier--proxy .tier-label {
|
||
background: #64748b;
|
||
}
|
||
|
||
#appendix-c .tier--proxy .tier-body {
|
||
background: #f8fafc;
|
||
border: 1.5px solid #cbd5e1;
|
||
}
|
||
|
||
#appendix-c .tier--api .tier-label {
|
||
background: #16a34a;
|
||
}
|
||
|
||
#appendix-c .tier--api .tier-body {
|
||
background: #f0fdf4;
|
||
border: 1.5px solid #bbf7d0;
|
||
}
|
||
|
||
#appendix-c .tier--data .tier-label {
|
||
background: #d97706;
|
||
}
|
||
|
||
#appendix-c .tier--data .tier-body {
|
||
background: #fffbeb;
|
||
border: 1.5px solid #fde68a;
|
||
}
|
||
|
||
#appendix-c .tier--ext .tier-label {
|
||
background: #7c3aed;
|
||
}
|
||
|
||
#appendix-c .tier--ext .tier-body {
|
||
background: #faf5ff;
|
||
border: 1.5px solid #ddd6fe;
|
||
}
|
||
|
||
#appendix-c .tier-arrow {
|
||
display: flex;
|
||
align-items: center;
|
||
justify-content: center;
|
||
height: 28px;
|
||
gap: .4rem;
|
||
color: #6b7280;
|
||
font-size: .7rem;
|
||
margin-left: 28px;
|
||
}
|
||
|
||
#appendix-c .tier-arrow svg {
|
||
flex-shrink: 0;
|
||
}
|
||
|
||
/* boxes */
|
||
#appendix-c .box {
|
||
background: white;
|
||
border-radius: 10px;
|
||
padding: .55rem .8rem;
|
||
box-shadow: 0 2px 6px rgba(0, 0, 0, .06);
|
||
border: 1px solid rgba(0, 0, 0, .06);
|
||
min-width: 130px;
|
||
}
|
||
|
||
#appendix-c .box-title {
|
||
font-size: .78rem;
|
||
font-weight: 700;
|
||
margin: 0 0 .25rem;
|
||
color: var(--heading);
|
||
}
|
||
|
||
#appendix-c .box ul {
|
||
list-style: none;
|
||
font-size: .7rem;
|
||
color: #4b5563;
|
||
padding: 0;
|
||
margin: 0;
|
||
}
|
||
|
||
#appendix-c .box ul li {
|
||
margin-bottom: .12rem;
|
||
}
|
||
|
||
#appendix-c .box ul li::before {
|
||
content: "• ";
|
||
color: #9ca3af;
|
||
}
|
||
|
||
#appendix-c .box--blue {
|
||
border-top: 3px solid #2563eb;
|
||
}
|
||
|
||
#appendix-c .box--green {
|
||
border-top: 3px solid #16a34a;
|
||
}
|
||
|
||
#appendix-c .box--amber {
|
||
border-top: 3px solid #d97706;
|
||
}
|
||
|
||
#appendix-c .box--purple {
|
||
border-top: 3px solid #7c3aed;
|
||
}
|
||
|
||
#appendix-c .box--slate {
|
||
border-top: 3px solid #64748b;
|
||
}
|
||
|
||
/* badges */
|
||
#appendix-c .badge {
|
||
display: inline-block;
|
||
font-size: .6rem;
|
||
font-weight: 700;
|
||
padding: .1rem .35rem;
|
||
border-radius: 4px;
|
||
margin-left: .2rem;
|
||
vertical-align: middle;
|
||
}
|
||
|
||
#appendix-c .badge--blue {
|
||
background: #dbeafe;
|
||
color: #1d4ed8;
|
||
}
|
||
|
||
#appendix-c .badge--green {
|
||
background: #dcfce7;
|
||
color: #15803d;
|
||
}
|
||
|
||
#appendix-c .badge--amber {
|
||
background: #fef3c7;
|
||
color: #b45309;
|
||
}
|
||
|
||
#appendix-c .badge--purple {
|
||
background: #ede9fe;
|
||
color: #6d28d9;
|
||
}
|
||
|
||
/* auth flow — renamed .aflow* to avoid conflict with technical.html's .flow rule */
|
||
#appendix-c .aflow {
|
||
display: flex;
|
||
align-items: flex-start;
|
||
gap: 0;
|
||
overflow-x: auto;
|
||
padding-bottom: .5rem;
|
||
}
|
||
|
||
#appendix-c .aflow-step {
|
||
display: flex;
|
||
flex-direction: column;
|
||
align-items: center;
|
||
min-width: 150px;
|
||
max-width: 165px;
|
||
}
|
||
|
||
#appendix-c .aflow-bubble {
|
||
background: white;
|
||
border-radius: 10px;
|
||
padding: .55rem .7rem;
|
||
box-shadow: 0 2px 6px rgba(0, 0, 0, .07);
|
||
border: 1.5px solid var(--border);
|
||
font-size: .7rem;
|
||
text-align: center;
|
||
width: 100%;
|
||
}
|
||
|
||
#appendix-c .aflow-bubble strong {
|
||
display: block;
|
||
font-size: .78rem;
|
||
margin: 0 0 .2rem;
|
||
color: var(--heading);
|
||
font-weight: 700;
|
||
}
|
||
|
||
#appendix-c .aflow-bubble .note {
|
||
color: #6b7280;
|
||
margin-top: .2rem;
|
||
font-style: italic;
|
||
}
|
||
|
||
#appendix-c .aflow-num {
|
||
width: 22px;
|
||
height: 22px;
|
||
border-radius: 50%;
|
||
background: var(--heading);
|
||
color: white;
|
||
font-size: .7rem;
|
||
font-weight: 700;
|
||
display: flex;
|
||
align-items: center;
|
||
justify-content: center;
|
||
margin-bottom: .35rem;
|
||
}
|
||
|
||
#appendix-c .aflow-connector {
|
||
display: flex;
|
||
align-items: center;
|
||
padding-top: 11px;
|
||
flex-shrink: 0;
|
||
}
|
||
|
||
#appendix-c .aflow-connector svg {
|
||
color: #9ca3af;
|
||
}
|
||
|
||
/* data model */
|
||
#appendix-c .entity-grid {
|
||
display: grid;
|
||
grid-template-columns: repeat(auto-fill, minmax(190px, 1fr));
|
||
gap: .7rem;
|
||
}
|
||
|
||
#appendix-c .entity {
|
||
background: white;
|
||
border-radius: 10px;
|
||
box-shadow: 0 2px 6px rgba(0, 0, 0, .06);
|
||
border: 1px solid var(--border);
|
||
overflow: hidden;
|
||
}
|
||
|
||
#appendix-c .entity-header {
|
||
padding: .4rem .7rem;
|
||
font-size: .75rem;
|
||
font-weight: 700;
|
||
color: white;
|
||
}
|
||
|
||
#appendix-c .entity-fields {
|
||
padding: .45rem .7rem;
|
||
font-size: .68rem;
|
||
color: #4b5563;
|
||
}
|
||
|
||
#appendix-c .entity-fields div {
|
||
padding: .1rem 0;
|
||
border-bottom: 1px solid #f3f4f6;
|
||
}
|
||
|
||
#appendix-c .entity-fields div:last-child {
|
||
border-bottom: none;
|
||
}
|
||
|
||
#appendix-c .pk {
|
||
color: #d97706;
|
||
font-weight: 700;
|
||
}
|
||
|
||
#appendix-c .fk {
|
||
color: var(--accent);
|
||
}
|
||
|
||
#appendix-c .field-note {
|
||
color: #9ca3af;
|
||
font-size: .63rem;
|
||
margin-left: .2rem;
|
||
}
|
||
|
||
#appendix-c .rel-summary {
|
||
margin-top: .8rem;
|
||
background: white;
|
||
border-radius: 10px;
|
||
padding: .8rem 1rem;
|
||
border: 1px solid var(--border);
|
||
font-size: .73rem;
|
||
color: var(--text);
|
||
}
|
||
|
||
/* role pyramid + permission matrix */
|
||
#appendix-c .role-and-matrix {
|
||
display: grid;
|
||
grid-template-columns: 1fr 1fr;
|
||
gap: 1rem;
|
||
}
|
||
|
||
@media (max-width: 720px) {
|
||
#appendix-c .role-and-matrix {
|
||
grid-template-columns: 1fr;
|
||
}
|
||
}
|
||
|
||
#appendix-c .role-pane {
|
||
background: white;
|
||
border-radius: 12px;
|
||
padding: 1rem;
|
||
box-shadow: 0 2px 6px rgba(0, 0, 0, .06);
|
||
border: 1px solid var(--border);
|
||
}
|
||
|
||
#appendix-c .pane-label {
|
||
font-size: .8rem;
|
||
font-weight: 700;
|
||
margin: 0 0 .8rem;
|
||
color: var(--heading);
|
||
}
|
||
|
||
#appendix-c .role-pyramid {
|
||
display: flex;
|
||
flex-direction: column;
|
||
align-items: center;
|
||
gap: .35rem;
|
||
padding: .25rem 0;
|
||
}
|
||
|
||
#appendix-c .role-row {
|
||
display: flex;
|
||
justify-content: center;
|
||
}
|
||
|
||
#appendix-c .role-chip {
|
||
border-radius: 8px;
|
||
padding: .45rem 1rem;
|
||
font-size: .75rem;
|
||
font-weight: 700;
|
||
color: white;
|
||
text-align: center;
|
||
min-width: 140px;
|
||
}
|
||
|
||
#appendix-c table.matrix {
|
||
width: 100%;
|
||
border-collapse: collapse;
|
||
font-size: .69rem;
|
||
}
|
||
|
||
#appendix-c table.matrix th,
|
||
#appendix-c table.matrix td {
|
||
border: 1px solid var(--border);
|
||
padding: .3rem .45rem;
|
||
text-align: center;
|
||
}
|
||
|
||
#appendix-c table.matrix th {
|
||
background: var(--surface-2);
|
||
font-weight: 700;
|
||
color: var(--text);
|
||
}
|
||
|
||
#appendix-c table.matrix td:first-child {
|
||
text-align: left;
|
||
font-weight: 600;
|
||
color: var(--text);
|
||
}
|
||
|
||
#appendix-c .allow {
|
||
background: #dcfce7;
|
||
color: #15803d;
|
||
font-weight: 700;
|
||
border-radius: 4px;
|
||
padding: .08rem .28rem;
|
||
font-size: .63rem;
|
||
display: inline-block;
|
||
}
|
||
|
||
#appendix-c .mine {
|
||
background: #fef3c7;
|
||
color: #b45309;
|
||
font-weight: 700;
|
||
border-radius: 4px;
|
||
padding: .08rem .28rem;
|
||
font-size: .63rem;
|
||
display: inline-block;
|
||
}
|
||
|
||
#appendix-c .deny {
|
||
background: #fee2e2;
|
||
color: #b91c1c;
|
||
font-weight: 700;
|
||
border-radius: 4px;
|
||
padding: .08rem .28rem;
|
||
font-size: .63rem;
|
||
display: inline-block;
|
||
}
|
||
|
||
/* info cards */
|
||
#appendix-c .info-cards {
|
||
display: grid;
|
||
grid-template-columns: repeat(auto-fill, minmax(220px, 1fr));
|
||
gap: .7rem;
|
||
}
|
||
|
||
#appendix-c .info-card {
|
||
background: white;
|
||
border-radius: 10px;
|
||
padding: .9rem 1rem;
|
||
box-shadow: 0 2px 6px rgba(0, 0, 0, .06);
|
||
border: 1px solid var(--border);
|
||
}
|
||
|
||
#appendix-c .info-card h3 {
|
||
font-size: .82rem;
|
||
font-weight: 700;
|
||
margin: 0 0 .45rem;
|
||
color: var(--heading);
|
||
border: none;
|
||
padding: 0;
|
||
}
|
||
|
||
#appendix-c .info-card ul {
|
||
list-style: none;
|
||
font-size: .72rem;
|
||
color: #4b5563;
|
||
padding: 0;
|
||
margin: 0;
|
||
}
|
||
|
||
#appendix-c .info-card ul li {
|
||
padding: .18rem 0;
|
||
border-bottom: 1px solid #f3f4f6;
|
||
}
|
||
|
||
#appendix-c .info-card ul li:last-child {
|
||
border-bottom: none;
|
||
}
|
||
|
||
#appendix-c .info-card ul li::before {
|
||
content: "→ ";
|
||
color: #9ca3af;
|
||
}
|
||
</style>
|
||
|
||
<style>
|
||
/* ─── ERD Embed ─── */
|
||
#erd-embed {
|
||
--erd-bg: #f5f7fb;
|
||
--erd-surface: #ffffff;
|
||
--erd-surface-hover: #f0f2f7;
|
||
--erd-border: #e2e4ed;
|
||
--erd-border-highlight: #b0b6d0;
|
||
--erd-text: #374151;
|
||
--erd-text-muted: #9ca3af;
|
||
--erd-text-bright: #111827;
|
||
--erd-pk: #b45309;
|
||
--erd-pk-bg: rgba(180, 83, 9, 0.08);
|
||
--erd-fk: #1d4ed8;
|
||
--erd-fk-bg: rgba(29, 78, 216, 0.08);
|
||
--erd-col: #4b5563;
|
||
--erd-type: #9ca3af;
|
||
--erd-line-color: #94a3b8;
|
||
--erd-line-highlight: #2563eb;
|
||
--erd-shadow: 0 2px 12px rgba(0, 0, 0, 0.08);
|
||
--erd-shadow-lg: 0 4px 24px rgba(0, 0, 0, 0.14);
|
||
--erd-radius: 10px;
|
||
position: relative;
|
||
height: 720px;
|
||
overflow: hidden;
|
||
background: var(--erd-bg);
|
||
color: var(--erd-text);
|
||
font-family: 'DM Sans', sans-serif;
|
||
border-radius: 12px;
|
||
margin-top: 16px;
|
||
}
|
||
|
||
#erd-embed #toolbar {
|
||
position: absolute;
|
||
top: 0;
|
||
left: 0;
|
||
right: 0;
|
||
height: 52px;
|
||
background: var(--erd-surface);
|
||
border-bottom: 1px solid var(--erd-border);
|
||
display: flex;
|
||
align-items: center;
|
||
padding: 0 20px;
|
||
z-index: 10;
|
||
gap: 16px;
|
||
border-radius: 0;
|
||
}
|
||
|
||
#erd-embed #toolbar h1 {
|
||
font-family: 'IBM Plex Mono', monospace;
|
||
font-size: 14px;
|
||
font-weight: 600;
|
||
color: var(--erd-text-bright);
|
||
letter-spacing: -0.02em;
|
||
margin: 0;
|
||
padding: 0;
|
||
border: none;
|
||
}
|
||
|
||
#erd-embed #toolbar .separator {
|
||
width: 1px;
|
||
height: 24px;
|
||
background: var(--erd-border);
|
||
}
|
||
|
||
#erd-embed #toolbar .stat {
|
||
font-family: 'IBM Plex Mono', monospace;
|
||
font-size: 11px;
|
||
color: var(--erd-text-muted);
|
||
margin: 0;
|
||
}
|
||
|
||
#erd-embed #toolbar .stat span {
|
||
color: var(--erd-text);
|
||
font-weight: 600;
|
||
}
|
||
|
||
#erd-embed .toolbar-actions {
|
||
margin-left: auto;
|
||
display: flex;
|
||
gap: 8px;
|
||
}
|
||
|
||
#erd-embed .toolbar-btn {
|
||
background: var(--erd-border);
|
||
border: none;
|
||
color: var(--erd-text);
|
||
font-family: 'DM Sans', sans-serif;
|
||
font-size: 12px;
|
||
padding: 6px 14px;
|
||
border-radius: 6px;
|
||
cursor: pointer;
|
||
transition: all 0.15s;
|
||
}
|
||
|
||
#erd-embed .toolbar-btn:hover {
|
||
background: var(--erd-border-highlight);
|
||
color: var(--erd-text-bright);
|
||
}
|
||
|
||
#erd-embed #canvas-container {
|
||
position: absolute;
|
||
top: 52px;
|
||
left: 0;
|
||
right: 0;
|
||
bottom: 0;
|
||
overflow: hidden;
|
||
cursor: grab;
|
||
}
|
||
|
||
#erd-embed #canvas-container:active {
|
||
cursor: grabbing;
|
||
}
|
||
|
||
#erd-embed #canvas {
|
||
position: absolute;
|
||
transform-origin: 0 0;
|
||
}
|
||
|
||
#erd-embed svg#lines {
|
||
position: absolute;
|
||
top: 0;
|
||
left: 0;
|
||
pointer-events: none;
|
||
overflow: visible;
|
||
}
|
||
|
||
#erd-embed .table-node {
|
||
position: absolute;
|
||
background: var(--erd-surface);
|
||
border: 1px solid var(--erd-border);
|
||
border-radius: var(--erd-radius);
|
||
min-width: 240px;
|
||
box-shadow: var(--erd-shadow);
|
||
cursor: move;
|
||
user-select: none;
|
||
transition: box-shadow 0.2s, border-color 0.2s;
|
||
}
|
||
|
||
#erd-embed .table-node:hover {
|
||
border-color: var(--erd-border-highlight);
|
||
box-shadow: var(--erd-shadow-lg);
|
||
z-index: 10;
|
||
}
|
||
|
||
#erd-embed .table-node.dragging {
|
||
z-index: 100;
|
||
border-color: var(--erd-fk);
|
||
box-shadow: 0 0 0 1px var(--erd-fk), var(--erd-shadow-lg);
|
||
}
|
||
|
||
#erd-embed .table-header {
|
||
padding: 10px 14px;
|
||
border-bottom: 1px solid var(--erd-border);
|
||
display: flex;
|
||
align-items: center;
|
||
gap: 8px;
|
||
}
|
||
|
||
#erd-embed .table-icon {
|
||
width: 20px;
|
||
height: 20px;
|
||
border-radius: 5px;
|
||
display: flex;
|
||
align-items: center;
|
||
justify-content: center;
|
||
font-size: 10px;
|
||
font-weight: 700;
|
||
flex-shrink: 0;
|
||
}
|
||
|
||
#erd-embed .table-header .table-name {
|
||
font-family: 'IBM Plex Mono', monospace;
|
||
font-size: 13px;
|
||
font-weight: 600;
|
||
color: var(--erd-text-bright);
|
||
letter-spacing: -0.01em;
|
||
}
|
||
|
||
#erd-embed .table-body {
|
||
padding: 4px 0;
|
||
}
|
||
|
||
#erd-embed .column-row {
|
||
display: flex;
|
||
align-items: center;
|
||
padding: 3px 14px;
|
||
gap: 8px;
|
||
font-family: 'IBM Plex Mono', monospace;
|
||
font-size: 11.5px;
|
||
line-height: 1.6;
|
||
transition: background 0.1s;
|
||
margin: 0;
|
||
}
|
||
|
||
#erd-embed .column-row:hover {
|
||
background: var(--erd-surface-hover);
|
||
}
|
||
|
||
#erd-embed .col-badge {
|
||
font-size: 8px;
|
||
font-weight: 700;
|
||
padding: 1px 4px;
|
||
border-radius: 3px;
|
||
letter-spacing: 0.05em;
|
||
min-width: 20px;
|
||
text-align: center;
|
||
flex-shrink: 0;
|
||
}
|
||
|
||
#erd-embed .col-badge.pk {
|
||
background: var(--erd-pk-bg);
|
||
color: var(--erd-pk);
|
||
}
|
||
|
||
#erd-embed .col-badge.fk {
|
||
background: var(--erd-fk-bg);
|
||
color: var(--erd-fk);
|
||
}
|
||
|
||
#erd-embed .col-badge.empty {
|
||
min-width: 20px;
|
||
}
|
||
|
||
#erd-embed .col-name {
|
||
color: var(--erd-col);
|
||
flex: 1;
|
||
}
|
||
|
||
#erd-embed .col-name.pk-name {
|
||
color: var(--erd-pk);
|
||
}
|
||
|
||
#erd-embed .col-name.fk-name {
|
||
color: var(--erd-fk);
|
||
}
|
||
|
||
#erd-embed .col-type {
|
||
color: var(--erd-type);
|
||
font-size: 10px;
|
||
text-align: right;
|
||
}
|
||
|
||
#erd-embed #legend {
|
||
position: absolute;
|
||
bottom: 16px;
|
||
left: 16px;
|
||
background: var(--erd-surface);
|
||
border: 1px solid var(--erd-border);
|
||
border-radius: var(--erd-radius);
|
||
padding: 12px 16px;
|
||
z-index: 10;
|
||
font-size: 11px;
|
||
display: flex;
|
||
gap: 16px;
|
||
}
|
||
|
||
#erd-embed .legend-item {
|
||
display: flex;
|
||
align-items: center;
|
||
gap: 6px;
|
||
color: var(--erd-text-muted);
|
||
margin: 0;
|
||
}
|
||
|
||
#erd-embed .legend-dot {
|
||
width: 8px;
|
||
height: 8px;
|
||
border-radius: 2px;
|
||
}
|
||
|
||
#erd-embed #zoom-controls {
|
||
position: absolute;
|
||
bottom: 16px;
|
||
right: 16px;
|
||
display: flex;
|
||
gap: 4px;
|
||
z-index: 10;
|
||
}
|
||
|
||
#erd-embed #zoom-controls button {
|
||
width: 36px;
|
||
height: 36px;
|
||
background: var(--erd-surface);
|
||
border: 1px solid var(--erd-border);
|
||
border-radius: 8px;
|
||
color: var(--erd-text);
|
||
font-size: 16px;
|
||
cursor: pointer;
|
||
display: flex;
|
||
align-items: center;
|
||
justify-content: center;
|
||
transition: all 0.15s;
|
||
}
|
||
|
||
#erd-embed #zoom-controls button:hover {
|
||
background: var(--erd-surface-hover);
|
||
border-color: var(--erd-border-highlight);
|
||
}
|
||
|
||
#erd-embed #minimap {
|
||
position: absolute;
|
||
bottom: 60px;
|
||
right: 16px;
|
||
width: 180px;
|
||
height: 120px;
|
||
background: var(--erd-surface);
|
||
border: 1px solid var(--erd-border);
|
||
border-radius: 8px;
|
||
z-index: 10;
|
||
overflow: hidden;
|
||
}
|
||
|
||
#erd-embed #minimap canvas {
|
||
width: 100%;
|
||
height: 100%;
|
||
}
|
||
|
||
#erd-embed .rel-line {
|
||
fill: none;
|
||
stroke: var(--erd-line-color);
|
||
stroke-width: 1.5;
|
||
opacity: 0.5;
|
||
transition: opacity 0.2s, stroke 0.2s;
|
||
}
|
||
|
||
#erd-embed .rel-line.highlighted {
|
||
stroke: var(--erd-line-highlight);
|
||
opacity: 1;
|
||
stroke-width: 2;
|
||
}
|
||
|
||
#erd-embed .rel-marker {
|
||
fill: var(--erd-line-color);
|
||
transition: fill 0.2s;
|
||
}
|
||
|
||
#erd-embed .rel-marker.highlighted {
|
||
fill: var(--erd-line-highlight);
|
||
}
|
||
|
||
#erd-embed .rel-label {
|
||
font-family: 'IBM Plex Mono', monospace;
|
||
font-size: 9px;
|
||
fill: var(--erd-text-muted);
|
||
pointer-events: none;
|
||
}
|
||
</style>
|
||
</head>
|
||
|
||
<body>
|
||
<div class="page">
|
||
<aside class="sidebar">
|
||
<div class="brand">WIN Developer Docs</div>
|
||
<a href="#desc">1. Project Description</a>
|
||
<a href="#arch">2. Architecture</a>
|
||
<a href="#flow">3. Data Flow</a>
|
||
<a href="#hosting">4. Recommended Hosting</a>
|
||
<a href="#install">5. Application Installation</a>
|
||
<a href="#auth">6. Authentication & Authorization</a>
|
||
<a href="#backup">7. Database Backup</a>
|
||
<a href="#env">8. Environment Variables</a>
|
||
<a href="#partner">9. Partner Statement</a>
|
||
<a href="#walk">10. Installation Walkthrough</a>
|
||
<a href="#analysis">11. Performance & UX Analysis</a>
|
||
<a href="#improve">12. Known Liabilities & Improvements</a>
|
||
<a href="#sustain">13. Sustainability Considerations</a>
|
||
<a href="#appendix-a">Appendix A – ERD</a>
|
||
<a href="#appendix-b">Appendix B – Repository Structure</a>
|
||
<a href="#appendix-c">Appendix C – Architecture Overview</a>
|
||
</aside>
|
||
|
||
<main class="content">
|
||
<div class="hero">
|
||
<div class="meta">Developer Documentation</div>
|
||
<h1>WIN Student Goal Tracker</h1>
|
||
<p>April 2026</p>
|
||
</div>
|
||
|
||
<section id="desc">
|
||
<h2>1. Project Description</h2>
|
||
<p>
|
||
The WIN Student Goal Tracker is a web-based case management system designed to support
|
||
organizations working with adults with special needs. The platform enables staff to track
|
||
student goals, document services, and log critical incidents while maintaining strong privacy,
|
||
auditability, and compliance with FERPA, IDEA, and FAPE.
|
||
</p>
|
||
<p>
|
||
The system is designed with sustainability in mind, ensuring that future developers can
|
||
easily maintain, extend, and redeploy the application.
|
||
</p>
|
||
</section>
|
||
|
||
<section id="arch">
|
||
<h2>2. Architecture</h2>
|
||
|
||
<h3>Technology Stack & Infrastructure</h3>
|
||
<div class="stack">
|
||
<div class="card">
|
||
<strong>Frontend</strong>
|
||
Angular 20.1.5
|
||
</div>
|
||
<div class="card">
|
||
<strong>Backend</strong>
|
||
.NET 9.0 (C#) with Dapper ORM
|
||
</div>
|
||
<div class="card">
|
||
<strong>Authentication</strong>
|
||
JWT + Refresh Tokens
|
||
</div>
|
||
<div class="card">
|
||
<strong>Database</strong>
|
||
MySQL
|
||
</div>
|
||
<div class="card">
|
||
<strong>Infrastructure</strong>
|
||
Docker + VPS + Traefik
|
||
</div>
|
||
</div>
|
||
|
||
<h3>Architecture Description</h3>
|
||
<ol>
|
||
<li>Presentation Layer (Angular)</li>
|
||
<li>Application Layer (.NET API)</li>
|
||
<li>Data Layer (MySQL)</li>
|
||
</ol>
|
||
<p>Traefik manages routing and HTTPS traffic.</p>
|
||
</section>
|
||
|
||
<section id="flow">
|
||
<h2>3. Data Flow</h2>
|
||
<p>Each scenario lists the UI file that initiates the action, the Angular service method called, the API
|
||
endpoint hit, the .NET controller and repository method invoked, and the stored procedure that reads or writes
|
||
the database.</p>
|
||
|
||
<h3>1 — User Login</h3>
|
||
<div class="flow">login.html (submit) → Auth.login() [auth.ts] → POST /api/Auth/Login → AuthController.Login() →
|
||
UserRepository.GetByEmailAsync() + GetProgramsForUserIdAsync() → sp_User_GetByEmail,
|
||
sp_UserPrograms_GetByUserId → session token + program list → login.html renders program selector</div>
|
||
|
||
<h3>2 — View My Students (Home Dashboard)</h3>
|
||
<div class="flow">home.ts (constructor) → StudentService.getMyStudents() [student.service.ts] → GET
|
||
/api/Student/my → StudentController.GetMyStudents() → StudentRepository.GetMyStudentsAsync() →
|
||
sp_Student_GetWithAssignments → student card list → home.html renders student cards</div>
|
||
|
||
<h3>3 — Open Student Workspace (Full Profile)</h3>
|
||
<div class="flow">home.html (card click → router) → workspace.ts (route param change) →
|
||
StudentService.getFullProfile() [student.service.ts] → GET /api/Student/{id}/full →
|
||
StudentController.GetFullProfile() → StudentRepository.GetFullProfileAsync() → sp_Student_GetFullProfile →
|
||
goals + benchmarks + progress events → workspace.html renders detail view</div>
|
||
|
||
<h3>4 — Create Goal</h3>
|
||
<div class="flow">workspace.html (Add Goal button) → goal-modal.ts (onSubmit) → StudentService.createGoal()
|
||
[student.service.ts] → POST /api/Student/{id}/goals → StudentController.CreateGoal() →
|
||
PermissionService.IsAllowed() → StudentRepository.InsertGoalAsync() → sp_Goal_Insert → created goal returned →
|
||
goal-modal closes, workspace.html refreshes</div>
|
||
|
||
<h3>5 — Log Progress Event</h3>
|
||
<div class="flow">workspace.html (Add Event) → edit-event-modal.ts (onSave) → StudentService.addProgressEvent()
|
||
[student.service.ts] → POST /api/Student/{id}/progress-event → StudentController.AddProgressEvent() →
|
||
StudentRepository.SaveProgressEventAsync() → sp_ProgressEvent_Save → new event ID returned → workspace.html
|
||
progress list refreshes</div>
|
||
</section>
|
||
|
||
<section id="hosting">
|
||
<h2>4. Recommended Hosting</h2>
|
||
<ul>
|
||
<li>The entire application is currently hosted online with Hetzner (www.hetzner.com), on a plan donated by team members. The team has committed to hosting for at least the next two years, and if/when that changes, will help the partner transition to the hosting platform of their choice. The team recommends staying with Hetzner based on their reliability and low cost.</li>
|
||
</ul>
|
||
</section>
|
||
|
||
<section id="install">
|
||
<h2>5. Application Installation</h2>
|
||
<p>
|
||
The application is composed of three moving parts — an Angular 20 SPA (<code>ui/winstudentgoaltracker</code>),
|
||
a .NET 9 Web API (<code>api/</code>), and a MySQL 8 database initialised from the SQL objects in
|
||
<code>db/Objects/</code>. Configuration for every tier is read from a single <code>.env</code> file at the
|
||
repository root. Two workflows are supported: running the stack directly on a developer workstation, or
|
||
deploying the full stack to a server with <code>docker compose</code> behind an existing Traefik reverse
|
||
proxy.
|
||
</p>
|
||
|
||
<h3>5.1 Local Development Environment</h3>
|
||
<p>
|
||
The recommended local workflow runs MySQL in Docker (so the schema initialises itself from
|
||
<code>db/docker-init/01-schema.sh</code>) while the API and UI run on the host for fast iteration and
|
||
debugging.
|
||
</p>
|
||
|
||
<h4>Prerequisites</h4>
|
||
<ul>
|
||
<li><strong>Node.js 22+</strong> and <strong>npm</strong> — to run the Angular CLI</li>
|
||
<li><strong>.NET 9 SDK</strong> — to build and run the API</li>
|
||
<li><strong>Docker Desktop</strong> (or Docker Engine + Compose plugin) — to run MySQL</li>
|
||
<li><strong>Git</strong> — to clone the repository</li>
|
||
</ul>
|
||
|
||
<h4>Step 1 — Clone and create <code>.env</code></h4>
|
||
<p>
|
||
Clone the repository and create a <code>.env</code> file at the project root. The API reads it via
|
||
<code>DotNetEnv</code> (which traverses parent directories), the UI/API containers read it through
|
||
<code>docker-compose.yml</code>'s <code>env_file</code> directive, and the MySQL container consumes the
|
||
<code>MYSQL_*</code> variables natively.
|
||
</p>
|
||
<pre><code>git clone <repo-url> WinStudentGoalTracker
|
||
cd WinStudentGoalTracker</code></pre>
|
||
<p>Create <code>.env</code> with the following keys (replace the secrets before sharing or deploying):</p>
|
||
<pre><code># MySQL container bootstrap
|
||
MYSQL_ROOT_PASSWORD=change_me_root
|
||
MYSQL_DATABASE=winstudentgoaltracker
|
||
MYSQL_USER=appuser
|
||
MYSQL_PASSWORD=change_me_app
|
||
|
||
# Connection used by the .NET API
|
||
MYSQL_HOST=127.0.0.1
|
||
MYSQL_PORT=3309
|
||
# (the api falls back to root/no-password if MYSQL_USER/PASSWORD are omitted)
|
||
|
||
# JWT signing key — must be >= 32 bytes
|
||
JWT_KEY=replace_with_a_long_random_string_at_least_32_chars</code></pre>
|
||
<p>
|
||
<code>.env</code> is listed in <code>.gitignore</code>; never commit it. For local dev, point
|
||
<code>MYSQL_HOST</code> at <code>127.0.0.1</code> so the host-side API can reach the containerised MySQL
|
||
through the published port <code>3309</code>.
|
||
</p>
|
||
|
||
<h4>Step 2 — Start MySQL</h4>
|
||
<p>
|
||
Bring up only the database service from the root compose file. On first run it creates the volume, creates
|
||
the database and application user, and executes <code>db/docker-init/01-schema.sh</code> which loads tables,
|
||
functions, views, and stored procedures from <code>db/Objects/</code> in the correct order.
|
||
</p>
|
||
<pre><code>docker compose up -d mysql
|
||
docker compose logs -f mysql # watch for "Schema initialization complete"</code></pre>
|
||
<p>
|
||
MySQL is exposed on host port <code>3309</code> (mapped to container port <code>3306</code>). To reset the
|
||
database during development, stop the container and remove the named volume:
|
||
<code>docker compose down && docker volume rm winstudentgoaltracker_win_mysql_data</code>.
|
||
</p>
|
||
|
||
<h4>Step 3 — Run the API</h4>
|
||
<pre><code>cd api
|
||
dotnet restore
|
||
dotnet run</code></pre>
|
||
<p>
|
||
The API listens on <code>http://localhost:5000</code> (and <code>https://localhost:5001</code>) by default
|
||
via Kestrel. Swagger UI is available at <code>/swagger</code> when running in the
|
||
<code>Development</code> environment (the default for <code>dotnet run</code>). On startup the console
|
||
prints the resolved MySQL connection string — verify it points at <code>127.0.0.1:3309</code> and the
|
||
database name from your <code>.env</code>.
|
||
</p>
|
||
|
||
<h4>Step 4 — Run the UI</h4>
|
||
<pre><code>cd ui/winstudentgoaltracker
|
||
npm install
|
||
npm start</code></pre>
|
||
<p>
|
||
The Angular dev server starts on <code>http://localhost:4200</code>. The base URL the UI calls is defined in
|
||
<code>src/environments/environment.development.ts</code>. For a fully local stack, edit that file so
|
||
<code>apiBaseUrl</code> points at your locally running API (e.g. <code>http://localhost:5000</code>) instead
|
||
of the deployed <code>https://winapi.opelly.me</code>. The API's CORS policy already allows any origin in
|
||
development.
|
||
</p>
|
||
|
||
<h4>Step 5 — Seed a user</h4>
|
||
<p>
|
||
The schema initialises empty. To sign in, insert at least one <code>user</code>, one
|
||
<code>school_district</code>, one <code>program</code>, and a <code>user_program</code> row linking the user
|
||
to the program with a role. Passwords are hashed with PBKDF2 by <code>PasswordHasher</code>; the simplest
|
||
path is to use an existing seeded dump (ask a team member for <code>seed.sql</code>) or register via a
|
||
<code>super_admin</code> account using the admin endpoints.
|
||
</p>
|
||
|
||
<h3>5.2 Production Deployment (Docker Compose + Traefik)</h3>
|
||
<p>
|
||
Production deploys all four tiers — MySQL, .NET API, Nginx-served Angular build, and the Traefik reverse
|
||
proxy — as Docker containers on a single VPS. The repository's <code>docker-compose.yml</code> brings up
|
||
MySQL, the API, and the UI; <strong>Traefik runs in a separate compose stack</strong> on the same host and
|
||
publishes ports 80/443 for the whole server. The two stacks communicate through an external Docker network
|
||
named <code>web</code>.
|
||
</p>
|
||
|
||
<h4>Prerequisites on the server</h4>
|
||
<ul>
|
||
<li>Linux VPS with <strong>Docker Engine</strong> and the <strong>Compose plugin</strong> installed</li>
|
||
<li>Ports <strong>80</strong> and <strong>443</strong> open on the server's firewall</li>
|
||
<li>Two DNS <code>A</code> records pointing at the server's public IP:
|
||
<code>win.<your-domain></code> (UI) and <code>winapi.<your-domain></code> (API)</li>
|
||
<li>A working Traefik stack on the host (see next section)</li>
|
||
</ul>
|
||
|
||
<h4>Setting up Traefik (one-time)</h4>
|
||
<p>
|
||
Traefik is the edge router that terminates TLS, requests Let's Encrypt certificates, and forwards traffic to
|
||
the correct container based on the <code>Host()</code> rule. The WIN compose file assumes Traefik is already
|
||
running on the host and configured with:
|
||
</p>
|
||
<ul>
|
||
<li>An <strong>external Docker network named <code>web</code></strong> that Traefik is attached to. The
|
||
application's UI and API containers join this network so Traefik can reach them.
|
||
<pre><code>docker network create web</code></pre>
|
||
</li>
|
||
<li>An entrypoint named <strong><code>websecure</code></strong> listening on <code>:443</code> (and typically
|
||
a redirect from <code>:80</code>).</li>
|
||
<li>A certificate resolver named <strong><code>letsencrypt</code></strong> (ACME, HTTP or DNS challenge)
|
||
configured with an email and a persistent <code>acme.json</code> volume.</li>
|
||
<li>Two file-provider middlewares referenced by the labels in <code>docker-compose.yml</code>:
|
||
<strong><code>gzip@file</code></strong> (compression) and
|
||
<strong><code>security-headers@file</code></strong> (HSTS, frame-deny, etc.).</li>
|
||
</ul>
|
||
<p>
|
||
A minimal Traefik <code>docker-compose.yml</code> typically lives in <code>/opt/traefik/</code> alongside a
|
||
<code>traefik.yml</code> static config (declaring the entrypoints, providers, and ACME resolver) and a
|
||
<code>dynamic/</code> directory containing the <code>gzip</code> and <code>security-headers</code>
|
||
middleware definitions. Traefik's official documentation at
|
||
<a href="https://doc.traefik.io/traefik/">doc.traefik.io</a> covers this setup in detail — the WIN stack
|
||
does not prescribe a specific Traefik configuration, it only requires that the network, entrypoint, cert
|
||
resolver, and middleware names above exist.
|
||
</p>
|
||
|
||
<h4>Step 1 — Prepare the repository on the server</h4>
|
||
<pre><code>git clone <repo-url> /opt/winstudentgoaltracker
|
||
cd /opt/winstudentgoaltracker</code></pre>
|
||
|
||
<h4>Step 2 — Populate <code>.env</code></h4>
|
||
<p>Same keys as the local workflow, but with production-safe values. For the containerised deployment,
|
||
<code>MYSQL_HOST</code> must be the service name <code>mysql</code> (the API container resolves it over the
|
||
internal <code>backend</code> Docker network) and <code>MYSQL_PORT</code> must be <code>3306</code> (the
|
||
container's internal port, not the host-mapped <code>3309</code>):</p>
|
||
<pre><code>MYSQL_ROOT_PASSWORD=<strong-random>
|
||
MYSQL_DATABASE=winstudentgoaltracker
|
||
MYSQL_USER=appuser
|
||
MYSQL_PASSWORD=<strong-random>
|
||
MYSQL_HOST=mysql
|
||
MYSQL_PORT=3306
|
||
JWT_KEY=<32+ byte random secret></code></pre>
|
||
|
||
<h4>Step 3 — Update domain labels (if not using <code>opelly.me</code>)</h4>
|
||
<p>
|
||
<code>docker-compose.yml</code> hard-codes the Traefik <code>Host()</code> rules
|
||
<code>winapi.opelly.me</code> and <code>win.opelly.me</code>. Edit those two labels to match your DNS
|
||
records before bringing the stack up. The UI also points at <code>https://winapi.opelly.me</code> via
|
||
<code>src/environments/environment.ts</code>; update that file and rebuild the UI image so the browser calls
|
||
your API host.
|
||
</p>
|
||
|
||
<h4>Step 4 — Build and start the stack</h4>
|
||
<pre><code>docker compose up -d --build</code></pre>
|
||
<p>This creates:</p>
|
||
<ul>
|
||
<li><code>winstudent-mysql</code> — MySQL 8 on the internal <code>backend</code> network, volume
|
||
<code>win_mysql_data</code>, schema loaded on first start.</li>
|
||
<li><code>winstudent-api</code> — .NET 9 API on port <code>8005</code>, attached to both
|
||
<code>backend</code> (to reach MySQL) and <code>web</code> (to be reached by Traefik). Published at
|
||
<code>winapi.<your-domain></code> over HTTPS.</li>
|
||
<li><code>winstudent-ui</code> — Angular build served by Nginx on port <code>8006</code>, attached to
|
||
<code>web</code>. Published at <code>win.<your-domain></code> over HTTPS.</li>
|
||
</ul>
|
||
<p>
|
||
Traefik picks up the new containers automatically via its Docker provider, requests TLS certificates from
|
||
Let's Encrypt on first request, and begins routing traffic. Confirm with
|
||
<code>docker compose ps</code> (all services <code>healthy</code>/<code>running</code>) and
|
||
<code>docker compose logs -f api</code> (look for the Kestrel startup line and the printed connection
|
||
string).
|
||
</p>
|
||
|
||
<h4>Step 5 — Verify</h4>
|
||
<ul>
|
||
<li>Visit <code>https://win.<your-domain></code> — the login page should load over HTTPS with a valid
|
||
Let's Encrypt certificate.</li>
|
||
<li>Visit <code>https://winapi.<your-domain>/swagger</code> — should return 404 in production (Swagger
|
||
is only enabled in <code>Development</code>); a 404 from the API itself confirms routing works.</li>
|
||
<li>Sign in with a seeded user and check <code>docker compose logs api</code> for successful
|
||
<code>/api/Auth/Login</code> requests.</li>
|
||
</ul>
|
||
|
||
<h4>Updating a running deployment</h4>
|
||
<pre><code>cd /opt/winstudentgoaltracker
|
||
git pull
|
||
docker compose up -d --build # rebuilds changed images and recreates containers
|
||
docker compose logs -f</code></pre>
|
||
<p>
|
||
MySQL is <strong>not</strong> rebuilt by <code>--build</code> (it uses the upstream image) and its volume
|
||
persists across <code>up</code>/<code>down</code> cycles, so data survives code deployments. Schema
|
||
migrations should be applied by running the appropriate SQL files from <code>db/migrations/</code> against
|
||
the running MySQL container; see section 7 for backup procedures before running migrations.
|
||
</p>
|
||
</section>
|
||
|
||
<section id="auth">
|
||
<h2>6. Authentication & Authorization</h2>
|
||
<p>JWT-based authentication with refresh tokens.</p>
|
||
<p><strong>Key files:</strong> AuthController.cs, JwtService.cs, middleware</p>
|
||
</section>
|
||
|
||
<section id="backup">
|
||
<h2>7. Database Backup</h2>
|
||
|
||
<h3>Backup</h3>
|
||
<pre><code>mysqldump -u username -p dbname > backup.sql</code></pre>
|
||
|
||
<h3>Restore</h3>
|
||
<pre><code>mysql -u username -p dbname < backup.sql</code></pre>
|
||
|
||
<h3>From the UI</h3>
|
||
<p>Administrators may back up the database via the "Backup" button in the Administrator panel</p>
|
||
</section>
|
||
|
||
<section id="env">
|
||
<h2>8. Environment Variables</h2>
|
||
<pre><code>DB_CONNECTION=...
|
||
JWT_SECRET=...
|
||
JWT_EXPIRATION=3600</code></pre>
|
||
<p>Ensure <code>.env</code> is in <code>.gitignore</code>.</p>
|
||
</section>
|
||
|
||
<section id="partner">
|
||
<h2>9. Partner Statement</h2>
|
||
<p>The partner representatives Polly Balsillie and Fred Winter have reviewed the developer documentation and
|
||
understands its scope and purpose.</p>
|
||
<p><strong>Date:</strong> April 16, 2026</p>
|
||
</section>
|
||
|
||
<section id="walk">
|
||
<h2>10. Installation Walkthrough Statement</h2>
|
||
<p>N/A. An installation walkthrough was not appropriate for the partner, as they are non-technical. They are
|
||
primarily users of the application, and at least two of our team members will continue to make ourselves
|
||
available to support the application.</p>
|
||
<p><strong>Date:</strong> April 16, 2026</p>
|
||
</section>
|
||
|
||
<section id="analysis">
|
||
<h2>11. Performance & UX Analysis</h2>
|
||
|
||
<h3>Lighthouse Results</h3>
|
||
|
||
<div style="display: flex; gap: 1.5rem; flex-wrap: wrap; margin: 1.5rem 0;">
|
||
<figure style="margin: 0; text-align: center;">
|
||
<img src="lighthouse_desktop.png" alt="Lighthouse Desktop Results"
|
||
style="max-width: 100%; border: 1px solid #ddd; border-radius: 6px;" />
|
||
<figcaption style="margin-top: 0.5rem; font-size: 0.85rem; color: #666;">Desktop</figcaption>
|
||
</figure>
|
||
<figure style="margin: 0; text-align: center;">
|
||
<img src="lighthouse_mobile.png" alt="Lighthouse Mobile Results"
|
||
style="max-width: 100%; border: 1px solid #ddd; border-radius: 6px;" />
|
||
<figcaption style="margin-top: 0.5rem; font-size: 0.85rem; color: #666;">Mobile</figcaption>
|
||
</figure>
|
||
</div>
|
||
|
||
<h3>Form Factor Analysis</h3>
|
||
<ul>
|
||
<li>Mobile Portrait: Fully responsive and functional - minimal UI and streamlined functionality for robust field use.</li>
|
||
<li>Mobile Landscape: Improved readability and layout - same features and user story as mobile portrait</li>
|
||
<li>Desktop: Full-featured user experience, with application configuration, administrator and edit/delete functionality</li>
|
||
</ul>
|
||
|
||
<h3>UX Observations</h3>
|
||
<p>Clean navigation with consistent user workflows across devices.</p>
|
||
|
||
<span class="pill">Responsive</span>
|
||
<span class="pill">Accessible</span>
|
||
<span class="pill">High Performance</span>
|
||
<span class="pill">SEO Ready</span>
|
||
</section>
|
||
|
||
<section id="improve">
|
||
<h2>12. Known Liabilities & Improvements</h2>
|
||
<p><strong>Issues:</strong> Potential performance scaling and mobile optimization opportunities</p>
|
||
<p><strong>Improvements:</strong> Lazy loading, bundle optimization, responsive enhancements</p>
|
||
</section>
|
||
|
||
<section id="sustain">
|
||
<h2>13. Sustainability Considerations</h2>
|
||
<p>Docker deployment, very inexpensive hosting, and modular design support long-term maintainability. Addcitionally, the use of free, off-the-shelf technology choices (Angular, C#, MySQL) contribute a sustainable project tech stack. </p>
|
||
</section>
|
||
|
||
<section id="appendix-a">
|
||
<h2>Appendix A – ERD</h2>
|
||
<p>
|
||
The system includes a structured relational database supporting users, roles, permissions,
|
||
programs, students, goals, progress tracking, and incident logging. The interactive diagram
|
||
below shows all 17 tables and their relationships. Drag tables to rearrange, scroll to zoom,
|
||
and hover a table to highlight its connections.
|
||
</p>
|
||
|
||
<a href="erd.html" class="arch-link">
|
||
<svg width="14" height="14" viewBox="0 0 24 24" fill="none" stroke="currentColor" stroke-width="2.5">
|
||
<path d="M18 13v6a2 2 0 0 1-2 2H5a2 2 0 0 1-2-2V8a2 2 0 0 1 2-2h6" />
|
||
<polyline points="15 3 21 3 21 9" />
|
||
<line x1="10" y1="14" x2="21" y2="3" />
|
||
</svg>
|
||
Open full-page ERD
|
||
</a>
|
||
|
||
<div id="erd-embed">
|
||
<div id="toolbar">
|
||
<h1>⬡ ERD</h1>
|
||
<div class="separator"></div>
|
||
<div class="stat">Tables <span id="table-count">17</span></div>
|
||
<div class="separator"></div>
|
||
<div class="stat">Relations <span id="rel-count">0</span></div>
|
||
<div class="toolbar-actions">
|
||
<button class="toolbar-btn" onclick="resetView()">Reset View</button>
|
||
<button class="toolbar-btn" onclick="toggleLabels()">Toggle Labels</button>
|
||
</div>
|
||
</div>
|
||
|
||
<div id="canvas-container">
|
||
<div id="canvas">
|
||
<svg id="lines" width="6000" height="4000"></svg>
|
||
</div>
|
||
</div>
|
||
|
||
<div id="legend">
|
||
<div class="legend-item">
|
||
<div class="legend-dot" style="background:#b45309"></div> Primary Key
|
||
</div>
|
||
<div class="legend-item">
|
||
<div class="legend-dot" style="background:#1d4ed8"></div> Foreign Key
|
||
</div>
|
||
<div class="legend-item">
|
||
<svg width="30" height="10">
|
||
<line x1="0" y1="5" x2="30" y2="5" stroke="#94a3b8" stroke-width="1.5" />
|
||
</svg>
|
||
Relationship
|
||
</div>
|
||
</div>
|
||
|
||
<div id="zoom-controls">
|
||
<button onclick="zoomIn()">+</button>
|
||
<button onclick="zoomOut()">−</button>
|
||
<button onclick="resetView()" style="font-size:12px">⌂</button>
|
||
</div>
|
||
|
||
<div id="minimap"><canvas id="minimap-canvas"></canvas></div>
|
||
</div>
|
||
</section>
|
||
|
||
<section id="appendix-b">
|
||
<h2>Appendix B – Repository Structure</h2>
|
||
<pre><code>WinStudentGoalTracker/
|
||
├── api/ .NET 9 backend — controllers, services, repositories, Dockerfile
|
||
├── db/ MySQL schema and stored procedures; docker-init scripts for container startup
|
||
├── docs/ HTML documentation (technical spec, user manual, ERD, architecture, API)
|
||
├── ui/ Angular 20 frontend SPA
|
||
├── prototype/ Early role-assignment prototypes (not production code)
|
||
├── docker-compose.yml Orchestrates API, Angular/Nginx, MySQL, and Traefik containers
|
||
└── WinStudentGoalTracker.sln Visual Studio solution file</code></pre>
|
||
</section>
|
||
|
||
<section id="appendix-c">
|
||
<h2>Appendix C – Architecture Overview</h2>
|
||
<p>High-level diagrams of the system tiers, two-phase auth flow, data model, role hierarchy, deployment, and key
|
||
design decisions.</p>
|
||
|
||
<a href="architecture.html" class="arch-link">
|
||
<svg width="14" height="14" viewBox="0 0 24 24" fill="none" stroke="currentColor" stroke-width="2.5">
|
||
<path d="M18 13v6a2 2 0 0 1-2 2H5a2 2 0 0 1-2-2V8a2 2 0 0 1 2-2h6" />
|
||
<polyline points="15 3 21 3 21 9" />
|
||
<line x1="10" y1="14" x2="21" y2="3" />
|
||
</svg>
|
||
Open full-page Architecture Overview
|
||
</a>
|
||
|
||
<!-- ── 1. System Architecture ── -->
|
||
<div class="asec-title"><span>🏗</span> System Architecture</div>
|
||
|
||
<div class="arch-diagram">
|
||
|
||
<div class="tier tier--client">
|
||
<div class="tier-label">Client</div>
|
||
<div class="tier-body">
|
||
<div class="box box--blue">
|
||
<div class="box-title">Angular SPA <span class="badge badge--blue">v20</span></div>
|
||
<ul>
|
||
<li>TypeScript 5.8 + SCSS</li>
|
||
<li>Angular Signals (state)</li>
|
||
<li>Lazy-loaded routes</li>
|
||
<li>JWT auth interceptor</li>
|
||
<li>Proactive token refresh</li>
|
||
</ul>
|
||
</div>
|
||
<div class="box box--blue">
|
||
<div class="box-title">Desktop Layout</div>
|
||
<ul>
|
||
<li>Home / Workspace</li>
|
||
<li>Student detail view</li>
|
||
<li>Goal & Benchmark modals</li>
|
||
<li>Progress event modals</li>
|
||
<li>Report generation UI</li>
|
||
<li>Admin panel</li>
|
||
</ul>
|
||
</div>
|
||
<div class="box box--blue">
|
||
<div class="box-title">Mobile Layout</div>
|
||
<ul>
|
||
<li>Student card list</li>
|
||
<li>Add progress event</li>
|
||
<li>Toggle benchmark</li>
|
||
</ul>
|
||
</div>
|
||
<div class="box box--blue">
|
||
<div class="box-title">Shared Services</div>
|
||
<ul>
|
||
<li>AuthService (signals)</li>
|
||
<li>ApiService (HTTP)</li>
|
||
<li>StudentService</li>
|
||
<li>AdminService</li>
|
||
<li>ReportPromptService</li>
|
||
<li>PlatformService</li>
|
||
</ul>
|
||
</div>
|
||
</div>
|
||
</div>
|
||
|
||
<div class="tier-arrow">
|
||
<svg width="16" height="16" viewBox="0 0 24 24" fill="none" stroke="currentColor" stroke-width="2">
|
||
<path d="M12 5v14M5 12l7 7 7-7" />
|
||
</svg>
|
||
<span>HTTPS / REST + JWT Bearer — win.opelly.me → winapi.opelly.me</span>
|
||
</div>
|
||
|
||
<div class="tier tier--proxy">
|
||
<div class="tier-label">Proxy</div>
|
||
<div class="tier-body">
|
||
<div class="box box--slate">
|
||
<div class="box-title">Traefik Reverse Proxy</div>
|
||
<ul>
|
||
<li>win.opelly.me → Angular (Nginx :8006)</li>
|
||
<li>winapi.opelly.me → API (:8005)</li>
|
||
<li>Let's Encrypt TLS</li>
|
||
<li>Gzip compression</li>
|
||
<li>Security headers</li>
|
||
</ul>
|
||
</div>
|
||
</div>
|
||
</div>
|
||
|
||
<div class="tier-arrow">
|
||
<svg width="16" height="16" viewBox="0 0 24 24" fill="none" stroke="currentColor" stroke-width="2">
|
||
<path d="M12 5v14M5 12l7 7 7-7" />
|
||
</svg>
|
||
<span>HTTP (internal Docker network)</span>
|
||
</div>
|
||
|
||
<div class="tier tier--api">
|
||
<div class="tier-label">API</div>
|
||
<div class="tier-body">
|
||
<div class="box box--green">
|
||
<div class="box-title">ASP.NET Core 9 <span class="badge badge--green">C#</span></div>
|
||
<ul>
|
||
<li>JWT authentication middleware</li>
|
||
<li>Swagger / OpenAPI</li>
|
||
<li>CORS policy</li>
|
||
<li>Dependency injection</li>
|
||
</ul>
|
||
</div>
|
||
<div class="box box--green">
|
||
<div class="box-title">Controllers</div>
|
||
<ul>
|
||
<li>/api/Auth — login & tokens</li>
|
||
<li>/api/Student — CRUD</li>
|
||
<li>/api/Admin — district/users</li>
|
||
<li>/api/ReportPrompt — prompts</li>
|
||
</ul>
|
||
</div>
|
||
<div class="box box--green">
|
||
<div class="box-title">Services</div>
|
||
<ul>
|
||
<li>TokenService (JWT)</li>
|
||
<li>PermissionService</li>
|
||
<li>PasswordHasher (PBKDF2)</li>
|
||
<li>RecommendationService</li>
|
||
<li>TranscriptionService</li>
|
||
<li>ProgressReportBuilder</li>
|
||
</ul>
|
||
</div>
|
||
<div class="box box--green">
|
||
<div class="box-title">Repositories <span class="badge badge--green">Dapper</span></div>
|
||
<ul>
|
||
<li>StudentRepository</li>
|
||
<li>UserRepository</li>
|
||
<li>AuthRepository</li>
|
||
<li>AdminRepository</li>
|
||
<li>ReportPromptRepository</li>
|
||
</ul>
|
||
</div>
|
||
</div>
|
||
</div>
|
||
|
||
<div class="tier-arrow">
|
||
<svg width="16" height="16" viewBox="0 0 24 24" fill="none" stroke="currentColor" stroke-width="2">
|
||
<path d="M12 5v14M5 12l7 7 7-7" />
|
||
</svg>
|
||
<span>Stored procedures via Dapper — TCP :3309 (Docker internal)</span>
|
||
</div>
|
||
|
||
<div class="tier tier--data">
|
||
<div class="tier-label">Data</div>
|
||
<div class="tier-body">
|
||
<div class="box box--amber">
|
||
<div class="box-title">MySQL 8 <span class="badge badge--amber">Docker</span></div>
|
||
<ul>
|
||
<li>winstudentgoaltracker DB</li>
|
||
<li>17 tables</li>
|
||
<li>51 stored procedures</li>
|
||
<li>Initialised from db/docker-init/</li>
|
||
</ul>
|
||
</div>
|
||
<div class="box box--amber">
|
||
<div class="box-title">Core Tables</div>
|
||
<ul>
|
||
<li>user, school_district, program</li>
|
||
<li>user_program (roles junction)</li>
|
||
<li>student, user_student</li>
|
||
<li>goal (recursive), benchmark</li>
|
||
<li>progress_event + junction</li>
|
||
<li>refresh_token</li>
|
||
</ul>
|
||
</div>
|
||
<div class="box box--amber">
|
||
<div class="box-title">Procedure Groups</div>
|
||
<ul>
|
||
<li>sp_Student_* (CRUD)</li>
|
||
<li>sp_Goal_* / sp_Benchmark_*</li>
|
||
<li>sp_ProgressEvent_*</li>
|
||
<li>sp_RefreshToken_*</li>
|
||
<li>sp_Program_* / sp_User_*</li>
|
||
<li>sp_ProgressReport_*</li>
|
||
</ul>
|
||
</div>
|
||
</div>
|
||
</div>
|
||
|
||
<div class="tier-arrow">
|
||
<svg width="16" height="16" viewBox="0 0 24 24" fill="none" stroke="currentColor" stroke-width="2">
|
||
<path d="M12 5v14M5 12l7 7 7-7" />
|
||
</svg>
|
||
<span>Outbound HTTP from API tier only</span>
|
||
</div>
|
||
|
||
<div class="tier tier--ext">
|
||
<div class="tier-label">External</div>
|
||
<div class="tier-body">
|
||
<div class="box box--purple">
|
||
<div class="box-title">Ollama LLM <span class="badge badge--purple">AI</span></div>
|
||
<ul>
|
||
<li>llm.opelly.me</li>
|
||
<li>Model: gemma4:e2b</li>
|
||
<li>Benchmark recommendations</li>
|
||
<li>5-min timeout</li>
|
||
</ul>
|
||
</div>
|
||
<div class="box box--purple">
|
||
<div class="box-title">STT Service <span class="badge badge--purple">AI</span></div>
|
||
<ul>
|
||
<li>stt.opelly.me</li>
|
||
<li>Speech-to-text transcription</li>
|
||
<li>Progress event dictation</li>
|
||
<li>5-min timeout</li>
|
||
</ul>
|
||
</div>
|
||
</div>
|
||
</div>
|
||
|
||
</div><!-- /arch-diagram -->
|
||
|
||
<!-- ── 2. Auth Flow ── -->
|
||
<div class="asec-title"><span>🔐</span> Two-Phase Authentication Flow</div>
|
||
|
||
<div style="overflow-x:auto; padding-bottom:.5rem;">
|
||
<div class="aflow" style="min-width:900px;">
|
||
|
||
<div class="aflow-step">
|
||
<div class="aflow-num">1</div>
|
||
<div class="aflow-bubble">
|
||
<strong>User Submits Credentials</strong>
|
||
email + password<br>
|
||
<span class="note">POST /api/Auth/Login</span>
|
||
</div>
|
||
</div>
|
||
|
||
<div class="aflow-connector">
|
||
<svg width="28" height="16" viewBox="0 0 28 16" fill="none" stroke="currentColor" stroke-width="2">
|
||
<path d="M0 8h24M18 2l6 6-6 6" />
|
||
</svg>
|
||
</div>
|
||
|
||
<div class="aflow-step">
|
||
<div class="aflow-num">2</div>
|
||
<div class="aflow-bubble">
|
||
<strong>Phase 1 Response</strong>
|
||
Session token (5 min)<br>+ program list<br>
|
||
<span class="note">Stored in localStorage</span>
|
||
</div>
|
||
</div>
|
||
|
||
<div class="aflow-connector">
|
||
<svg width="28" height="16" viewBox="0 0 28 16" fill="none" stroke="currentColor" stroke-width="2">
|
||
<path d="M0 8h24M18 2l6 6-6 6" />
|
||
</svg>
|
||
</div>
|
||
|
||
<div class="aflow-step">
|
||
<div class="aflow-num">3</div>
|
||
<div class="aflow-bubble">
|
||
<strong>User Selects Program</strong>
|
||
session token + program ID<br>
|
||
<span class="note">POST /api/Auth/SelectProgram</span>
|
||
</div>
|
||
</div>
|
||
|
||
<div class="aflow-connector">
|
||
<svg width="28" height="16" viewBox="0 0 28 16" fill="none" stroke="currentColor" stroke-width="2">
|
||
<path d="M0 8h24M18 2l6 6-6 6" />
|
||
</svg>
|
||
</div>
|
||
|
||
<div class="aflow-step">
|
||
<div class="aflow-num">4</div>
|
||
<div class="aflow-bubble">
|
||
<strong>Phase 2 Response</strong>
|
||
JWT (1 min) +<br>Refresh token (30 days)<br>
|
||
<span class="note">JWT includes role + program_id</span>
|
||
</div>
|
||
</div>
|
||
|
||
<div class="aflow-connector">
|
||
<svg width="28" height="16" viewBox="0 0 28 16" fill="none" stroke="currentColor" stroke-width="2">
|
||
<path d="M0 8h24M18 2l6 6-6 6" />
|
||
</svg>
|
||
</div>
|
||
|
||
<div class="aflow-step">
|
||
<div class="aflow-num">5</div>
|
||
<div class="aflow-bubble">
|
||
<strong>Authenticated Requests</strong>
|
||
Bearer JWT on all API calls<br>
|
||
<span class="note">Auth interceptor injects token</span>
|
||
</div>
|
||
</div>
|
||
|
||
<div class="aflow-connector">
|
||
<svg width="28" height="16" viewBox="0 0 28 16" fill="none" stroke="currentColor" stroke-width="2">
|
||
<path d="M0 8h24M18 2l6 6-6 6" />
|
||
</svg>
|
||
</div>
|
||
|
||
<div class="aflow-step">
|
||
<div class="aflow-num">6</div>
|
||
<div class="aflow-bubble">
|
||
<strong>Proactive Refresh</strong>
|
||
10 s before JWT expiry:<br>POST /api/Auth/RefreshToken<br>
|
||
<span class="note">Rotated refresh token returned</span>
|
||
</div>
|
||
</div>
|
||
|
||
</div>
|
||
</div>
|
||
|
||
<div class="card" style="margin-top:.75rem;font-size:.8rem;color:#4b5563;">
|
||
<strong>Token storage:</strong> <code>auth_jwt</code>, <code>auth_refresh_token</code>,
|
||
<code>auth_session_token</code> — all in <code>localStorage</code>.
|
||
|
|
||
<strong>401 interceptor:</strong> attempts one silent refresh; if that fails, redirects to
|
||
<code>/login</code>.
|
||
|
|
||
<strong>Refresh token rotation:</strong> each use replaces the old token (tracked via
|
||
<code>replaced_by_token_id</code>).
|
||
</div>
|
||
|
||
<!-- ── 3. Data Model ── -->
|
||
<div class="asec-title"><span>🗄</span> Core Data Model</div>
|
||
|
||
<div class="entity-grid">
|
||
|
||
<div class="entity">
|
||
<div class="entity-header" style="background:#64748b;">school_district</div>
|
||
<div class="entity-fields">
|
||
<div><span class="pk">PK</span> id_school_district</div>
|
||
<div>name</div>
|
||
<div>contact_email</div>
|
||
<div><span class="field-note">Top-level tenant</span></div>
|
||
</div>
|
||
</div>
|
||
|
||
<div class="entity">
|
||
<div class="entity-header" style="background:#2563eb;">program</div>
|
||
<div class="entity-fields">
|
||
<div><span class="pk">PK</span> id_program</div>
|
||
<div><span class="fk">FK</span> id_school_district</div>
|
||
<div>name, description</div>
|
||
<div><span class="field-note">Scope boundary for data</span></div>
|
||
</div>
|
||
</div>
|
||
|
||
<div class="entity">
|
||
<div class="entity-header" style="background:#0d9488;">user</div>
|
||
<div class="entity-fields">
|
||
<div><span class="pk">PK</span> id_user</div>
|
||
<div>email, name</div>
|
||
<div>password_hash, password_salt</div>
|
||
<div>locked_until</div>
|
||
</div>
|
||
</div>
|
||
|
||
<div class="entity">
|
||
<div class="entity-header" style="background:#7c3aed;">user_program <span
|
||
style="font-size:.6rem;opacity:.8;">(junction)</span></div>
|
||
<div class="entity-fields">
|
||
<div><span class="pk">PK</span> id_user_program</div>
|
||
<div><span class="fk">FK</span> id_user</div>
|
||
<div><span class="fk">FK</span> id_program</div>
|
||
<div><span class="fk">FK</span> id_role</div>
|
||
<div>is_primary, status</div>
|
||
</div>
|
||
</div>
|
||
|
||
<div class="entity">
|
||
<div class="entity-header" style="background:#d97706;">student</div>
|
||
<div class="entity-fields">
|
||
<div><span class="pk">PK</span> id_student</div>
|
||
<div><span class="fk">FK</span> id_program</div>
|
||
<div>identifier, program_year</div>
|
||
<div>enrollment_date</div>
|
||
<div>next_iep_date</div>
|
||
</div>
|
||
</div>
|
||
|
||
<div class="entity">
|
||
<div class="entity-header" style="background:#7c3aed;">user_student <span
|
||
style="font-size:.6rem;opacity:.8;">(junction)</span></div>
|
||
<div class="entity-fields">
|
||
<div><span class="pk">PK</span> id_user_student</div>
|
||
<div><span class="fk">FK</span> id_user</div>
|
||
<div><span class="fk">FK</span> id_student</div>
|
||
<div>is_primary</div>
|
||
</div>
|
||
</div>
|
||
|
||
<div class="entity">
|
||
<div class="entity-header" style="background:#16a34a;">goal</div>
|
||
<div class="entity-fields">
|
||
<div><span class="pk">PK</span> id_goal</div>
|
||
<div><span class="fk">FK</span> id_goal_parent <span class="field-note">(self-ref)</span></div>
|
||
<div><span class="fk">FK</span> id_student</div>
|
||
<div>description, category</div>
|
||
<div>baseline, target_completion_date</div>
|
||
<div>close_date, achieved</div>
|
||
</div>
|
||
</div>
|
||
|
||
<div class="entity">
|
||
<div class="entity-header" style="background:#0d9488;">benchmark</div>
|
||
<div class="entity-fields">
|
||
<div><span class="pk">PK</span> id_benchmark</div>
|
||
<div><span class="fk">FK</span> id_goal</div>
|
||
<div>benchmark (description)</div>
|
||
<div>short_name</div>
|
||
</div>
|
||
</div>
|
||
|
||
<div class="entity">
|
||
<div class="entity-header" style="background:#e11d48;">progress_event</div>
|
||
<div class="entity-fields">
|
||
<div><span class="pk">PK</span> id_progress_event</div>
|
||
<div><span class="fk">FK</span> id_goal</div>
|
||
<div><span class="fk">FK</span> id_user_created</div>
|
||
<div>content (rich text)</div>
|
||
<div>is_sensitive</div>
|
||
</div>
|
||
</div>
|
||
|
||
<div class="entity">
|
||
<div class="entity-header" style="background:#7c3aed;">progress_event_benchmark <span
|
||
style="font-size:.6rem;opacity:.8;">(junction)</span></div>
|
||
<div class="entity-fields">
|
||
<div><span class="fk">FK</span> id_progress_event</div>
|
||
<div><span class="fk">FK</span> id_benchmark</div>
|
||
<div><span class="field-note">Links events to benchmarks</span></div>
|
||
</div>
|
||
</div>
|
||
|
||
<div class="entity">
|
||
<div class="entity-header" style="background:#64748b;">refresh_token</div>
|
||
<div class="entity-fields">
|
||
<div><span class="pk">PK</span> id_refresh_token</div>
|
||
<div><span class="fk">FK</span> id_user, id_program</div>
|
||
<div>token_hash, token_salt</div>
|
||
<div>expires_at, revoked_at</div>
|
||
<div>replaced_by_token_id</div>
|
||
</div>
|
||
</div>
|
||
|
||
<div class="entity">
|
||
<div class="entity-header" style="background:#4f46e5;">report_prompt</div>
|
||
<div class="entity-fields">
|
||
<div><span class="pk">PK</span> id_report_prompt</div>
|
||
<div><span class="fk">FK</span> program_id</div>
|
||
<div>report_name</div>
|
||
<div>prompt_template (LLM)</div>
|
||
</div>
|
||
</div>
|
||
|
||
</div>
|
||
|
||
<div class="rel-summary">
|
||
<strong>Key relationships:</strong>
|
||
school_district <strong>1→N</strong> program ·
|
||
program <strong>1→N</strong> student ·
|
||
student <strong>1→N</strong> goal (recursive parent/child) ·
|
||
goal <strong>1→N</strong> benchmark ·
|
||
goal <strong>1→N</strong> progress_event ·
|
||
progress_event <strong>M↔N</strong> benchmark (junction) ·
|
||
user <strong>M↔N</strong> program (via user_program + role) ·
|
||
user <strong>M↔N</strong> student (via user_student)
|
||
</div>
|
||
|
||
<!-- ── 4. Role Hierarchy ── -->
|
||
<div class="asec-title"><span>👥</span> Role Hierarchy & Permissions</div>
|
||
|
||
<div class="role-and-matrix">
|
||
|
||
<div class="role-pane">
|
||
<div class="pane-label">Role Tiers (highest → lowest)</div>
|
||
<div class="role-pyramid">
|
||
<div class="role-row">
|
||
<div class="role-chip" style="background:#0f172a;min-width:280px;">super_admin · <span
|
||
style="font-weight:400;font-size:.7rem;">full platform access</span></div>
|
||
</div>
|
||
<div class="role-row">
|
||
<div class="role-chip" style="background:#1d4ed8;min-width:240px;">district_admin · <span
|
||
style="font-weight:400;font-size:.7rem;">manages programs</span></div>
|
||
</div>
|
||
<div class="role-row">
|
||
<div class="role-chip" style="background:#2563eb;min-width:200px;">program_admin · <span
|
||
style="font-weight:400;font-size:.7rem;">manages users</span></div>
|
||
</div>
|
||
<div class="role-row">
|
||
<div class="role-chip" style="background:#16a34a;min-width:160px;">teacher · <span
|
||
style="font-weight:400;font-size:.7rem;">full student CRUD</span></div>
|
||
</div>
|
||
<div class="role-row">
|
||
<div class="role-chip" style="background:#d97706;min-width:120px;">paraeducator · <span
|
||
style="font-weight:400;font-size:.7rem;">log events</span></div>
|
||
</div>
|
||
</div>
|
||
</div>
|
||
|
||
<div class="role-pane" style="overflow-x:auto;">
|
||
<div class="pane-label">Sample Permission Matrix</div>
|
||
<table class="matrix">
|
||
<thead>
|
||
<tr>
|
||
<th>Entity / Action</th>
|
||
<th>super_admin</th>
|
||
<th>district_admin</th>
|
||
<th>program_admin</th>
|
||
<th>teacher</th>
|
||
<th>paraeducator</th>
|
||
</tr>
|
||
</thead>
|
||
<tbody>
|
||
<tr>
|
||
<td>Student — Create</td>
|
||
<td><span class="allow">Allow</span></td>
|
||
<td><span class="allow">Allow</span></td>
|
||
<td><span class="allow">Allow</span></td>
|
||
<td><span class="allow">Allow</span></td>
|
||
<td><span class="deny">Deny</span></td>
|
||
</tr>
|
||
<tr>
|
||
<td>Student — Update</td>
|
||
<td><span class="allow">Allow</span></td>
|
||
<td><span class="allow">Allow</span></td>
|
||
<td><span class="allow">Allow</span></td>
|
||
<td><span class="mine">Mine</span></td>
|
||
<td><span class="deny">Deny</span></td>
|
||
</tr>
|
||
<tr>
|
||
<td>Goal — Create</td>
|
||
<td><span class="allow">Allow</span></td>
|
||
<td><span class="allow">Allow</span></td>
|
||
<td><span class="allow">Allow</span></td>
|
||
<td><span class="mine">Mine</span></td>
|
||
<td><span class="deny">Deny</span></td>
|
||
</tr>
|
||
<tr>
|
||
<td>Goal — Update</td>
|
||
<td><span class="allow">Allow</span></td>
|
||
<td><span class="allow">Allow</span></td>
|
||
<td><span class="allow">Allow</span></td>
|
||
<td><span class="mine">Mine</span></td>
|
||
<td><span class="deny">Deny</span></td>
|
||
</tr>
|
||
<tr>
|
||
<td>ProgressEvent — Create</td>
|
||
<td><span class="allow">Allow</span></td>
|
||
<td><span class="allow">Allow</span></td>
|
||
<td><span class="allow">Allow</span></td>
|
||
<td><span class="allow">Allow</span></td>
|
||
<td><span class="mine">Mine</span></td>
|
||
</tr>
|
||
<tr>
|
||
<td>ProgressEvent — Delete</td>
|
||
<td><span class="allow">Allow</span></td>
|
||
<td><span class="allow">Allow</span></td>
|
||
<td><span class="allow">Allow</span></td>
|
||
<td><span class="mine">Mine</span></td>
|
||
<td><span class="deny">Deny</span></td>
|
||
</tr>
|
||
<tr>
|
||
<td>Program — Create</td>
|
||
<td><span class="allow">Allow</span></td>
|
||
<td><span class="mine">Mine</span></td>
|
||
<td><span class="deny">Deny</span></td>
|
||
<td><span class="deny">Deny</span></td>
|
||
<td><span class="deny">Deny</span></td>
|
||
</tr>
|
||
<tr>
|
||
<td>User — Create</td>
|
||
<td><span class="allow">Allow</span></td>
|
||
<td><span class="mine">Mine</span></td>
|
||
<td><span class="mine">Mine</span></td>
|
||
<td><span class="deny">Deny</span></td>
|
||
<td><span class="deny">Deny</span></td>
|
||
</tr>
|
||
</tbody>
|
||
</table>
|
||
<div style="font-size:.63rem;color:#9ca3af;margin-top:.5rem;">
|
||
<span class="allow">Allow</span> = full access
|
||
<span class="mine">Mine</span> = own records only
|
||
<span class="deny">Deny</span> = not permitted
|
||
</div>
|
||
</div>
|
||
|
||
</div>
|
||
|
||
<!-- ── 5. Deployment ── -->
|
||
<div class="asec-title"><span>🐳</span> Deployment & Infrastructure</div>
|
||
|
||
<div class="info-cards">
|
||
<div class="info-card">
|
||
<h3>Docker Containers</h3>
|
||
<ul>
|
||
<li>mysql:8 (port 3309)</li>
|
||
<li>.NET API (port 8005)</li>
|
||
<li>Angular / Nginx (port 8006)</li>
|
||
<li>Traefik reverse proxy</li>
|
||
</ul>
|
||
</div>
|
||
<div class="info-card">
|
||
<h3>Docker Networks</h3>
|
||
<ul>
|
||
<li>web — external HTTPS routing</li>
|
||
<li>backend — internal service mesh</li>
|
||
<li>API depends on MySQL healthcheck</li>
|
||
<li>UI depends on API (build time)</li>
|
||
</ul>
|
||
</div>
|
||
<div class="info-card">
|
||
<h3>Environment Config</h3>
|
||
<ul>
|
||
<li>MYSQL_ROOT_PASSWORD</li>
|
||
<li>MYSQL_DATABASE / USER / PASSWORD</li>
|
||
<li>JWT_KEY (signing secret)</li>
|
||
<li>MYSQL_HOST / PORT</li>
|
||
</ul>
|
||
</div>
|
||
<div class="info-card">
|
||
<h3>Frontend Environments</h3>
|
||
<ul>
|
||
<li>Dev: localhost:5000 (API)</li>
|
||
<li>Prod: winapi.opelly.me (API)</li>
|
||
<li>Served by Nginx in production</li>
|
||
<li>Angular CLI dev server locally</li>
|
||
</ul>
|
||
</div>
|
||
<div class="info-card">
|
||
<h3>DB Initialisation</h3>
|
||
<ul>
|
||
<li>Schemas from db/docker-init/</li>
|
||
<li>51 stored procedures</li>
|
||
<li>Health check gates API startup</li>
|
||
<li>No ORM migrations — raw SQL</li>
|
||
</ul>
|
||
</div>
|
||
<div class="info-card">
|
||
<h3>AI / External Services</h3>
|
||
<ul>
|
||
<li>Ollama LLM — llm.opelly.me</li>
|
||
<li>STT service — stt.opelly.me</li>
|
||
<li>Both called from API tier only</li>
|
||
<li>5-minute timeout on both</li>
|
||
</ul>
|
||
</div>
|
||
</div>
|
||
|
||
<!-- ── 6. Key Design Decisions ── -->
|
||
<div class="asec-title"><span>⚙️</span> Key Design Decisions</div>
|
||
|
||
<div class="info-cards">
|
||
<div class="info-card">
|
||
<h3>Two-Phase JWT Auth</h3>
|
||
<ul>
|
||
<li>Phase 1: credentials → session token</li>
|
||
<li>Phase 2: program select → scoped JWT</li>
|
||
<li>Prevents data leakage before program chosen</li>
|
||
<li>JWT scoped to one program at a time</li>
|
||
</ul>
|
||
</div>
|
||
<div class="info-card">
|
||
<h3>Refresh Token Rotation</h3>
|
||
<ul>
|
||
<li>New token issued on each refresh</li>
|
||
<li>Old token replaced (not deleted)</li>
|
||
<li>Genealogy tracked via replaced_by</li>
|
||
<li>Prevents replay attacks</li>
|
||
</ul>
|
||
</div>
|
||
<div class="info-card">
|
||
<h3>Program-Scoped Multi-Tenancy</h3>
|
||
<ul>
|
||
<li>JWT contains program_id claim</li>
|
||
<li>All queries filtered by program</li>
|
||
<li>district_admin scoped to district</li>
|
||
<li>super_admin bypasses scope</li>
|
||
</ul>
|
||
</div>
|
||
<div class="info-card">
|
||
<h3>Dapper + Stored Procedures</h3>
|
||
<ul>
|
||
<li>No ORM (Entity Framework)</li>
|
||
<li>Business logic close to data</li>
|
||
<li>Dapper maps rows to C# objects</li>
|
||
<li>51 procedures for full coverage</li>
|
||
</ul>
|
||
</div>
|
||
<div class="info-card">
|
||
<h3>Angular Signals (not RxJS)</h3>
|
||
<ul>
|
||
<li>Auth state as reactive signals</li>
|
||
<li>Computed signals derive user context</li>
|
||
<li>Simpler than Observable chains</li>
|
||
<li>Auto-update dependent UI</li>
|
||
</ul>
|
||
</div>
|
||
<div class="info-card">
|
||
<h3>Centralised Permission Matrix</h3>
|
||
<ul>
|
||
<li>Declarative rules per entity + action</li>
|
||
<li>Allow / MineOnly / Deny granularity</li>
|
||
<li>Evaluated at request time in API</li>
|
||
<li>Single source of truth for authz</li>
|
||
</ul>
|
||
</div>
|
||
</div>
|
||
|
||
</section>
|
||
</main>
|
||
</div>
|
||
|
||
<script>
|
||
// ─── ERD Schema Data ───
|
||
const schema = {
|
||
tables: {
|
||
user: {
|
||
columns: [
|
||
{ name: 'id_user', type: 'char(36)', pk: true },
|
||
{ name: 'email', type: 'varchar(255)' },
|
||
{ name: 'name', type: 'varchar(255)' },
|
||
{ name: 'password_hash', type: 'varchar(255)' },
|
||
{ name: 'password_salt', type: 'varchar(255)' },
|
||
{ name: 'password_updated_at', type: 'timestamp' },
|
||
{ name: 'failed_login_attempts', type: 'int' },
|
||
{ name: 'locked_until', type: 'timestamp' },
|
||
{ name: 'created_at', type: 'timestamp' }
|
||
],
|
||
color: '#6ea8fe'
|
||
},
|
||
student: {
|
||
columns: [
|
||
{ name: 'id_student', type: 'char(36)', pk: true },
|
||
{ name: 'id_program', type: 'char(36)', fk: 'program' },
|
||
{ name: 'identifier', type: 'varchar(50)' },
|
||
{ name: 'program_year', type: 'int' },
|
||
{ name: 'enrollment_date', type: 'date' },
|
||
{ name: 'next_iep_date', type: 'date' },
|
||
{ name: 'created_at', type: 'timestamp' }
|
||
],
|
||
color: '#66d9a0'
|
||
},
|
||
goal: {
|
||
columns: [
|
||
{ name: 'id_goal', type: 'char(36)', pk: true },
|
||
{ name: 'id_goal_parent', type: 'char(36)', fk: 'goal' },
|
||
{ name: 'id_student', type: 'char(36)', fk: 'student' },
|
||
{ name: 'id_user_created', type: 'char(36)', fk: 'user' },
|
||
{ name: 'description', type: 'text' },
|
||
{ name: 'category', type: 'varchar(255)' },
|
||
{ name: 'baseline', type: 'text' },
|
||
{ name: 'target_completion_date', type: 'date' },
|
||
{ name: 'close_date', type: 'date' },
|
||
{ name: 'achieved', type: 'tinyint(1)' },
|
||
{ name: 'close_notes', type: 'text' },
|
||
{ name: 'created_at', type: 'timestamp' },
|
||
{ name: 'updated_at', type: 'timestamp' }
|
||
],
|
||
color: '#f0b866'
|
||
},
|
||
benchmark: {
|
||
columns: [
|
||
{ name: 'id_benchmark', type: 'char(36)', pk: true },
|
||
{ name: 'id_goal', type: 'char(36)', fk: 'goal' },
|
||
{ name: 'id_user_created', type: 'char(36)', fk: 'user' },
|
||
{ name: 'benchmark', type: 'text' },
|
||
{ name: 'short_name', type: 'varchar(50)' },
|
||
{ name: 'created_at', type: 'datetime' },
|
||
{ name: 'updated_at', type: 'datetime' }
|
||
],
|
||
color: '#f0b866'
|
||
},
|
||
progress_event: {
|
||
columns: [
|
||
{ name: 'id_progress_event', type: 'char(36)', pk: true },
|
||
{ name: 'id_goal', type: 'char(36)', fk: 'goal' },
|
||
{ name: 'id_user_created', type: 'char(36)', fk: 'user' },
|
||
{ name: 'content', type: 'text' },
|
||
{ name: 'is_sensitive', type: 'tinyint(1)' },
|
||
{ name: 'created_at', type: 'timestamp' },
|
||
{ name: 'updated_at', type: 'timestamp' }
|
||
],
|
||
color: '#c792ea'
|
||
},
|
||
progress_event_benchmark: {
|
||
columns: [
|
||
{ name: 'id_progress_event_benchmark', type: 'char(36)', pk: true },
|
||
{ name: 'id_progress_event', type: 'char(36)', fk: 'progress_event' },
|
||
{ name: 'id_benchmark', type: 'char(36)', fk: 'benchmark' },
|
||
{ name: 'created_at', type: 'datetime' }
|
||
],
|
||
color: '#c792ea'
|
||
},
|
||
progress_report: {
|
||
columns: [
|
||
{ name: 'id_progress_report', type: 'char(36)', pk: true },
|
||
{ name: 'id_student', type: 'char(36)', fk: 'student' },
|
||
{ name: 'id_goal', type: 'char(36)', fk: 'goal' },
|
||
{ name: 'id_user_created', type: 'char(36)', fk: 'user' },
|
||
{ name: 'period', type: 'varchar(10)' },
|
||
{ name: 'year', type: 'int' },
|
||
{ name: 'summary', type: 'text' },
|
||
{ name: 'generated_at', type: 'timestamp' }
|
||
],
|
||
color: '#c792ea'
|
||
},
|
||
health_note: {
|
||
columns: [
|
||
{ name: 'id_health_note', type: 'char(36)', pk: true },
|
||
{ name: 'id_student', type: 'char(36)', fk: 'student' },
|
||
{ name: 'id_user_created', type: 'char(36)', fk: 'user' },
|
||
{ name: 'content', type: 'text' },
|
||
{ name: 'created_at', type: 'timestamp' }
|
||
],
|
||
color: '#66d9a0'
|
||
},
|
||
program: {
|
||
columns: [
|
||
{ name: 'id_program', type: 'char(36)', pk: true },
|
||
{ name: 'id_school_district', type: 'char(36)', fk: 'school_district' },
|
||
{ name: 'name', type: 'varchar(255)' },
|
||
{ name: 'description', type: 'text' },
|
||
{ name: 'created_at', type: 'timestamp' }
|
||
],
|
||
color: '#f07178'
|
||
},
|
||
school_district: {
|
||
columns: [
|
||
{ name: 'id_school_district', type: 'char(36)', pk: true },
|
||
{ name: 'name', type: 'varchar(255)' },
|
||
{ name: 'contact_email', type: 'varchar(255)' },
|
||
{ name: 'created_at', type: 'timestamp' }
|
||
],
|
||
color: '#f07178'
|
||
},
|
||
role: {
|
||
columns: [
|
||
{ name: 'id_role', type: 'char(36)', pk: true },
|
||
{ name: 'name', type: 'varchar(100)' },
|
||
{ name: 'internal_name', type: 'varchar(100)' },
|
||
{ name: 'description', type: 'text' },
|
||
{ name: 'created_at', type: 'timestamp' }
|
||
],
|
||
color: '#6ea8fe'
|
||
},
|
||
user_program: {
|
||
columns: [
|
||
{ name: 'id_user_program', type: 'char(36)', pk: true },
|
||
{ name: 'id_user', type: 'char(36)', fk: 'user' },
|
||
{ name: 'id_program', type: 'char(36)', fk: 'program' },
|
||
{ name: 'id_role', type: 'char(36)', fk: 'role' },
|
||
{ name: 'is_primary', type: 'tinyint(1)' },
|
||
{ name: 'status', type: 'varchar(20)' },
|
||
{ name: 'joined_at', type: 'timestamp' }
|
||
],
|
||
color: '#6ea8fe'
|
||
},
|
||
user_student: {
|
||
columns: [
|
||
{ name: 'id_user_student', type: 'char(36)', pk: true },
|
||
{ name: 'id_user', type: 'char(36)', fk: 'user' },
|
||
{ name: 'id_student', type: 'char(36)', fk: 'student' },
|
||
{ name: 'is_primary', type: 'tinyint(1)' }
|
||
],
|
||
color: '#6ea8fe'
|
||
},
|
||
password_history: {
|
||
columns: [
|
||
{ name: 'id_password_history', type: 'char(36)', pk: true },
|
||
{ name: 'id_user', type: 'char(36)', fk: 'user' },
|
||
{ name: 'password_hash', type: 'varchar(255)' },
|
||
{ name: 'created_at', type: 'timestamp' }
|
||
],
|
||
color: '#5a5f75'
|
||
},
|
||
password_reset_token: {
|
||
columns: [
|
||
{ name: 'id_password_reset_token', type: 'char(36)', pk: true },
|
||
{ name: 'id_user', type: 'char(36)', fk: 'user' },
|
||
{ name: 'token_hash', type: 'varchar(255)' },
|
||
{ name: 'expires_at', type: 'timestamp' },
|
||
{ name: 'created_at', type: 'timestamp' },
|
||
{ name: 'used_at', type: 'timestamp' },
|
||
{ name: 'invalidated_at', type: 'timestamp' },
|
||
{ name: 'request_ip', type: 'varchar(45)' }
|
||
],
|
||
color: '#5a5f75'
|
||
},
|
||
refresh_token: {
|
||
columns: [
|
||
{ name: 'id_refresh_token', type: 'char(36)', pk: true },
|
||
{ name: 'id_user', type: 'char(36)', fk: 'user' },
|
||
{ name: 'id_program', type: 'char(36)', fk: 'program' },
|
||
{ name: 'token_hash', type: 'varchar(512)' },
|
||
{ name: 'token_salt', type: 'varchar(512)' },
|
||
{ name: 'expires_at', type: 'timestamp' },
|
||
{ name: 'last_used_at', type: 'timestamp' },
|
||
{ name: 'revoked_at', type: 'timestamp' },
|
||
{ name: 'device_info', type: 'varchar(255)' },
|
||
{ name: 'user_agent', type: 'varchar(512)' },
|
||
{ name: 'replaced_by_token_id', type: 'char(36)', fk: 'refresh_token' },
|
||
{ name: 'created_at', type: 'timestamp' },
|
||
{ name: 'updated_at', type: 'timestamp' }
|
||
],
|
||
color: '#5a5f75'
|
||
},
|
||
ReportPrompt: {
|
||
columns: [
|
||
{ name: 'id_ReportPrompt', type: 'char(36)', pk: true },
|
||
{ name: 'prompt', type: 'text' },
|
||
{ name: 'reportname', type: 'char(100)' },
|
||
{ name: 'id_program', type: 'char(36)', fk: 'program' }
|
||
],
|
||
color: '#c792ea'
|
||
}
|
||
}
|
||
};
|
||
|
||
const relationships = [
|
||
{ from: 'goal', fromCol: 'id_goal_parent', to: 'goal', toCol: 'id_goal', label: 'parent' },
|
||
{ from: 'goal', fromCol: 'id_student', to: 'student', toCol: 'id_student' },
|
||
{ from: 'goal', fromCol: 'id_user_created', to: 'user', toCol: 'id_user' },
|
||
{ from: 'health_note', fromCol: 'id_student', to: 'student', toCol: 'id_student' },
|
||
{ from: 'health_note', fromCol: 'id_user_created', to: 'user', toCol: 'id_user' },
|
||
{ from: 'password_history', fromCol: 'id_user', to: 'user', toCol: 'id_user' },
|
||
{ from: 'password_reset_token', fromCol: 'id_user', to: 'user', toCol: 'id_user' },
|
||
{ from: 'program', fromCol: 'id_school_district', to: 'school_district', toCol: 'id_school_district' },
|
||
{ from: 'progress_event', fromCol: 'id_goal', to: 'goal', toCol: 'id_goal' },
|
||
{ from: 'progress_event', fromCol: 'id_user_created', to: 'user', toCol: 'id_user' },
|
||
{ from: 'progress_event_benchmark', fromCol: 'id_progress_event', to: 'progress_event', toCol: 'id_progress_event' },
|
||
{ from: 'progress_event_benchmark', fromCol: 'id_benchmark', to: 'benchmark', toCol: 'id_benchmark' },
|
||
{ from: 'progress_report', fromCol: 'id_student', to: 'student', toCol: 'id_student' },
|
||
{ from: 'progress_report', fromCol: 'id_goal', to: 'goal', toCol: 'id_goal' },
|
||
{ from: 'progress_report', fromCol: 'id_user_created', to: 'user', toCol: 'id_user' },
|
||
{ from: 'refresh_token', fromCol: 'id_user', to: 'user', toCol: 'id_user' },
|
||
{ from: 'refresh_token', fromCol: 'id_program', to: 'program', toCol: 'id_program' },
|
||
{ from: 'refresh_token', fromCol: 'replaced_by_token_id', to: 'refresh_token', toCol: 'id_refresh_token', label: 'replaces' },
|
||
{ from: 'benchmark', fromCol: 'id_goal', to: 'goal', toCol: 'id_goal' },
|
||
{ from: 'benchmark', fromCol: 'id_user_created', to: 'user', toCol: 'id_user' },
|
||
{ from: 'student', fromCol: 'id_program', to: 'program', toCol: 'id_program' },
|
||
{ from: 'user_program', fromCol: 'id_user', to: 'user', toCol: 'id_user' },
|
||
{ from: 'user_program', fromCol: 'id_program', to: 'program', toCol: 'id_program' },
|
||
{ from: 'user_program', fromCol: 'id_role', to: 'role', toCol: 'id_role' },
|
||
{ from: 'user_student', fromCol: 'id_user', to: 'user', toCol: 'id_user' },
|
||
{ from: 'user_student', fromCol: 'id_student', to: 'student', toCol: 'id_student' },
|
||
{ from: 'ReportPrompt', fromCol: 'id_program', to: 'program', toCol: 'id_program' }
|
||
];
|
||
|
||
document.getElementById('rel-count').textContent = relationships.length;
|
||
|
||
const positions = {
|
||
user: { x: 100, y: 300 },
|
||
role: { x: 100, y: 50 },
|
||
user_program: { x: 380, y: 80 },
|
||
user_student: { x: 380, y: 340 },
|
||
school_district: { x: 680, y: 50 },
|
||
program: { x: 680, y: 260 },
|
||
student: { x: 680, y: 500 },
|
||
goal: { x: 1050, y: 260 },
|
||
benchmark: { x: 1050, y: 50 },
|
||
progress_event: { x: 1380, y: 100 },
|
||
progress_event_benchmark: { x: 1380, y: 340 },
|
||
progress_report: { x: 1050, y: 570 },
|
||
health_note: { x: 680, y: 760 },
|
||
ReportPrompt: { x: 1380, y: 570 },
|
||
password_history: { x: 100, y: 620 },
|
||
password_reset_token: { x: 100, y: 800 },
|
||
refresh_token: { x: 380, y: 620 }
|
||
};
|
||
|
||
const canvas = document.getElementById('canvas');
|
||
const svgLines = document.getElementById('lines');
|
||
const container = document.getElementById('canvas-container');
|
||
const tableElements = {};
|
||
let scale = 0.85;
|
||
let panX = 50, panY = 20;
|
||
let showLabels = true;
|
||
|
||
function renderTables() {
|
||
for (const [tableName, table] of Object.entries(schema.tables)) {
|
||
const pos = positions[tableName] || { x: 0, y: 0 };
|
||
const el = document.createElement('div');
|
||
el.className = 'table-node';
|
||
el.id = `table-${tableName}`;
|
||
el.style.left = pos.x + 'px';
|
||
el.style.top = pos.y + 'px';
|
||
|
||
const headerColor = table.color || '#6ea8fe';
|
||
let html = `
|
||
<div class="table-header">
|
||
<div class="table-icon" style="background:${headerColor}22;color:${headerColor}">⬡</div>
|
||
<span class="table-name">${tableName}</span>
|
||
</div>
|
||
<div class="table-body">
|
||
`;
|
||
for (const col of table.columns) {
|
||
const isPk = col.pk;
|
||
const isFk = col.fk;
|
||
let badge = '<span class="col-badge empty"></span>';
|
||
if (isPk) badge = '<span class="col-badge pk">PK</span>';
|
||
else if (isFk) badge = '<span class="col-badge fk">FK</span>';
|
||
|
||
let nameClass = 'col-name';
|
||
if (isPk) nameClass += ' pk-name';
|
||
else if (isFk) nameClass += ' fk-name';
|
||
|
||
html += `
|
||
<div class="column-row" data-col="${col.name}">
|
||
${badge}
|
||
<span class="${nameClass}">${col.name}</span>
|
||
<span class="col-type">${col.type}</span>
|
||
</div>`;
|
||
}
|
||
html += '</div>';
|
||
el.innerHTML = html;
|
||
canvas.appendChild(el);
|
||
tableElements[tableName] = el;
|
||
makeDraggable(el, tableName);
|
||
}
|
||
}
|
||
|
||
function getColumnY(tableName, colName) {
|
||
const el = tableElements[tableName];
|
||
if (!el) return 0;
|
||
const row = el.querySelector(`[data-col="${colName}"]`);
|
||
if (!row) return el.offsetTop + 20;
|
||
return el.offsetTop + row.offsetTop + row.offsetHeight / 2;
|
||
}
|
||
|
||
function drawRelationships() {
|
||
svgLines.innerHTML = '';
|
||
|
||
const defs = document.createElementNS('http://www.w3.org/2000/svg', 'defs');
|
||
const marker = document.createElementNS('http://www.w3.org/2000/svg', 'marker');
|
||
marker.setAttribute('id', 'arrowhead');
|
||
marker.setAttribute('markerWidth', '8');
|
||
marker.setAttribute('markerHeight', '6');
|
||
marker.setAttribute('refX', '8');
|
||
marker.setAttribute('refY', '3');
|
||
marker.setAttribute('orient', 'auto');
|
||
const poly = document.createElementNS('http://www.w3.org/2000/svg', 'polygon');
|
||
poly.setAttribute('points', '0 0, 8 3, 0 6');
|
||
poly.setAttribute('class', 'rel-marker');
|
||
marker.appendChild(poly);
|
||
defs.appendChild(marker);
|
||
|
||
const markerH = document.createElementNS('http://www.w3.org/2000/svg', 'marker');
|
||
markerH.setAttribute('id', 'arrowhead-hl');
|
||
markerH.setAttribute('markerWidth', '8');
|
||
markerH.setAttribute('markerHeight', '6');
|
||
markerH.setAttribute('refX', '8');
|
||
markerH.setAttribute('refY', '3');
|
||
markerH.setAttribute('orient', 'auto');
|
||
const polyH = document.createElementNS('http://www.w3.org/2000/svg', 'polygon');
|
||
polyH.setAttribute('points', '0 0, 8 3, 0 6');
|
||
polyH.setAttribute('class', 'rel-marker highlighted');
|
||
markerH.appendChild(polyH);
|
||
defs.appendChild(markerH);
|
||
svgLines.appendChild(defs);
|
||
|
||
for (const rel of relationships) {
|
||
const fromEl = tableElements[rel.from];
|
||
const toEl = tableElements[rel.to];
|
||
if (!fromEl || !toEl) continue;
|
||
|
||
const fromY = getColumnY(rel.from, rel.fromCol);
|
||
const toY = getColumnY(rel.to, rel.toCol);
|
||
const fromLeft = fromEl.offsetLeft;
|
||
const fromRight = fromEl.offsetLeft + fromEl.offsetWidth;
|
||
const toLeft = toEl.offsetLeft;
|
||
const toRight = toEl.offsetLeft + toEl.offsetWidth;
|
||
|
||
let x1, x2;
|
||
if (rel.from === rel.to) {
|
||
x1 = fromRight;
|
||
x2 = fromRight;
|
||
const path = document.createElementNS('http://www.w3.org/2000/svg', 'path');
|
||
const loopSize = 40;
|
||
const d = `M ${x1} ${fromY} C ${x1 + loopSize} ${fromY - loopSize}, ${x2 + loopSize} ${toY + loopSize}, ${x2} ${toY}`;
|
||
path.setAttribute('d', d);
|
||
path.setAttribute('class', 'rel-line');
|
||
path.setAttribute('marker-end', 'url(#arrowhead)');
|
||
path.dataset.from = rel.from;
|
||
path.dataset.to = rel.to;
|
||
svgLines.appendChild(path);
|
||
|
||
if (showLabels && rel.label) {
|
||
const text = document.createElementNS('http://www.w3.org/2000/svg', 'text');
|
||
text.setAttribute('x', x1 + loopSize + 4);
|
||
text.setAttribute('y', (fromY + toY) / 2);
|
||
text.setAttribute('class', 'rel-label');
|
||
text.textContent = rel.label;
|
||
svgLines.appendChild(text);
|
||
}
|
||
continue;
|
||
}
|
||
|
||
const fromCX = (fromLeft + fromRight) / 2;
|
||
const toCX = (toLeft + toRight) / 2;
|
||
if (fromCX < toCX) { x1 = fromRight; x2 = toLeft; }
|
||
else { x1 = fromLeft; x2 = toRight; }
|
||
|
||
const dx = Math.abs(x2 - x1);
|
||
const cpOffset = Math.max(40, dx * 0.35);
|
||
const path = document.createElementNS('http://www.w3.org/2000/svg', 'path');
|
||
const cp1x = x1 < x2 ? x1 + cpOffset : x1 - cpOffset;
|
||
const cp2x = x1 < x2 ? x2 - cpOffset : x2 + cpOffset;
|
||
const d = `M ${x1} ${fromY} C ${cp1x} ${fromY}, ${cp2x} ${toY}, ${x2} ${toY}`;
|
||
path.setAttribute('d', d);
|
||
path.setAttribute('class', 'rel-line');
|
||
path.setAttribute('marker-end', 'url(#arrowhead)');
|
||
path.dataset.from = rel.from;
|
||
path.dataset.to = rel.to;
|
||
svgLines.appendChild(path);
|
||
|
||
if (showLabels && rel.label) {
|
||
const mx = (x1 + x2) / 2;
|
||
const my = (fromY + toY) / 2 - 6;
|
||
const text = document.createElementNS('http://www.w3.org/2000/svg', 'text');
|
||
text.setAttribute('x', mx);
|
||
text.setAttribute('y', my);
|
||
text.setAttribute('class', 'rel-label');
|
||
text.setAttribute('text-anchor', 'middle');
|
||
text.textContent = rel.label;
|
||
svgLines.appendChild(text);
|
||
}
|
||
}
|
||
}
|
||
|
||
function makeDraggable(el, tableName) {
|
||
let startX, startY, origX, origY;
|
||
el.addEventListener('mousedown', (e) => {
|
||
if (e.button !== 0) return;
|
||
e.stopPropagation();
|
||
el.classList.add('dragging');
|
||
startX = e.clientX;
|
||
startY = e.clientY;
|
||
origX = el.offsetLeft;
|
||
origY = el.offsetTop;
|
||
|
||
function onMove(e) {
|
||
const dx = (e.clientX - startX) / scale;
|
||
const dy = (e.clientY - startY) / scale;
|
||
el.style.left = (origX + dx) + 'px';
|
||
el.style.top = (origY + dy) + 'px';
|
||
positions[tableName].x = origX + dx;
|
||
positions[tableName].y = origY + dy;
|
||
drawRelationships();
|
||
updateMinimap();
|
||
}
|
||
function onUp() {
|
||
el.classList.remove('dragging');
|
||
document.removeEventListener('mousemove', onMove);
|
||
document.removeEventListener('mouseup', onUp);
|
||
}
|
||
document.addEventListener('mousemove', onMove);
|
||
document.addEventListener('mouseup', onUp);
|
||
});
|
||
el.addEventListener('mouseenter', () => highlightTable(tableName));
|
||
el.addEventListener('mouseleave', () => clearHighlights());
|
||
}
|
||
|
||
function highlightTable(tableName) {
|
||
const lines = svgLines.querySelectorAll('.rel-line');
|
||
lines.forEach(line => {
|
||
if (line.dataset.from === tableName || line.dataset.to === tableName) {
|
||
line.classList.add('highlighted');
|
||
line.setAttribute('marker-end', 'url(#arrowhead-hl)');
|
||
} else {
|
||
line.style.opacity = '0.15';
|
||
}
|
||
});
|
||
}
|
||
|
||
function clearHighlights() {
|
||
const lines = svgLines.querySelectorAll('.rel-line');
|
||
lines.forEach(line => {
|
||
line.classList.remove('highlighted');
|
||
line.setAttribute('marker-end', 'url(#arrowhead)');
|
||
line.style.opacity = '';
|
||
});
|
||
}
|
||
|
||
let isPanning = false, panStartX, panStartY;
|
||
|
||
container.addEventListener('mousedown', (e) => {
|
||
if (e.target !== container && e.target !== canvas && e.target.tagName !== 'svg') return;
|
||
isPanning = true;
|
||
panStartX = e.clientX - panX;
|
||
panStartY = e.clientY - panY;
|
||
container.style.cursor = 'grabbing';
|
||
});
|
||
|
||
document.addEventListener('mousemove', (e) => {
|
||
if (!isPanning) return;
|
||
panX = e.clientX - panStartX;
|
||
panY = e.clientY - panStartY;
|
||
applyTransform();
|
||
updateMinimap();
|
||
});
|
||
|
||
document.addEventListener('mouseup', () => {
|
||
isPanning = false;
|
||
container.style.cursor = 'grab';
|
||
});
|
||
|
||
container.addEventListener('wheel', (e) => {
|
||
e.preventDefault();
|
||
const delta = e.deltaY > 0 ? 0.92 : 1.08;
|
||
const newScale = Math.max(0.2, Math.min(2, scale * delta));
|
||
const rect = container.getBoundingClientRect();
|
||
const cx = e.clientX - rect.left;
|
||
const cy = e.clientY - rect.top;
|
||
panX = cx - (cx - panX) * (newScale / scale);
|
||
panY = cy - (cy - panY) * (newScale / scale);
|
||
scale = newScale;
|
||
applyTransform();
|
||
updateMinimap();
|
||
}, { passive: false });
|
||
|
||
function applyTransform() {
|
||
canvas.style.transform = `translate(${panX}px, ${panY}px) scale(${scale})`;
|
||
}
|
||
|
||
function zoomIn() { scale = Math.min(2, scale * 1.2); applyTransform(); updateMinimap(); }
|
||
function zoomOut() { scale = Math.max(0.2, scale * 0.8); applyTransform(); updateMinimap(); }
|
||
|
||
function resetView() {
|
||
scale = 0.85; panX = 50; panY = 20;
|
||
applyTransform(); updateMinimap();
|
||
}
|
||
|
||
function toggleLabels() {
|
||
showLabels = !showLabels;
|
||
drawRelationships();
|
||
}
|
||
|
||
const minimapCanvas = document.getElementById('minimap-canvas');
|
||
const mCtx = minimapCanvas.getContext('2d');
|
||
|
||
function updateMinimap() {
|
||
const mw = 180, mh = 120;
|
||
minimapCanvas.width = mw * 2;
|
||
minimapCanvas.height = mh * 2;
|
||
minimapCanvas.style.width = mw + 'px';
|
||
minimapCanvas.style.height = mh + 'px';
|
||
mCtx.scale(2, 2);
|
||
mCtx.clearRect(0, 0, mw, mh);
|
||
mCtx.fillStyle = '#f5f7fb';
|
||
mCtx.fillRect(0, 0, mw, mh);
|
||
|
||
let minX = Infinity, minY = Infinity, maxX = -Infinity, maxY = -Infinity;
|
||
for (const [name, pos] of Object.entries(positions)) {
|
||
const el = tableElements[name];
|
||
if (!el) continue;
|
||
minX = Math.min(minX, pos.x); minY = Math.min(minY, pos.y);
|
||
maxX = Math.max(maxX, pos.x + el.offsetWidth); maxY = Math.max(maxY, pos.y + el.offsetHeight);
|
||
}
|
||
const padding = 40;
|
||
minX -= padding; minY -= padding; maxX += padding; maxY += padding;
|
||
const scaleX = mw / (maxX - minX);
|
||
const scaleY = mh / (maxY - minY);
|
||
const ms = Math.min(scaleX, scaleY);
|
||
|
||
for (const [name, pos] of Object.entries(positions)) {
|
||
const el = tableElements[name];
|
||
if (!el) continue;
|
||
const color = schema.tables[name]?.color || '#6ea8fe';
|
||
mCtx.fillStyle = color + '60';
|
||
mCtx.fillRect((pos.x - minX) * ms, (pos.y - minY) * ms, el.offsetWidth * ms, el.offsetHeight * ms);
|
||
}
|
||
|
||
const rect = container.getBoundingClientRect();
|
||
const vx = (-panX / scale - minX) * ms;
|
||
const vy = (-panY / scale - minY) * ms;
|
||
const vw = (rect.width / scale) * ms;
|
||
const vh = (rect.height / scale) * ms;
|
||
mCtx.strokeStyle = '#6ea8fe';
|
||
mCtx.lineWidth = 1;
|
||
mCtx.strokeRect(vx, vy, vw, vh);
|
||
}
|
||
|
||
renderTables();
|
||
drawRelationships();
|
||
applyTransform();
|
||
setTimeout(updateMinimap, 100);
|
||
window.addEventListener('resize', updateMinimap);
|
||
</script>
|
||
</body>
|
||
|
||
</html> |