josh-chamberlain commented 2 months ago

Context

Part of #256

Requirements

[ ] Reflect these changes to the schema
- I added a data dictionary to the docs which should be accurate to Airtable. To disambiguate or see examples, there's a handy "manage fields" feature in "Tools" in the upper right.
- [x] a few fields in Airtable related to project management were deleted
  - feasibility_notes, priority, estimated_effort
- [x] rename record_type_submitted to data_requirements
  - description: Details the data must have, like "case numbers" or "incident location"
  - rationale: asking people to adhere to our schema is silly. This is basically how people have always used the field.
- [x] add coverage_range
  - description: A range of years or dates the data should cover
  - rationale: we always end up asking people this
- [x] adjust status_last_changed to date_status_last_changed for clarity.
- [x] rename agency_described to location_described
- [x] Add triggers to automatically update the date_created and date_last_changed columns when the relevant actions occur (Airtable does this now, but we're going to #32 )
  - we should only override date_created if it's blank, though
- [x] Replace request_status's text type with an enum to enforce the permitted statuses (source of truth currently airtable)
- [x] Have all newly created rows have request_status be "Intake" by default
- note: we will probably sync work_required to (or from) the github issue as labels

Docs

Done

maxachis commented 1 month ago

Planned Schema

@josh-chamberlain, have a look at this and let me know if this design is what you are expecting

-- Table: public.data_requests

-- DROP TABLE IF EXISTS public.data_requests;

CREATE TABLE IF NOT EXISTS public.data_requests
(
    id bigint NOT NULL GENERATED BY DEFAULT AS IDENTITY ( INCREMENT 1 START 1 MINVALUE 1 MAXVALUE 9223372036854775807 CACHE 1 ),
    submission_notes text COLLATE pg_catalog."default",
    request_status request_status NOT NULL DEFAULT 'Intake'::request_status,
    submitter_contact_info text COLLATE pg_catalog."default",
    location_described_submitted text COLLATE pg_catalog."default",
    record_type text COLLATE pg_catalog."default",
    archive_reason text COLLATE pg_catalog."default",
    date_created timestamp with time zone NOT NULL,
    date_status_last_changed timestamp with time zone,
    coverage_range daterange,
    data_requirements text COLLATE pg_catalog."default",
    record_types_required record_type[],
    github_issue_url text COLLATE pg_catalog."default",
    internal_notes text COLLATE pg_catalog."default",
    pdap_response text COLLATE pg_catalog."default",
    volunteers_can_contact_requestor boolean,
    creator_user_id integer REFERENCES users (id)
    CONSTRAINT data_requests_pkey PRIMARY KEY (id)
)

TABLESPACE pg_default;

ALTER TABLE IF EXISTS public.data_requests
    OWNER to data_sources_app_dev;

REVOKE ALL ON TABLE public.data_requests FROM data_sources_app_v2;

GRANT ALL ON TABLE public.data_requests TO data_sources_app_dev;

GRANT DELETE, INSERT, SELECT, UPDATE ON TABLE public.data_requests TO data_sources_app_v2;

COMMENT ON TABLE public.data_requests
    IS 'Stores information related to data requests, including submission details, status, and related metadata.';

COMMENT ON COLUMN public.data_requests.id
    IS 'Primary key, automatically generated as a unique identifier.';

COMMENT ON COLUMN public.data_requests.submission_notes
    IS 'Optional notes provided by the submitter during the request submission.';

COMMENT ON COLUMN public.data_requests.request_status
    IS 'The status of the request, using a custom enum type request_status, defaults to Intake.';

COMMENT ON COLUMN public.data_requests.submitter_contact_info
    IS 'Contact information for the person who submitted the request.';

COMMENT ON COLUMN public.data_requests.location_described_submitted
    IS 'Description of the location relevant to the request, if applicable.';

COMMENT ON COLUMN public.data_requests.record_type
    IS 'Type of record requested.';

COMMENT ON COLUMN public.data_requests.archive_reason
    IS 'Reason for archiving the request, if applicable.';

COMMENT ON COLUMN public.data_requests.date_created
    IS 'The date and time when the request was created.';

COMMENT ON COLUMN public.data_requests.date_status_last_changed
    IS 'The date and time when the status of the request was last changed.';

COMMENT ON COLUMN public.data_requests.coverage_range
    IS 'The date range covered by the request, if applicable.';

COMMENT ON COLUMN public.data_requests.data_requirements
    IS 'Detailed requirements for the data being requested.';

COMMENT ON COLUMN public.data_requests.creator_user_id
   IS 'The user id of the creator of the data request'

-- Trigger: data_requests_status_change

-- DROP TRIGGER IF EXISTS data_requests_status_change ON public.data_requests;

CREATE OR REPLACE TRIGGER data_requests_status_change
    BEFORE UPDATE 
    ON public.data_requests
    FOR EACH ROW
    WHEN (old.request_status IS DISTINCT FROM new.request_status)
    EXECUTE FUNCTION public.update_status_change_date();

COMMENT ON TRIGGER data_requests_status_change ON public.data_requests
    IS 'Updates date_status_last_changed whenever request_status changes.';

COMMENT ON TYPE public.request_status IS '
Represents the different stages or statuses a request can have in the system:

- ''Intake'': The initial phase where the request is being gathered or evaluated.
- ''Active'': The request is currently being processed or worked on.
- ''Complete'': The request has been successfully completed and fulfilled.
- ''Request withdrawn'': The request has been withdrawn or canceled by the requester.
- ''Waiting for scraper'': The request is on hold, awaiting data collection by a web scraper.
- ''Archived'': The request has been archived, likely for long-term storage or future reference.
- ''Waiting for requestor'': The request is on hold, awaiting further information or action from the requester.
- ''Ready to Start'': The request is ready to be worked on.
- ''Waiting for FOIA'': The request is on hold, awaiting the results of a Freedom of Information Act request.
';

maxachis commented 1 month ago

@josh-chamberlain In addition to my query above, do we also want to include

submitter_user_id? Do we envision all submitters to be a logged-in user?
github_issue_url, if we want to link these to Github issues?

josh-chamberlain commented 1 month ago

@maxachis this is looking solid, but there are some fields missing from the data dictionary:

target_date
github_issue (addresses your next comment)
internal_notes
record_types_required
pdap_response
data_sources
volunteer_contact

Not needed:

withdrawal_reason
related_requests
work_required (deprecating in favor of github labels)

We also have a few more statii:

Notes:

what's the reason for not including the time zone in the dates?
request_status will later be inferred via github integration, and managed in the github project
as for submitter_user_id...I think we should allow anyone to submit. If they're logged in, we should capture their submitter_user_id and if they're not, we should capture their submitter_email.

All this makes me think there is more discussion / thought due on this before I can expect you to make much progress!

maxachis commented 1 month ago

All this makes me think there is more discussion / thought due on this before I can expect you to make much progress!

@josh-chamberlain Sounds good to me! Just trying to knock out what I can now!

Added the additional columns to the schema.

what's the reason for not including the time zone in the dates?

After looking it up, not any good reason! I believe this was an artifact of the original design. Updated!

data_sources

I propose that instead of having this be a column, we have this be a link table requests_to_data_sources, where we can add the relevant connections that way!

josh-chamberlain commented 4 weeks ago

@maxachis yep, I appreciate it! Yes, any way you make the link happen is fine by me.

maxachis commented 3 weeks ago

All this makes me think there is more discussion / thought due on this before I can expect you to make much progress!

@josh-chamberlain Just to help me keep stuff straight, what do you think remains to be ironed out on this topic before I can go full steam ahead? 🚂

josh-chamberlain commented 3 weeks ago

@maxachis I think we figured a lot of it out in our other discussions related to this on other issues; I feel better about our odds of having a source of truth in the swagger docs for which fields exist + are available, which is most important for making future improvements ("we can't fix what we can't measure", but meta). I say full steam ahead!

josh-chamberlain commented 2 weeks ago

@maxachis I don't think I captured the requests_to_data_sources idea in the requirements; did that end up happening? I don't see it in the code, but it may be known by another name.

maxachis commented 2 weeks ago

@maxachis I don't think I captured the requests_to_data_sources idea in the requirements; did that end up happening? I don't see it in the code, but it may be known by another name.

Not yet, but it'd be a trivial add to do! Just to confirm, we're happy with the link table idea, right?

josh-chamberlain commented 2 weeks ago

@maxachis yep, sounds good! I made an issue, #424

Police-Data-Accessibility-Project / data-sources-app

v2 schema updates for requests table #355

Context

Requirements

Docs

Planned Schema