to drive autocomplete widgets.

In particular:

• case names
• organization names
• method names

More can be added once we have mockups that need them.

Think about whether/how to cache these requests if necessary.

There are some cut-and-paste errors that result in some APIs for Methods or Organizations looking like duplicates of Case APIs and being grouped erroneously.

Having a stack of migrations is good during early development because if anything breaks it can be backed out or tested individually, but as the structure stabilizes we should dump out one schema to be based on going forward.

Instead of requiring users to be in the Contributors group, we should ensure they are not in the Banned group.

There are a few known places that need to do more (marked in the code). Mainly need to figure out the right as functions to call, I think, since postgres-promise can handle most of the escaping.

The first cut at generating JSON from Postgres was focussed on getting things working. Now we need to flesh it out. We currently pull in data from the main cases table, case___authors, case__localized_texts, and geolocation, but we want to also pull in:

• case__attachments
• case__methods
• case__tags
• case_videos

Version 2.3.0 of tape-watch just got published.

This version is covered by your current version range and after updating it in your project the build failed.

As tape-watch is a direct dependency of this project this is very likely breaking your project right now. If other packages depend on you it’s very likely also breaking them.
I recommend you give this issue a very high priority. I’m sure you can resolve this 💪

Your Greenkeeper Bot 🌴

Currently the alphabetical sort ranks titles that begin with " before those that begin with A. I would prefer if we could ignore "( etc. when looking at the lexicographic title order.

see prettier/prettier#923

• /case/<num> should return related_cases, related_methods, related_orgs in the payload
• same for /method/<num>
• same for /organization/<num>

Including dependencies in package.json

We are having trouble accessing Auth0 API from within localhost:3000
@dethe suggested creating a proxy might be a soluction.

To replicate the issue you can go to the frontend's branch called profiles and visit http://localhost:3000/en-US/otherprofile open the console and click on text "click to get info of a user".
You should see:

Currently if there is a facet present in the search, any full-text query is ignored. We should be able to handle both, and multiple facets.

Be prepared to treat objectType as a facet as well.

Wire all the related cases/methods/organizations into api return values:

Each case, method, organization can return:

• related cases
• related methods
• related organizations

and also

• cases which this is a related X for
• methods which this is a related X for
• organizations which this is a related X for

The tables are defined (in https://github.com/participedia/api/tree/35_complete_add_apis) already, but still need to be added to the query results.

currently controllers have code like:

AWS.config.update({
  profile: "ppadmin",
  region: "us-east-1"
});

That should all be centralized in a shared module, which looks up the profile and region in environment vars.

If you look at the data for http://api.participedia.xyz/user/134860 you'll notice:

id: 4387,
type: "case",
title: "Marrickville Infrastructure Citizens' Jury",
lead_image: "(Marrickville.JPG,,)"
},

We need it to be:

id: 4387,
type: "case",
title: "Marrickville Infrastructure Citizens' Jury",
lead_image: {
   url: "Marrickville.JPG",
   title: null,
   size: null
},

(just like cases and the like are usually returned)

(need this for profile and user views)

For converting our site from Amazon Document to Amazon RDS for PostgreSQL we will need:

• Schema for existing data
• Script to convert from existing data (as JSON) to SQL (after suitable cleanup)
• Full instructions for connecting API server to PostgreSQL

Currently looking at the node-postgres-promises library for connecting, there is an tutorial for using this: Designing a REST-ful API with Node and Postgres.

Feasibility of relying on PostgreSQL full text search rather than ElasticSearch is based on Postgres full-text search is Good Enough!.

Amazon RDS pre-installs the extensions needed for full-text search, and for GIS: General PostgreSQL Extensions Supported on Amazon RDS

The web site for the API (api.participedia.xyz) contains docs, but currently they're not very helpful in understanding things like the minimal content of body for POST/PUT calls for methods/cases/etc.

• deal with schema change since last time we did the CSV export
• automate the CSV download and image download + s3 upload.
• do it nightly

Because we're logging in with real credentials (via auth0), but the user table is populated with fake user data (including fake emails), getUserIfExists (which assumes that if there is an (authenticated) user, that user's email should be in the table and looks them up by email, fails.

I'd noticed this in a test where I'd accidentally included authentication on a GET request, but now see it whenever trying to view an object (case/method/organization) while logged in. It shouldn't be a problem on production (where our Auth0 users correspond to users in the database, but it's a pain in out test environment.

I think the easiest solution is a migration to add all of us developing and testing to the database, so we can be found by email lookup. I personally don't care too much if my email is checked into Github in the code of a migration (it's been publicly available on the internet for decades), but should probably get buy-in from others before doing this.

Another option is to pull down the users dynamically from the old Participedia and directly into Postgres without storing that data in Github at all, which might be worth investigating.

message: "invalid input syntax for integer: "getCountsByCountry"",

• write test
• fix test

In addition to raw conversion, we'd like to have:

• Schema in human and machine-readable formats
• Some detailed stats on how often each field is used and what the typical and outlier values are

Cleanup includes:

• splitting up list properly (including json-reencoded lists)
• not re-encoding json lists and dicts as strings in the json
• ensure all dates are ISO-8601 (no unix timestamps)
• extracting fields out of structured HTML blobs (i.e., "file_attachments")

Next Steps:

• make it machine readable
• re-apply David's key renaming
• Clean up inconsistencies (not all lat/longs are in the same format)
• Parse "file_attachments" into json objects
• Converting Yes/No into booleans
• Do something useful with id cross-referencing

Title, images & videos, description, author.

should show up in searches.

(Can do it with the generic case/method/org save I expect, just need it for featuring front-end)

Default query should return the latest version in the selected language. When text/title are changed, add a new record with current timestamp rather than changing the existing row.

We need to trigger a refresh on these when the data changes. If only English text is changed:

REFRESH MATERIALIZED VIEW search_index_en;

Repeat as needed to cover other languages (once they are supported).

We can store this in the database for ease of updating / versioning.

• affiliation (text)
• title (text)
• bio (text)
• location (geolocation data type)

• Should be returned in the /user/* calls.

Need to get these working on top of Postgres

I was truncating text for readability during debugging, forgot to disable that when exporting data. Also, seeing escaped ampersands "&" in text and titles.

/search?query=participatory%20budgeting results in a nasty error message.

Need to know how to send full text queries that are multiple words.

We need to be able to say "showing 30 hits out of 133" and compute the # of pages. So we need "total_hits" added to the return payload on /search (and ideally all paginated endpoints)

Using values which are keys into the locale db.

• [] extract possible values per field from data set
• [] de-englishify existing db to return keys
• [] generate en locale mapping for front end use and data export

currently on a case GET, there's a fragment of JSON that looks like:

bookmarked: {
   case: false
}

I think it should instead be just:

bookmarked: false

Replace API call interface to the database and search engine with ones to postgres. The frontend should be able to remain relatively stable, unless we want to restructure the JSON we return.

too sql fancy for me to do in a single query.

The profile editing page lets users specify:

• Location
• Organization (foreign key)
• Department
• Job Title
• Website
• Bio

• switch ES endpoint in api/helpers/es.js to point to the new elasticsearch cluster search-pp-stage-37xn6cdq7tj7ehv5rjrgxgrjhq.us-east-1.es.amazonaws.com
• check API code to use the new indices, etc.
• change front-end code to use the new schema
• change API backend to do non-search queries in dynamodb
• review AWS roles and permissions to lock down ES
• create API endpoints for methods, organizations, users
• create front-ends for methods, organizations, users

Giving "internal server error"

Search has three arguments:

1. query: either an empty string, the 'geo_country: [Country]' pair, or a user-typed query.
2. category: "Cases", "Methods", "Organizations" or "All"
3. sort: "chronological", "featured", or "alphabetical"

Front-end PR 122 depends on this

In the map display, I need to be able to efficiently get all of the title + locations of all of the cases for example, but without fetching body, and all of the other metadata on cases.

This will be a frequent query, as it'll happen on the home page. I suspect we want a simplified version of search_cases.sql etc which only returns fields we know we need. A hackier version is to do the full SQL search, but trim the results before they go over the slow wire.

Fields to return for search results:

• id
• title
• type (case/method/organization)
• lead image
• last updated
• (original) author name
• (original) author id
• bookmarked by current user?

Create a User API to return JSON from Postgres, following the basic pattern used for Cases, Methods, and Organizations

Reading 12.2.2 on https://www.postgresql.org/docs/9.5/static/textsearch-tables.html#TEXTSEARCH-TABLES-INDEX makes me feel that we probably want to encode language in the localized text tables using the postgres version of languages, so we can do

CREATE INDEX body_idx ON case__localized_texts USING GIN (to_tsvector(language, body));

but today language values are things like 'en', while postgres knows how to deal with:

 pg_catalog | danish     | configuration for danish language
 pg_catalog | dutch      | configuration for dutch language
 pg_catalog | english    | configuration for english language
 pg_catalog | finnish    | configuration for finnish language
 pg_catalog | french     | configuration for french language
 pg_catalog | german     | configuration for german language
 pg_catalog | hungarian  | configuration for hungarian language
 pg_catalog | italian    | configuration for italian language
 pg_catalog | norwegian  | configuration for norwegian language
 pg_catalog | portuguese | configuration for portuguese language
 pg_catalog | romanian   | configuration for romanian language
 pg_catalog | russian    | configuration for russian language
 pg_catalog | simple     | simple configuration
 pg_catalog | spanish    | configuration for spanish language
 pg_catalog | swedish    | configuration for swedish language
 pg_catalog | turkish    | configuration for turkish language

Currently we only look for summary and use that to set the body property. Either key should work, with preference given to body if it is present.

Feels like we need some sort of watermark marker, which applies the migrations after the lastermark.

Need to get these working on top of Postgres.

https://devcenter.heroku.com/articles/s3-upload-node

The profile view says we should show when the user joined. We have the field created_at in the user payload from Auth0, so we should add it to the user table.