Add form response model & migrationssqla-migration

4 files changed, 83 insertions, 3 deletions

diff --git a/backend/models/orm/__init__.py b/backend/models/orm/__init__.py
index 4db39ae..4b4e99b 100644
--- a/backend/models/orm/__init__.py
+++ b/backend/models/orm/__init__.py
@@ -2,8 +2,7 @@
 
 from .admins import Admin
 from .base import Base
-from .forms import Form, FormEditor, FormFeatures
-from .questions import (
+from .form_questions import (
     FormCheckboxQuestion,
     FormCodeQuestion,
     FormCodeQuestionTest,
@@ -16,6 +15,8 @@ from .questions import (
     FormTimezoneQuestion,
     FormVoteQuestion,
 )
+from .form_responses import FormResponse
+from .forms import Form, FormEditor, FormFeatures
 
 __all__ = (
     "Admin",
@@ -29,6 +30,7 @@ __all__ = (
     "FormQuestion",
     "FormRadioQuestion",
     "FormRangeQuestion",
+    "FormResponse",
     "FormSectionQuestion",
     "FormSelectQuestion",
     "FormTextQuestion",
diff --git a/backend/models/orm/form_responses.py b/backend/models/orm/form_responses.py
new file mode 100644
index 0000000..d9876b9
--- /dev/null
+++ b/backend/models/orm/form_responses.py
@@ -0,0 +1,28 @@
+"""A submitted response to a form."""
+
+from datetime import datetime
+
+from sqlalchemy import ForeignKey
+from sqlalchemy.orm import Mapped, mapped_column
+from sqlalchemy.types import BigInteger, DateTime, Text
+
+from .base import Base
+
+
+class FormResponse(Base):
+    """A submitted response to a form."""
+
+    __tablename__ = "form_responses"
+
+    response_id: Mapped[int] = mapped_column(primary_key=True)
+    form_id: Mapped[int] = mapped_column(ForeignKey("forms.form_id"))
+    submitted_at: Mapped[datetime] = mapped_column(DateTime(timezone=True))
+
+    user_id: Mapped[int] = mapped_column(BigInteger)
+    username: Mapped[str] = mapped_column(Text)
+    user_email: Mapped[str] = mapped_column(Text, nullable=True)
+    user_is_admin: Mapped[bool]
+
+    antispam_ip_hash: Mapped[str] = mapped_column(Text, nullable=True)
+    antispam_user_agent_hash: Mapped[str] = mapped_column(Text, nullable=True)
+    antispam_captcha_pass: Mapped[bool] = mapped_column(nullable=True)
diff --git a/backend/models/orm/forms.py b/backend/models/orm/forms.py
index 71119c7..0c1a4e3 100644
--- a/backend/models/orm/forms.py
+++ b/backend/models/orm/forms.py
@@ -12,7 +12,7 @@ from backend.constants import FormFeatures
 from .base import Base
 
 if TYPE_CHECKING:
-    from . import FormQuestion
+    from . import FormQuestion, FormResponse
 
 
 class Form(Base):
@@ -31,6 +31,10 @@ class Form(Base):
         cascade="all, delete",
         passive_deletes=True,
     )
+    form_responses: Mapped[list["FormResponse"]] = relationship(
+        cascade="all, delete",
+        passive_deletes=True,
+    )
 
     webhook_url: Mapped[str | None] = mapped_column(Text, nullable=True)
     webhook_message: Mapped[str | None] = mapped_column(Text, nullable=True)
diff --git a/migrations/versions/1721565620-9ee599d0f323_add_form_responses.py b/migrations/versions/1721565620-9ee599d0f323_add_form_responses.py
new file mode 100644
index 0000000..1927f16
--- /dev/null
+++ b/migrations/versions/1721565620-9ee599d0f323_add_form_responses.py
@@ -0,0 +1,46 @@
+"""
+Add form responses.
+
+Revision ID: 9ee599d0f323
+Revises: bef2f206168e
+Create Date: 2024-07-21 12:40:20.326205+00:00
+"""
+
+import sqlalchemy as sa
+from alembic import op
+
+# revision identifiers, used by Alembic.
+revision = "9ee599d0f323"
+down_revision = "bef2f206168e"
+branch_labels = None
+depends_on = None
+
+
+def upgrade() -> None:
+    """Apply this migration."""
+    # ### commands auto generated by Alembic - please adjust! ###
+    op.create_table(
+        "form_responses",
+        sa.Column("response_id", sa.Integer(), nullable=False),
+        sa.Column("form_id", sa.Integer(), nullable=False),
+        sa.Column("submitted_at", sa.DateTime(timezone=True), nullable=False),
+        sa.Column("user_id", sa.BigInteger(), nullable=False),
+        sa.Column("username", sa.Text(), nullable=False),
+        sa.Column("user_email", sa.Text(), nullable=True),
+        sa.Column("user_is_admin", sa.Boolean(), nullable=False),
+        sa.Column("antispam_ip_hash", sa.Text(), nullable=True),
+        sa.Column("antispam_user_agent_hash", sa.Text(), nullable=True),
+        sa.Column("antispam_captcha_pass", sa.Boolean(), nullable=True),
+        sa.ForeignKeyConstraint(
+            ["form_id"], ["forms.form_id"], name=op.f("form_responses_form_id_forms_fk")
+        ),
+        sa.PrimaryKeyConstraint("response_id", name=op.f("form_responses_pk")),
+    )
+    # ### end Alembic commands ###
+
+
+def downgrade() -> None:
+    """Revert this migration."""
+    # ### commands auto generated by Alembic - please adjust! ###
+    op.drop_table("form_responses")
+    # ### end Alembic commands ###